negative times in excel spreadsheet

I work flexi-time in a 4 week cycle. I can be above or below at the cycle end by +/- 5 hours. Mostly I am in deficit. I want to create a spreadsheet that adds my carry over deficit to the hours I've worked this month, but excel doesn't seem to add negative times. I either get a #value! error message or the cell remains blank (although it shows a figure if my carry over is in the black. Any help would be appreciated.

  AndyJ 19:31 17 Feb 03

Don't know if this helps click here;en-us;182247

  AndyJ 19:33 17 Feb 03

Sorry that link doesn't apear to work. Anyway it's Microsoft Support article 182247.

  VoG™ 22:08 17 Feb 03

Excel will do time calculations regardless of whether the result is positive or negative. Obviously the destination cell (i.e. where you write the formula) has to be formatted as Time.

Maybe if you can post a concrete example we can give more advice.

  VoG™ 22:45 17 Feb 03

As AndyJ says you need a formula like

=TEXT(ABS(B2-C2),"-h:mm:ss")

  Bacon & Eggs 23:55 17 Feb 03

I did a time sheet for the job i was doing last year... the bodge way round it is to record the actual hours you do, then total them up and use an =IF to say whether you are over or under. Something like (for a 160 hour month, 4x40hr weeks, with the total hours cell labelled "totalhours") :

=IF(totalhours>=160:00:00,totalhours-160:00:00,0-(160:00:00-totalhours))

Um, i think this checks out... not used Excel for a while so I'm happy to be told it won't work... I believe it should give you a positive number if you are over the hours (eg 5 for 165) and a negative for being under (eg -5 for 155).

Good luck - don't work too hard!

This thread is now locked and can not be replied to.

How to get Windows 10 for free | How to install Windows 10: There is still a way to avoid paying…

1995-2015: How technology has changed the world in 20 years

Alex Chinneck’s giant ice cube Christmas tree at Kings Cross

Apple rumours & predictions 2017: The iPhone 8, new iPads, and everything else you should expect fr7…