Calculations with negative times in EXCEL

Microsoft Excel 2003 (full product)
April 7, 2011 at 04:58:58
Specs: Windows XP
I'm working on timesheets, specifically TOIL (Time Off In Lieu). If an employee starts the year owing time, eg -3.30 hours, then works extra time of 3.30 hours, then takes another 1.30 hours TOIL I need a calcuation to show he owes 1.30 hours TOIL. At the moment I'm using this formula (because I have 3 months of time owed and time owing to calculate plus the brought forward 2010 figure) =TEXT(C21+D21+F21+H21+J21-E21-G21-I21-K21,"h:mm") which works for all employees who started the year without owing TOIL. However for those starting with a negative value I get the #VALUE! message. Any ideas?

See More: Calculations with negative times in EXCEL

April 7, 2011 at 15:45:42
The easiest way I have found to do negative time math is to convert everything to minutes, then you can use the minutes like they were regular numbers and you can do addtion, subtraction, etc.

Instead of -3:30 you would have -210

      A      B       C
                  Owed in
1) 15:00   18:30   -210

The formula in cell C1 would be: =(A1-B1)*24*60


Report •

April 8, 2011 at 00:34:47
Many thanks - I'll give that a go!

Report •
Related Solutions

Ask Question