Home/ Questions/Q 7176
Next
In Process
Computing Staff
  • 0
Asked: 2021-11-23T10:14:19+00:00 In:

If Statement Using Dates

  • 0

I want a formula that will return a blank if there is no date and the exact date if there is one. Let’s say A! will eventually have a date and the cell is formatted as date, but I do not want the date format in B1, simply blank if no date

Share

    1 Answer

    1. You say “Column B actually has the date formula“. It would help if you told us what that formula is.

      For example, let’s say you have this:

                            A                               B
1                  2/1/2016
2   =IF(B2>DATEVALUE("1/01/2016"), "√","")     =IF(A1="","",A1)

      If you delete the date in A1 you will get the √ in A2 because the “value” of the “blank cell” returned by the formula in B2 is considered greater than the DATEVALUE of any given date. Don’t ask me why, it just is.

      Not knowing what formula you are actually using, I’ll toss out this workaround for my example:

      EDIT:

      Try this in A2 instead of the more complicated workaround I offered below. If this doesn’t help, then please tell us the exact “date formula” you are using. (I’m not even sure what a “date formula” is.)

      = IF(AND(ISNUMBER(B46),B46>DATEVALUE(“1/01/2016”)), “√”,””)

      EDIT

      Set your options so that 0 (zero’s) are not displayed in your workbook.

      Put this in B2:

      =IF(A1=””,,A1)

      By not using using the “” to return a blank cell, the formula will return 0 if A1 is blank. Since 0 is not greater that DATEVALUE(“1/01/2016”), the IF in A2 will be FALSE and the cell will remain visually empty.

      The reason you need to set the option to not display 0’s, is because 0 will appear as 1/0/1900 in B2, assuming it is formatted as a date.

      Click Here Before Posting Data or VBA Code —> How To Post Data or Code.

      message edited by DerbyDad03

      • 0