Results 1 to 15 of 16

20031114, 11:26 #1
 Join Date
 Apr 2001
 Posts
 15
 Thanks
 0
 Thanked 0 Times in 0 Posts
Getting a negative '00:00' (2002)
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 JanOct 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

20031114, 12:00 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Getting a negative '00:00' (2002)
How did you get negative time values? As far as I know, Excel can only handle nonnegative times.

20031114, 12:40 #3
 Join Date
 Apr 2001
 Posts
 15
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Getting a negative '00:00' (2002)
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

20031114, 12:42 #4
 Join Date
 Apr 2001
 Posts
 15
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Getting a negative '00:00' (2002)
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

20031114, 12:55 #5
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Getting a negative '00:00' (2002)
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?

20031114, 14:28 #6
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Getting a negative '00:00' (2002)
If you use the 1904 time system (tools options  calculation), excel will accept negative times and dates.
Steve

20031114, 14:32 #7
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Getting a negative '00:00' (2002)
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

20031114, 15:30 #8
 Join Date
 Apr 2001
 Posts
 15
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Getting a negative '00:00' (2002)
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 longwinded, I'm sorry I don't have the techy language or understanding required to explain it more clearly.
Lucy

20031114, 15:49 #9
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Getting a negative '00:00' (2002)
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

20031114, 16:12 #10
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Getting a negative '00:00' (2002)
Thanks, I didn't know that. I never use the 1904 system.

20031114, 16:34 #11
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Getting a negative '00:00' (2002)
Neither do I. It is just one of those "tricks/techniques" I heard about at some time.
Steve

20031114, 20:56 #12
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Getting a negative '00:00' (2002)
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.
Legare Coleman

20031115, 08:50 #13
 Join Date
 Jan 2002
 Location
 The Hague, Netherlands
 Posts
 283
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Getting a negative '00:00' (2002)
The 1900 date system is due to Lotus 123, which MS took over for compatibility. The 1904 date system is in fact the correct system (which is used on the Mac).
Microsoft MVP  Excel

20040903, 10:56 #14
 Join Date
 Apr 2001
 Location
 Arriving Somewhere but not Here
 Posts
 698
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Getting a negative '00:00' (2002)
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

20040903, 12:44 #15
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Getting a negative '00:00' (2002)
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