# 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?

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]")))

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]")))

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.

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.

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.

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!

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

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.

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