Subtracting Dates

[lkontoes13]

Hello,

I am trying to subtract two date fields, both of which are formatted like this: 4/15/2013 12:10:00 AM, with date first and then time. I am trying to subtract the two so I can tell what the time difference is between the two.

For example: Say these are the two cells 4/15/2013 8:59:00 AM and 4/15/2013 12:10:00 AM. I would like to find the time difference, which in this case would be 8:49. Please help!

Thank you greatly.
-Leo

[FDibbins]

Hi and welcome to the forum

You should be able to just subtract 1 from the other - and then format as hours/minutes?

[benishiryo]

hi Leo, welcome to the forum. assuming the 2 are in A1 & B1 respectively, simply:
=A1-B1

that's the fraction of a day.
format as:
[h]:mm

if you need 8:30 hours shown as 8.5, then:
=(A1-B1)*24

[MarvinP]

Hi Leo and welcome to the forum,

I wonder if your version of excel knows about DateDif?

http://www.cpearson.com/excel/datedif.aspx or
http://office.microsoft.com/en-us/he...001160981.aspx

[lkontoes13]

Wow, that was easy. Thanks guys! Now that I have done that, some fields that have day and time differences show up like this: 1/1/00 6:16, meaning 1 day, 6 hours and 16 minutes time difference. However, I would like it to just show up as 30 hours and 16 minutes, converting days to hours in order to keep it formated as "time". Is that possible?

[FDibbins]

maybe try this?

=INT(A1-B1)*24+MOD(A1-B1,1)

[benishiryo]

or custom format as such:
[h]" hours and" mm" minutes"