MS-Excel Negative times

Question

I'm writing a spreadsheet for a shop manager. What it does is keep track of the number of hours a worker has worked.

So you enter times for Monday-Sunday, and then an adjustment - e.g. if they work 40/40/40/32 hours for the month, then you would have an adjustment of -2/-2/-2/+6 to bring the worker to the 38 hour week that he's being paid for. Some (most) weeks may be adjusted for overtime. The spreadsheet then totals the hours.

This spreadsheet is supposed to just be a self-calculating version of a paper form. It needs to match the paper form as it has to be substituted for the old form which is given to some other member of the company (pay clerk, I don't know; I'm not rebuilding their whole system, just replacing a form)

I'm having trouble entering a negative time in the adj field - the field has a [h]:mm formatting. and when i enter a negative time (e.g. -2:00) it displays an error, saying "incorrectly formatted equation", with the suggestion that if I was entering a string then I should prefix with a apostrophe.

How do I overcome this?

Answer 1

According to Excel...

"Dates and Times that are negative appear as ########"

Doesn't sound like you're going to be able to do that with an auto-summation formula. You'll have to set the formatting as none and just type it in (which defeats the purpose).

Answer 2

Tools - Options - Calculation - 1904 date system

Check this box to use the 1904 (Mac) date system and you will be able to use negative dates and times. I'm not sure how this will effect existing spreadsheets, so maybe someone else can speak to that.