1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Excel and negative times (#####)

Discussion in 'Business Applications' started by dw52, Aug 4, 2005.

Thread Status:
Not open for further replies.
Advertisement
  1. dw52

    dw52 Thread Starter

    Joined:
    Jul 7, 2001
    Messages:
    53
    Sorry but I'm back again with the same spreadsheet, different problem.

    Excel 2000 in win98 crashed all the time (another post for later) so I transferred file to PC with win XP/Excel 2003.

    New problem is:
    On one worksheet, entering appt times between 12:00 am and 12:59 AM shows #### in the column for pickup time. I expanded the column width but still occurs.

    Ex 12:00 am in the appt time = blank in appt col (but appt. time appears in formula bar) and #### in col for pickup time.
    Also, all appt times between 12:01 am and 12:59 am = #### for pickup time except 12:45 am which shows blank for appt time & ### for pickup time.

    When I click on the cells with ####, I see a message about "negative dates or times will show ####".
    There are currently 6 sheets for Jul to Dec but this seems to happen only in the Dec sheet.

    Thanks for any help you can provide.
     
  2. ChuckE

    ChuckE

    Joined:
    Aug 30, 2004
    Messages:
    2,311
    Sounds like you have negative times.
    If it is a formula for that cell, then perhaps your formula is reversed, where you are subtracting a newer time from an earlier time. Try reversing the two values.

    Why not temporarily change the cell format to a regular number, to see if the value is positive or negative. If it is negative, you will get those ####'s.
     
  3. dw52

    dw52 Thread Starter

    Joined:
    Jul 7, 2001
    Messages:
    53
    Hmmm??. The ### cells do show a negative number but how is that possible if I only copied the cell formula from the cell above, which returns a positive number?

    Here's some more info (BTW I am back to using Excel 2000 on a Win98 PC):

    Col A: date as dd/mm/yy, ddd
    Col B: Appt Time as h:mm AM/PM
    Col C: formula =IF(ISBLANK(D2)," ",D2-TIME(0,45,0)) format = same as Col B

    User enters appt time and Excel calculates pick up time. Got help from Dreamboat for the formula in Col C (Thank you!) so now users will see a blank cell if Col B is empty, just like I wanted. The new problem is appt times between 12AM and 12:59AM resort to the # symbols. I have tried adjusting the col width.
    Any ideas??
     
  4. maxflia10

    maxflia10

    Joined:
    Feb 24, 2003
    Messages:
    331
    Try this,

    =IF(D2="","",D2-"0:45"+(D2 < "0:45"))
     
  5. dw52

    dw52 Thread Starter

    Joined:
    Jul 7, 2001
    Messages:
    53
    By jove, it's working!!! Thank you!!!!
    I understand the first part of the formula but the last part: +(D3 < "0:45")) has me confused. If you don't have time to explain I will understand and thanks again!!!!
     
  6. maxflia10

    maxflia10

    Joined:
    Feb 24, 2003
    Messages:
    331
    You were getting the ##### because the formula was returning a negative time value when trying to subtract the 45 minutes for the pickup time. The D3 < "0:45" either equates to True (1) or False (0). If True it adds 1 or 24 hours to the answer. If you have to subtract times that run into the next day, this would also apply. As an example a start time of 7:00 PM and end time of 2:00 AM the next day.
     
  7. dw52

    dw52 Thread Starter

    Joined:
    Jul 7, 2001
    Messages:
    53
    Thanks for the explanation!!!
     
  8. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/387515

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice