Solved calculating time difference in Excel

Microsoft Office excel 2007 home & stude...
July 10, 2010 at 01:43:27
Specs: Windows XP
I have two sets of date/time readings, each in three colums with drop down menus for date, hours and minutes. I need to subtract one date/time from the other to give a time difference in hours.

See More: calculating time difference in Excel

Report •


#1
July 10, 2010 at 18:56:22
✔ Best Answer
Hi,

You haven't said what cells any of your data is in, or what format your date / time values take.

This suggested formula is based on the following:
1. Start date is in cell A2 and the date is recognized by Excel as a date (for example it will accept different date formats, and if formatted as a number will show a value around 40000 if it is a recent date)
2. Start hour is in cell B2 and it is an integer in the range 0 to 24
3. Start minute is in cell B3 and it is an integer in the range 0 to 59
4. The Ending date/time values are in cells D2, E2 & F2

As you want only a difference in hours, rounding has been applied, based on 30 minutes or greater rounding up to the next hour.

Enter this formula in any empty cell:
=IF(MINUTE((D2+TIMEVALUE(E2&":"&F2))-(A2+TIMEVALUE(B2&":"&C2)))>29, (D2+TIMEVALUE(E2&":"&F2))-(A2+TIMEVALUE (B2&":"&C2))+1/24, (D2+TIMEVALUE(E2&":"&F2))-(A2+TIMEVALUE(B2&":"&C2)))
Format the output cell:
Apply this Custom number format [h]
The square brackets are required. Excel will now convert days of difference to hours of difference and add it to the time difference.

If the assumptions given above are wrong, please provide the appropriate details.
If the formula does not return the expected number of hours, please post all 6 input values and the result of the formula.

Regards


Report •

#2
July 11, 2010 at 11:25:16
You, my friend, would appear to be a genius!! The only thing different from what you assumed was that I needed hours and minutes, (which I didn't make clear on the orignal post), but all I did was change the custom format to [h]:mm and I get hours and minutes. Brilliant solution and many thanks.....Ian

Report •

#3
July 11, 2010 at 13:27:08
Hi,

Don't forget that as you are using hours and minutes, you don't need the Rounding, so the formula just becomes:
=(D2+TIMEVALUE(E2&":"&F2))-(A2+TIMEVALUE(B2&":"&C2))

Regards


Report •

Related Solutions

#4
July 28, 2010 at 01:24:27
Ok guys, I'm back with my time difference problems. Thanks to Humar, I now have a series of totals in hours. I now need to add/subtract these totals. Using the SUM function adds them up but only in base 10. If I try to apply the [h] format the number changes to around 167880 or similar. I have Excel 2003 at home and it works on that, but the 2007 version I am using at work won't. Is it a setting problem? I notice that although I get the number in the cell, the formula bar displays a date and a time!!!

Any thoughts would be appreciated.......Ian


Report •


Ask Question