Date Comparison formula with varying formatting

[SamNew2Coding]

I have seen similar questions asked but have yet to find an answer for my circumstance. I have a Name Range (CDate) where a date is manually inputted into an excel file. I was using that name range in a formula to see if I should hide or show a column header. They are name ranges as well and are MonthEnd1, MonthEnd2, etc. I am slightly familiar from my research that excel records dates in a specific number format by default and formatting of the cells affects the comparison formula and can cause errors. Since I have data being imported to this template and have the name range values (CDate) changing here and there with each import, i was trying to see if i there is a way to do the date comparison in a formula regardless of that cells format.

This is what I've tried originally
=MonthEnd1<CDate,"",MonthEnd1

This seemed to have errors so i tried playing around with having the formula see them both as dates as its calculating but that didnt seem to work too well either
=IF(TEXT(MonthEnd1,"mm/dd/yyyy")<TEXT(CDate,"mm/dd/yyyy"),"",TEXT(MonthEnd1,"mm/dd/yyyy")

I have tried changing the cell format to be 'Short Date' for both name ranges (CDate and MonthEnd1) but still encounter issues. Anyone have any advice?

 

[mikerickson]

I think you need an IF in your original formula
=IF(MonthEnd1<CDate,"",MonthEnd1)

 

[Joe4]

Do you really have a range or variable named "CDATE"?
That is a big no-no. "CDATE" is already the name of an existing VBA function which converts a text entry to a date entry (see: MS Excel: How to use the CDATE Function (VBA)).
You should NEVER use reserved words (that is, names of existing functions, properties, or methods) as the name of variables, ranges, functions, or procedures in VBA. Doing so can cause ambiguity and unexpected results (as VBA cannot tell if you are referring to the variable "CDATE" or the function "CDATE").

Also, if you are trying to compare two dates, you do NOT want to use the TEXT function on them first! That converts the dates to TEXT, and then then will follow TEXT rules (i.e. "01/01/2020" will come before "12/31/2019"). Leave them as Dates. If they are both valid dates, you do not need to worry about their formatting before comparing. Excel does not care what format you choose to display your dates, because that is not how Exce;/VBA sees the dates. It seems them as numbers, specifically the number of days since 1/0/1900 (so all that a date is in Excel is a number, and you just use some date format for display purposes only).

 

[SamNew2Coding]

Do you really have a range or variable named "CDATE"?
That is a big no-no. "CDATE" is already the name of an existing VBA function which converts a text entry to a date entry (see: MS Excel: How to use the CDATE Function (VBA)).
You should NEVER use reserved words (that is, names of existing functions, properties, or methods) as the name of variables, ranges, functions, or procedures in VBA. Doing so can cause ambiguity and unexpected results (as VBA cannot tell if you are referring to the variable "CDATE" or the function "CDATE").

Also, if you are trying to compare two dates, you do NOT want to use the TEXT function on them first! That converts the dates to TEXT, and then then will follow TEXT rules (i.e. "01/01/2020" will come before "12/31/2019"). Leave them as Dates. If they are both valid dates, you do not need to worry about their formatting before comparing. Excel does not care what format you choose to display your dates, because that is not how Exce;/VBA sees the dates. It seems them as numbers, specifically the number of days since 1/0/1900 (so all that a date is in Excel is a number, and you just use some date format for display purposes only).

CDate isn't the real name of the range its ConversionDate but i shortened it for simplicity. I guess that was a shortsighted of me. My IF statement of =IF(MonthEnd1<ConversionDate,"",MonthEnd1) doesnt seem to work however. It may be because of the TEXT function you mentioned. The MonthEnd1 has the formula =TEXT(EDATE(MonthEnd,-12),"mm/dd/yyyy"). I have a Master MonthEnd that is manual which all other dates are formatted in a corresponding way off of. Would that be affecting my formula =IF(MonthEnd1<ConversionDate,"",MonthEnd1)? Shouldnt it not since it should just be referencing the cell value of the name range which itself is a date and cell formatting is 'Short Date'?

 

[Joe4]

The MonthEnd1 has the formula =TEXT(EDATE(MonthEnd,-12),"mm/dd/yyyy")

That makes MonthEnd1 text also.
If you want to compare MonthEnd1 and ConversionDate, don't make them text. Just leave them like:

=EDATE(MonthEnd,-12)

Why are you trying to convert them to Text?
Note that you can just format the cells where are located with a date format on the cell of "mm/dd/yyyy".

Alternatively, you could turn them back to date using the CDATE function in order to do comparisions, i.e.
CDATE(MonthEnd1)