Data Validation format issues

[02100kara]

I have a range of dates (E83:E161) in a validation on a table pictured. The range is 12 months of the current year 12 months of next year and each week of a year, a lot of options to choose from. I want the format for the next year month selection to be in format "month year" (ex. September 2023). all of the values in the range are made up of formulas because they're dynamic, the user has the option to select start year, start month, & sunday or monday as the beginning of the week.

The issue: Data Validation doesn't recognize the format of "month year" and gives an invalid entry error, stating its not an option in the range, but it most definitely is.
The work around was to put in MM-YYYY format in the range (ex SEP-2023) but in the drop down validation it shows as "9/1/2023" and not MM-YYYY. I tried changing the format of all cells involved and nothing works.
-The original formula in the referenced cells was made up of =CONCATENATE("September ", $B$3+1). This used to work but sheets did an update and now when I select this in the drop down it gives back the invalid error.
-format to MMM-YYYY, but this doesn't always show in the proper format when selected (ex: sometimes comes back as SEP-2022 often comes back 9/1/2022) and NEVER shows as the proper format in the validation drop down list when choosing(ex: 9/1/2022). Ive changed the format of the data validation cells, still doesn't work.
- I even tried using the =to_text() formula in the referenced cells. still gives invalid

Solution needed: Im eager to find a solution that shows just the month and year format in the validation and when selected because this selection brings back results for the whole month and seeing "9/1/2022" in the drop down doesn't make that apparent.

 

[Joe4]

OK, it looks like you may not fully understand how dates work in Excel.

Valid dates are really just numbers in Excel, specifically the number of days since 1/0/1900.
They are numbers with some sort of date format applied.
So Excel actually sees them as numbers (this can easily be seen by going to any valid entered date in Excel and changing the format to "General").

Changing the format of any number or dates does NOT changes its values, it only changes its appearance.
So even if you have a date like "9/1/2023" and change the format to "mmm-yyyy" to show "Sep-2023", the day portion is still really there, you just cannot see it with the format that you have chosen.

And anytime you use a date in a CONCATENATE or TEXT function, it will return a Text value, not a Date/Number value (without doing something to convert it back, if possible).

If you are not sure if an entry is entered as a valid Date (number) or if it is Text (which means you cannot use Date math on it), there are a few ways you can check that.

1. Since dates are really numbers, you can use the ISNUMBER function.
For example, if:
=ISNUMBER(A1)
returns TRUE, A1 is a valid date/number. If it returns FALSE, A1 is text.

2. Since formatting only works on numbers/dates, you can try changing the format of the cell.
If you change the format of the cell, and it does not change its appearance, then the value is NOT a valid date/number (it is text).
For example, if you have Sep-2023 in a cell, and change the format of the cell to "mm/dd/yyyy" and it still shows Sep-2023, then you have text (and not a valid date entry) in that cell.

Hope that helps clarify how dates and formats work in Excel, and things make a bit more sense.