subtracting dates and hh:mm:ss

[wisas62]

I'm trying to subtract two cells which contain both a date and a time.

05/03/2012 10:31:00 05/03/2012 11:02:40
05/06/2012 11:57:55 05/06/2012 12:08:30

Above is an example of what I want to subtract. I want to subtract B1-A2 and I would like the results in hours. I found a thread that had (=INT(B1-A2)&" days, "&TEXT(MOD(B1-A2,1)*24,"0.00")&" hours") but it doesn't work like I want it to. I also tried just a simple (=B1-A2) but it ignores the date in the cell. At the end I need to be able to take all my results and take an average in hours.

An example would be that (=B1-A2) =72:55:15.

[Kevin UK]

Hi wisas62

just subtract one from the other and format cells as "[h]:mm

Those time subtracted are only 31 mins & 10 mins.
Time.PNG

[wisas62]

No, I need to subtract multiple days;

05/06/2012 11:57:55 - 05/03/2012 11:02:40

which is 72:55:15.

[judgeh59]

=TEXT(A2, "mm/dd/yyyy hh:mm:ss") - TEXT(B1,"mm/dd/yyyy hh:mm:ss")

this worked for me using your B1-A2 scenario....this assumes that your date/times are not real date/times but TEXT/STRINGS....if that doesn't make sense let me know and I'll try to let you know how DATE/TIMES are represented in Excel....HTH

[judgeh59]

oops.....you will need to format the cells like Kevin suggested....except maybe adding a :ss to the end for seconds....