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

**Cyberpawz** · 03-13-2012, 08:24 AM

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

**royUK** · 03-13-2012, 08:31 AM

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

**Cyberpawz** · 03-13-2012, 08:55 AM

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.

**bebo021999** · 03-13-2012, 08:39 AM

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

**Fotis1991** · 03-13-2012, 09:19 AM

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.

**Cyberpawz** · 03-13-2012, 09:59 AM

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.

**Fotis1991** · 03-13-2012, 10:24 AM

....it won't show up if no date it there, and I get an -40981.

From your post N0#4
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!

**daddylonglegs** · 03-13-2012, 10:36 AM

Try this formula

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

format formula cell as general

**Cyberpawz** · 03-14-2012, 07:49 AM

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.