Date Formatting of Selection List, when Data Validity is defined with a formula

[gairsty]

drop-down-formatting.ods (14.2 KB)

I have a Selection List for a range of dates, where the Data Validity of the range is determined by a formula.
If the data in the range defined by Data Validity is formatted as Number or Currency, the Selection List (drop down) displays the correct formatting.
However, if the data is formatted in a Date format, it fails to display in the Selection List in a Date format. The date 01/05/22 will display as 44682.

If the range is not defined by formula in Data Validation (ie $Sheet1.$B$4:$Sheet1.$B$15) the Selection List performs as expected.

In the attached file cell D10 is the one that is not working as hoped.
D9 is the straightforward range definition.
I would like the drop down to display in a Date format.

Thanks in advance!

[erAck]

The formula

(INDIRECT(CONCATENATE("$B";(3+$D$8)))):$B$15

in array context generates an array of values, such arrays do not carry formatting information. The numeric values you see are the date serial numbers of the date values (which if formatted as date display a date).

[erAck]

You could use the validity formula

TEXT(INDIRECT(CONCATENATE("$B";(3+$D$8))):$B$15;"YYYY-MM-DD")

that displays ISO 8601 formatted dates suitable in any locale.
You could also use a format "MMM D, YY" as in the source data but that would be fragile as it would be locale dependent and may (will) not work in other locales.

[gairsty]

Thank you, that has cured the problem. I suspected it was something like that, but didn’t understand enough to find the solution!