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

JuicyMusic

Board Regular
Joined
Jun 13, 2020
Messages
159
Office Version
  1. 365
Platform
  1. Windows
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
    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
Joined
Jun 13, 2020
Messages
159
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 13, 2020
Messages
159
Office Version
  1. 365
Platform
  1. Windows
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!
 
Solution

Forum statistics

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

Disable AdBlock

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
Top