• Time in Formulas (Excel 2000 SR-1)

    Author
    Topic
    #358846

    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

    Viewing 1 reply thread
    Author
    Replies
    • #536391

      Excel does not like minus times it seems , but a workaround can be found at j-walk.com, using the 1904 date system.

      Andrew C

    • #536392

      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

    Viewing 1 reply thread
    Reply To: Time in Formulas (Excel 2000 SR-1)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: