Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Fontana, California, USA
    Posts
    625
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Time in Formulas (Excel 2000 SR-1)

    I need the results of a simple subtraction formula to display the result as either positive or negative. The problem, for me, is I don't know how to create a custom format to show hours, minutes and seconds as negative numbers. Help me please. Thanks in advance

    Linda

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Time in Formulas (Excel 2000 SR-1)

    Excel does not like minus times it seems , but a workaround can be found at <A target="_blank" HREF=http://j-walk.com/ss/excel/usertips/tip051.htm>j-walk.com</A>, using the 1904 date system.

    Andrew C

  3. #3
    Star Lounger
    Join Date
    Feb 2001
    Location
    Virginia Beach, Virginia, USA
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Time in Formulas (Excel 2000 SR-1)

    If you use Excels built in date/time formats, the values must be positive. A negative value formated to time is displayed as #######. One workaround is to convert from time to decimals or either minutes, hours, days (whatever works for you particular case. In Excel dates 1 = 24 hours. (Since you are dealing with relative time, we can hopefuly ignore base dates) 11:30am = 0.4792 (days) If you want decimal hours just multiply by 24 and you get 11.5 hours. If you need hh:mm:ss then try something along the lines of
    =SIGN(A1)*INT(ABS(A1))&":"&(ABS(A1)-INT(ABS(A1)))*60

  4. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Fontana, California, USA
    Posts
    625
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Time in Formulas (Excel 2000 SR-1)

    Excellent Andrew. Thank you sooo much! Have a terrific day.

    Linda

Posting Permissions

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