# 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 #22nd) I need a total number of hours, so a calcuation 14:00 - 6:30 should equal 7.5 hours, not 7.7 hoursplease help! See More: using time format to make calculations

#1 July 22, 2010 at 03:46:12
 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

Report •

#2
July 22, 2010 at 17:35:38
 Yes!Thank you so much, in the end I simplified the second formula by adding another column to just =c1*24I had originally tried with the IF formula, but couldn't work it out. Thanks again!

Report •

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

Report •
Related Solutions 