Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Shetland Isles, Shetland, Scotland
    Posts
    153
    Thanks
    3
    Thanked 1 Time in 1 Post
    Hi,
    I'm constructing a flexi-time 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.
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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.


  3. #3
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Shetland Isles, Shetland, Scotland
    Posts
    153
    Thanks
    3
    Thanked 1 Time in 1 Post
    Quote Originally Posted by HansV View Post
    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.

    Of course! I thought the TEXT function was doing something fancy, I forgot I could use the "[h]:mm" in Custom formatting.

    Thanks once again Hans!

    Jim

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •