Time & Date Calculations

Hi team,

I need your assistance on calculating Time and Date in Excel. I have 5 cells which are:

Cell A - START_DATE
Cell B - START_TIME

Cell C - END_DATE
Cell D - END_TIME

Cell E - DURATION

START_DATE START_TIME END_DATE END_TIME DURATION
11/10/2013 13:00:00 11/11/2013 16:00:00

I am trying to achieve the difference in Time between Cell A+B and Cell C+D and want to output to be in "days, hours, mins" in the duration cell. I have no idea if this is possible if someone could help me out that will be greatly appreciated.

Regards,
Ravi

Hi Ravi
Firstly I would change the format of columns A and C to a custom format of "dd/mm/yyyy hh:mm". Then in column D can just subtract Col A from Col C. Make sure Col D is formatted the same as columns a and C.
Hope this helps.
Tony

Hi Tony,

Thanks for your prompt advise. However i am trying to keep all the cell separate like the example spreadsheet attached. The reason of having the start date & start time & end date and end time in separate cells because i am manually copying and pasting the values from another system.

I understand your logic having the start date&time value in 1 cell and the end date&time in another cell and then subtract the two cell and get your answer. But as outlined above my i am manually bring the values for another system and i find it easier to have separate cells for the start date & start time and for the end date & end time.

Regards,
Ravi

Hi Ravi
In that case you can use this formula:
=(C2+D2)-(A2+B2)
Adjust accordingly.
Good luck.
Tony

Hi Tony,

Yup works fine. Is it possible to format the outcome to something like this - e.g 1d 8hrs 10mins ????

Regards,
Ravi

Tony,

This work fine the output is in total hours =24*((C2+D2)-(A2+B2)).

I am trying to figure out how I can break the hours into -days, hours, mins

Thanks for you help so far really appreciate this. I am such a beginner at this

Ok so this is where i am at and if any one can help me that will be great.

Start Date: Cell A - 4/08/2010
Start Time: Cell B - 23:00

End Date: Cell C - 5/08/2010
End Time: Cell D - 11:03

Total Duration in Hours: Cell E - 12:03 (using custom for [h]:mm)

Now when i try to convert the hours in Cell E into days, hours and minutes i am using the formula: =INT(E2) &" Days " & INT(MOD(K2,INT(K2))*24) & " Hours and " & MINUTE(K2) & " Minutes"

However my put is an error - #DIV/0! so not sure what i have done wrong now.

Regards,
Ravi

we're still on row number 1 ?

Please Login or Register  to view this content.

Thank you, I will try it tomorrow. However I have managed to get it to work using the following: =INT(E2) &"D " & HOUR(E2) & "H " & MINUTE(E2) & "M"

Hi All,

Please see the attached example - complete excel file this for for members/users who are having trouble with Time and Date function. Managed to achieve what I want thanks to the Excel Forum members ARGK & ThirtyTwo much appreciated.

I hope the attached file helps someone

Regards,
Ravi