To automate Calculation of time taken

**rshnkmr39** · 10-14-2014, 01:53 PM

I have 2 cells with Date and time each

Cell A1 :2/11/2014 12:05:00 PM . I need to subtract the time from 6 PM same day. which gives me 5 hours 55 minutes.

The formula I have used is

=IF(TEXT(A1, "hh:mm")>"18:00",0,TEXT((DATE(YEAR(A1),MONTH(A1),DAY(A1))+"18:00")-A1,"hh:mm"))

Cell A2 : 2/14/2014 5:05:00 PM

For the second time i calculate the time taken from 9:30 in the morning which is

=IF(DATE(DAY(B1),MONTH(A1),YEAR(A1))=DATE(DAY(H10),MONTH(H10),YEAR(H10)),0,TEXT(B1-(DATE(YEAR(B1),MONTH(B1),DAY(B1))+"9:30"),"hh:mm"))

I face a problem when i try to sum the differences i.e the sum of the differences

Can anyone help in adding the results of these formulas

**alansidman** · 10-14-2014, 02:39 PM

Suggest you post up a copy of your worksheet as you have cells listed in the second formula for which you have not provided values. Difficult to analyze what the patient is suffering from if you cannot see him.

**6StringJazzer** · 10-14-2014, 03:21 PM

The formula I have used is

=IF(TEXT(A1, "hh:mm")>"18:00",0,TEXT((DATE(YEAR(A1),MONTH(A1),DAY(A1))+"18:00")-A1,"hh:mm"))

When working with dates and times, don't convert back and forth to text. Here is a simpler formula that will give you the same result, and the result will be in time format instead of text.

Formula:

Please Login or Register  to view this content.

For the second time i calculate the time taken from 9:30 in the morning which is

=IF(DATE(DAY(B1),MONTH(A1),YEAR(A1))=DATE(DAY(H10),MONTH(H10),YEAR(H10)),0,TEXT(B1-(DATE(YEAR(B1),MONTH(B1),DAY(B1))+"9:30"),"hh:mm"))

What is in B1? H10? If you are trying to calculate the time from 9:30 AM to 5:05:00 PM (and return 0 if it is earlier than 9:30), the similarly you can use

Formula:

Please Login or Register  to view this content.

Then you can easily add these two results to get a valid time.

**rshnkmr39** · 10-15-2014, 03:00 PM

I am able to get the required result now...If the start date is 10/12/2014 and end date is 10/15/2014 I calculate the time taken on start day and end day using formula mentioned above. In between i need to take the 2 days i.e 8 and a half hours multiplied by 2 days and add it with 5 hours 55 mins and 7 hours 35 mins