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

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.

Huawei P10 review

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

An overview: What leading creative agencies are doing to improve diversity

New iPad, iPhone SE & Red iPhone 7 on sale now