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

1. 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. 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. 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. 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
•