User-67C15525-3DBE-41EF-B48A981FE25748D2 19:08 17 Feb 2003
Locked

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 2003

AndyJ 19:33 17 Feb 2003

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

VoG™ 22:08 17 Feb 2003

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 2003

As AndyJ says you need a formula like

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

Bacon & Eggs 23:55 17 Feb 2003

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.

Elsewhere on IDG sites

OnePlus 5T review: Hands-on

Illustrator Andrés Lozano on his improv line work, brazen use of colours & hand sketching

iPhone X review

Comment envoyer gratuitement des gros fichiers ?