# Date formula if positive show, if negative don't show.

1. ## Date formula if positive show, if negative don't show.

=VALUE(TEXT((NOW()-E16)/24,"[h]"))

This is the formula I'm using now, if I have a positive number I want it to show up, if the number is negative, I don't want it to show up. Is this possible?  Register To Reply

2. ## Re: Date formula if positive show, if negative don't show.

You can use an IF statement to return a blank, or Coditional Formatting to hide negative amounts.

=IF(VALUE(TEXT((NOW()-E16)/24,"[h]"))<0,"",VALUE(TEXT((NOW()-E16)/24,"[h]")))  Register To Reply

3. ## Re: Date formula if positive show, if negative don't show.

This may be shorter a bit:
=IF(NOW()-E16<0,"",VALUE(TEXT((NOW()-E16)/24,"[h]")))  Register To Reply

4. ## Re: Date formula if positive show, if negative don't show. Originally Posted by royUK You can use an IF statement to return a blank, or Coditional Formatting to hide negative amounts.

=IF(VALUE(TEXT((NOW()-E16)/24,"[h]"))<0,"",VALUE(TEXT((NOW()-E16)/24,"[h]")))
Thanks that works perfectly. I am now running into an issue in where I tried to make it so that it won't show up if no date it there, and I get an -40981. I tried to fix it by:

=IF(VALUE(TEXT((E73-NOW())/24,"[h]"))>=0,"",VALUE(TEXT((E73-NOW())/24,"[h]"))),IF(E73,TODAY()-E73,0)

Now I get an error, I really need to take an excel course again.  Register To Reply

5. ## Re: Date formula if positive show, if negative don't show.

Hi

Give a try to this.

=IF(OR(VALUE(TEXT((NOW()-E16)/24,"[h]"))<0,(VALUE(TEXT((NOW()-E16)/24,"[h]"))="",""),VALUE(TEXT((NOW()-E16)/24,"[h]"))))

Hope to helps you.  Register To Reply

6. ## Re: Date formula if positive show, if negative don't show. Originally Posted by Fotis1991 Hi

Give a try to this.

=IF(OR(VALUE(TEXT((NOW()-E16)/24,"[h]"))<0,(VALUE(TEXT((NOW()-E16)/24,"[h]"))="",""),VALUE(TEXT((NOW()-E16)/24,"[h]"))))

Hope to helps you.
I come back with a window that says formula contains an error.  Register To Reply

7. ## Re: Date formula if positive show, if negative don't show.

....it won't show up if no date it there, and I get an -40981.
If there is no date, Where? In which cell?

As you can understand, for all of us is very difficult, because we don't have a workbook to test our suggestions!  Register To Reply

8. ## Re: Date formula if positive show, if negative don't show.

Try this formula

=IF(OR(E16>NOW(),E16=""),"",INT(NOW()-E16))

format formula cell as general  Register To Reply

9. ## Re: Date formula if positive show, if negative don't show.

Ok, I got this going well so far, but I am running into an issue. I have placed some conditional formatting using the equation, I am curious is there a way to make conditional formatting work so it only shows a specific word or icon if the condition is 0?

I've looked around and so far I see >= but that is it, no =.

Here is the sheet so far.  Register To Reply