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

✔ Best Answer

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

Assuming you're using Excel, this will work: =IF(A1="","",A1)

B1 would need to be formatted as date. Otherwise you get a number.

message edited by beachyhbt

Thanks that worked, but I have a column linked to that column that messed up as a result. Column B actually has the date formula and if column B has a date I want column A to display a check mark. This is the formula for column A =IF(B46>DATEVALUE("1/01/2016"), "√","") but now that column B is blank if the date is blank, it now returns a check mark even if the date field is blank

Just to make sure I understand. You're changing the criteria. If Column B has a date, then you want a checkmark in Column A? So the cell in Column B either has a date or it's blank?

The Formula as written works correctly for me.

What linked cell is messed up?

Sorry if I'm being confusing. I have an excel workbook that I fill in over the course of 3-4 months. Sheet 1 is the project, Sheet 2 calculates the % complete for the project. I want to pull a date from sheet 1 to column B sheet 2, if there is no date I want it to remain blank, not in a date format. The first formula worked great. Then if there is a date in column B sheet 2 I want column A sheet 2 to return a checkmark. That was working until I entered the formula you sent to me in column B. When I entered the formula in column B the date format disappeared just as it was supposed to because there was no date on sheet 1. But in column A it returned a checkmark even with a blank cell. I only want column A to have a checkmark if there is a date in column B, so that's why my formula in column A is incorrect and I can't figure out how to fix it. I really appreciate your help ! Thank you

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

Ask Your Question

Weekly Poll

Do you think Slack's direct listing "IPO" is a good idea?

Discuss in The Lounge

Poll History