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

[JuicyMusic]

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.

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

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

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

 

[JuicyMusic]

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:

=IF(C40="December",$D$2-1,
IF(C40<>"December",$D$2,
YEAR(D40)))

C:

=IF(D40="","",
TEXT(D40,"MMMM"))

Thank you so much.

 

[JuicyMusic]

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:

=IF(OR(D40="",D40=0),"",
TEXT(D40,"MMMM"))

Formula in Column D:

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