# FORMULA IF DATEVALUE IN OTHER COLUMN IS IS "1/0/00", THEN RETURN BLANK

#### JuicyMusic

##### Board Regular
Well, here I am trying to tweak another "date" formula.

Column B: Year
Existing formula is not working if Column C doesn't have a month name value in it. I need the value in Column B to always be the year even if column C is blank AND column D has a "1/0/00" in it. This year column is sort of like a place holder.
Excel Formula:
=YEAR(D40)

Column C: Month name
Existing formula is giving me back #NAME? if Column D is blank AND is also giving me back "January" if there is a "1/0/00" in column D. I need this column to be blank if column D is blank or has a "1/0/00" in it.
Excel Formula:
=IF(OR(D40="",D40=DATEDVALUE(1/0/0)),"",
TEXT(D40,"MMMM"))

Column D: index/match result in date format. I would like to replace the "1/0/00" result with anything other than a blank cell. Maybe "--" would be acceptable. Here is the formula in column D, and this
one is working fine. But I'm open to suggestions.

Excel Formula:
=IFERROR(INDEX('401kFringesAndDeductionsEXPORT'!\$BX\$2:\$BX\$9000, MATCH(0, IF(\$A40='401kFringesAndDeductionsEXPORT'!\$BV\$2:\$BV\$9000, COUNTIF(\$D\$10:\$D39, '401kFringesAndDeductionsEXPORT'!\$BX\$2:\$BX\$9000), ""), 0)),0)

Here is a small snapshot of my worksheet. Sorry that I'm not allowed to upload it.

Thank you so much. Juicy!

Here is a snapshot of my worksheet.

#### Attachments

• Capture_Dated value issue.PNG
8.3 KB · Views: 7

### Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

#### JuicyMusic

##### Board Regular
This is an update. I've rewritten the formula in Column B and C. Now I only need the formula in column D to return a BLANK instead of "1/0/00" .

Here are my new formula's in Column B & C below. I made no changes to the formula in column D.

FYI - IN CELL D 2 IS THE YEAR: 2021

B:
Excel Formula:
=IF(C40="December",\$D\$2-1,
IF(C40<>"December",\$D\$2,
YEAR(D40)))

C:
Excel Formula:
=IF(D40="","",
TEXT(D40,"MMMM"))

Thank you so much.

#### JuicyMusic

##### Board Regular
SOLVED? Good morning everyone. I came in this morning and my post had no response so I relaxed and figured out how to change the formula in Column C
I decided to leave the "1/0/00" result in Column D but to change the formula in Column C - so that it doesn't return a "January" if there is "1/0/00" in column D. Here is my formula in Column C now:

Formula in Column C:
Excel Formula:
=IF(OR(D40="",D40=0),"",
TEXT(D40,"MMMM"))

Formula in Column D:
Excel Formula:
=IFERROR(INDEX('401kFringesAndDeductionsEXPORT'!\$BX\$2:\$BX\$9000, MATCH(0, IF(\$A40='401kFringesAndDeductionsEXPORT'!\$BV\$2:\$BV\$9000, COUNTIF(\$D\$10:\$D39, '401kFringesAndDeductionsEXPORT'!\$BX\$2:\$BX\$9000), ""), 0)),0)

I love this part of learning.! Trying and doing it yourself and your understanding increases. I know that this was a simple fix - but it's a good sign anyways.

Thanks!

Replies
9
Views
241
Replies
2
Views
98
Replies
5
Views
144
Replies
1
Views
225
Replies
2
Views
152

1,148,154
Messages
5,745,097
Members
423,922
Latest member
Taconumber

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back