Results 1 to 3 of 3
Thread: Negative times in formulas

20091119, 08:51 #1
 Join Date
 Feb 2001
 Location
 Shetland Isles, Shetland, Scotland
 Posts
 153
 Thanks
 3
 Thanked 1 Time in 1 Post
Hi,
I'm constructing a flexitime sheet & I've come across a problem with negative times.
The first problem was that I couldn't display negative times( 0:10 for 10 minutes due), I got an error. I fixed this by changing the date option to 1904 dates, this then displayed 0:10 correctly.
Then I had a problem with hours beyond 24 not displaying, 26 hours would be displayed as 2:00 (the other 24 having rolled over into a day). I fixed this by using =text(A1,"[h]:mm").
The reason I'm telling you the avove is that my solution may be causing the next problem:
I'd like to use those negative times in a formula, but I get a #VALUE error.
I've attached an example, Cell P6 shows the problem more clearly than the actual error cells.
P6 adds 2 onto 24 minutes (the contents of cell O6), I'd therefore expect P6 to show 47:36 (2 days plus 24 minutes), but I get a #VALUE error.
I did find a workaround (shown in cell O6). Basically, don't use a cell that might be negative in the formula, copy the formula that produces the negative value & use it again, this avaids the error. However this workaround would mean some horrendous formulas later on, & no hope of unpicking the logic should rules chage in a few years time.
Is there some simple solution to this? Is it my attempt to use 1904 dates that's causing it?
Any help would be much appreciated.
Thanks
Jim MacLeod
Shetland Isles.

20091119, 09:15 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
O6 does not contain a time value, it contains a text string resulting from the TEXT function, so you can't use it in calculations.
You should use the TEXT function only for display purposes, not for calculations.
There doesn't seem to be any need for it here.

20091119, 09:35 #3
 Join Date
 Feb 2001
 Location
 Shetland Isles, Shetland, Scotland
 Posts
 153
 Thanks
 3
 Thanked 1 Time in 1 Post