using time format to make calculations

Microsoft Office excel 2003 - upgrade
July 21, 2010 at 22:12:50
Specs: office 2003
I am trying to use formulas to calculate accross columns in a schedule document that uses 24 hour clock. I am having 2 problems.

1st) the format keeps changing to time which then shows a portion of 1 with 1 being 24 hours and 12 being equal to .5 - I tried using different cell formats which worked, but then ran into problem #2

2nd) I need a total number of hours, so a calcuation 14:00 - 6:30 should equal 7.5 hours, not 7.7 hours

please help!

See More: using time format to make calculations

Report •

July 22, 2010 at 03:46:12

I suggest that you keep your time entries in standard Excel format, which as you note has time as the decimal part of the number.

If you use proper Excel times, you can calculate differences with simple subtraction.

Then as a final step convert the time difference into hours and fractions of an hour.

You don't say whether there is any date information stored with your times. This makes a difference when calculating the time difference when start is before midnight and end is after midnight.

If the date is included e.g. 20/July/2010 23:00 to 21/July/2010 01:00 the difference will calculate to 2 hours.

If there is no date information, Excel shows ######## because it will not display negative times.

To overcome this and assuming that you are entering times with no date information, put start time in A1 (23:00) and end time in B1 (01:00)
then in cell C1 enter this formula:
Format the cell as h:mm
Cell C1 will display 2:00

Now convert the time difference to hours and fractions of an hour.

Format cell C1 as a number with say 4 decimal places
Enter this formula in cell C1:

Using your example with 06:30 in A1 and 14:00 in B1
C1 shows 7.5000

Another example:
A1=06:30, B1 = 06:50
C1 shows 0.3333 i.e., a third of an hour.

Hope this helps


Report •

July 22, 2010 at 17:35:38

Thank you so much, in the end I simplified the second formula by adding another column to just =c1*24

I had originally tried with the IF formula, but couldn't work it out. Thanks again!

Report •

July 22, 2010 at 19:14:43
You're welcome,



Report •

Related Solutions

Ask Question