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!

Hi, 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:

=IF(B1<A1,B1+1-A1,B1-A1)

Format the cell as h:mm

Cell C1 will display 2:00Now 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:

=24*(IF(B1<A1,B1+1-A1,B1-A1))Using your example with 06:30 in A1 and 14:00 in B1

C1 shows 7.5000Another example:

A1=06:30, B1 = 06:50

C1 shows 0.3333 i.e., a third of an hour.Hope this helps

Regards

Yes! 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!

You're welcome, Regards

Humar

Ask Your Question

Weekly Poll

Do you think Salesforce should have bought Slack?

Discuss in The Lounge

Poll History