Microsoft Excel 2003 (full product)

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?

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 Minutes 1) 15:00 18:30 -210The formula in cell C1 would be: =(A1-B1)*24*60

MIKE

Many thanks - I'll give that a go!

Ask Your Question

Weekly Poll

Have you played Jackbox during the pandemic?

Discuss in The Lounge

Poll History