MS-Excel Negative times

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开发者_如何学JAVA. 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?

---

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.

---

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).

---

I am solving the same problem. Setting for date formatting "1904" is necessary for both below described solution.

You can enter an equation as a result of predeceasing cells like C5-C4-C3 (check out-check in-standard working time). The result is negative and it will be displayed like -1:15 and you can further process it.

Second way was already described above - to put into the requested cell a negative decimal value as a fraction of "1". "1,000"=24 hours, "0,5"=12 hours, "0,01"= 14 minutes, "0,041667"=1 hour. You have to find the correct decimal numbers first.