Date Comparison formula with varying formatting

SamNew2Coding

New Member
Joined
Jun 15, 2020
Messages
11
Office Version
  1. 365
Platform
  1. Windows
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?
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,965
I think you need an IF in your original formula
=IF(MonthEnd1<CDate,"",MonthEnd1)
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,651
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jun 15, 2020
Messages
11
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,651
Office Version
  1. 365
Platform
  1. Windows
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:
Excel Formula:
=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)
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,127,587
Messages
5,625,662
Members
416,124
Latest member
DeMoNloK

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