# need one formula for returning positive or negative time w/out #######

1. ## need one formula for returning positive or negative time w/out #######

I'm subtracting time (y2-z2) and in some cases it should be a negative number. It will change, however, depending on data entered in. So i need one formula that will display the actual time whether the number is positive or negative.

I found this from a different website: =IF(Y2<Z2,"-","")&TEXT(ABS(Y2-ZZ2),"hh:mm:ss") but when the # is positive it just returns the value in y2.

any help?

Thanks!

Ian

2. ## Re: need one formula for returning positive or negative time w/out #######

=TEXT(ABS(A1-B1),"hh:mm:ss")

maybe I'm not understanding your request but I tried this and it seems to work on the data set I tried...HTH

3. ## Re: need one formula for returning positive or negative time w/out #######

Will you be doing any calculations on the results of these formulas?

By default, Excel will not display negative times but there is a setting that will allow you to do so.

However, this setting is potentially dangerous because it will cause all dates in your file to change.

For that reason I recommend NOT using this setting.

We can get your current formula to work but the result will be a TEXT string, not a true Excel time value.

4. ## Re: need one formula for returning positive or negative time w/out #######

I will definitely NOT be doing any calculations based on the results. I read something about changing it to 1904, rather than 1900 date format, but I can't find the setting in excel 2010.

5. ## Re: need one formula for returning positive or negative time w/out #######

Originally Posted by completelyhis
=IF(Y2<Z2,"-","")&TEXT(ABS(Y2-ZZ2),"hh:mm:ss") but when the # is positive it just returns the value in y2.

Ian
Probably due to the difference between Z2 and ZZ2, guessing ZZ2 is blank...

6. ## Re: need one formula for returning positive or negative time w/out #######

You can find the setting at:

File>Options>Advanced>When calculating this workbook>Use 1904 date system

If you have any dates in the file 1462 days will be added to those dates.

For example, if you had the date 1/1/2013 entered and you then change to the 1904 date system that date will now display as 1/2/2017.

7. ## Re: need one formula for returning positive or negative time w/out #######

tony and Johnmo...thanks! first off...can't believe i made such a bonehead mistake as to have the double z in there! and...now i know how to change the date system in 2010!

thanks again!

8. ## Re: need one formula for returning positive or negative time w/out #######

Good deal. Thanks for the feedback!

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1