Getting a negative '00:00' (2002)

[Lucy]

Hi

I suspect this is going to be very obvious to some of you, but how on earth does Excel decide that -00:13 added to 00:13 makes -00:00. I have a workbook set up with twelve worksheets each holding a timesheet for one month. They were all set up at the same time with the same formulas and Jan-Oct have worked just fine. I can solve the practical problem by pasting the values to a new sheet using the earlier sheets as templates, but I am curious as to how this kind of thing happens.

Thanks in advance.

Lucy

[HansV]

How did you get negative time values? As far as I know, Excel can only handle non-negative times.

[Lucy]

The cell is formatted as hh:mm under the Custom option. This allows us to calculate flexitime credited and debited. (See values below)

I can try sending you a sample, if that would help.

DATE IN OUT IN OUT HOURS LEAVE DAILY +/- HOURS +VE -VE NOTES

1st 08:30 12:51 13:37 17:00 07:44 0:32 03:33
2nd 10:00 12:40 02:40 -4:32 -00:59 Flexi pm

[Lucy]

Oops, that's why I should have used preview, isn't it? Hope the lack of tabulation doesn't make the info unintelligible.

Sorry, Lucy

[HansV]

I still don't understand how you get -4:32 and -00:59. If I enter something like that in Excel, I either get an error message or it is interpreted as text. And what do they mean?

[sdckapr]

If you use the 1904 time system (tools -options - calculation), excel will accept negative times and dates.

Steve

[sdckapr]

I don't understand the question. Excel can subtract 2 seemingly identical numbers and get "-0" it depends on the rounding and the rounding errors.

If you change to a different format display you will see that you have a tiny negative number so you have a negative difference, (that is the undisplayed figures of "-00:13" are larger than the undisplayed figures of "00:13"

Steve

[Lucy]

And I thought this was going to be straightforward <img src=/S/sorry.gif border=0 alt=sorry width=15 height=15>
The problem is this:

Until recently 'adding' the contents of two cells, eg -00:13 + 00:13 would have produced 00:00; now it produces -00:00.
The reason that this is a problem is because adding -00:00 to the contents of the next cell down the worksheet produces the 'wrong' answer, whereas adding 00:00 to the contents of the next cell down produces the 'right' answer.
In practice the errant minus sign means that someone who is in debit by 13 minutes on Day 1 of the month, works an extra 13 minutes on Day 2, and an extra 33 minutes on Day 3 will show a debit balance of 23 hours and 27 minutes (-23:27), when they should show a credit balance of thirty three minutes (00:33).

This seems incredibly long-winded, I'm sorry I don't have the techy language or understanding required to explain it more clearly.

Lucy

[sdckapr]

The only way that adding -0:00 to 0:33 will give -23:27 is if the -0:13 has an extra DAY onto it than the 0:13.
In that case the -0:00 is actually the number -1 (= -24 hrs) whihc is why the "debit" is so large.

Format the cells to general. I would wage that your "-0:00" is -1. Which means that your "-0:13" is actually "-1.009027" where your "0:13" is only "0.009027"

Steve

[HansV]

Thanks, I didn't know that. I never use the 1904 system.

[sdckapr]

Neither do I. It is just one of those "tricks/techniques" I heard about at some time.

Steve

[LegareColeman]

Instead of formatting these cells as hh:mm, try using [hh]:mm (with the brackets around the hh). This will display elapsed time, which should show you when one of the values is greater than 24 hours.

[Aladin Akyurek]

The 1900 date system is due to Lotus 1-2-3, which MS took over for compatibility. The 1904 date system is in fact the correct system (which is used on the Mac).

[stuck]

I'm curious, how can the 1904 start date be "the correct system" and the 1900 one be wrong? Are they not just different but arbitrary starting points?

stuck

[sdckapr]

The 1900 is wrong since there is a Feb 29, 1900. 1900 is not a leap year since it is a century and not divisible by 400.

All the "serialnumbers" before Mar 1, 1900 are "incorrect"

Steve