Specific date input changes while custom format is selected

[SteveNL86]

Dear reader,

I am running into an issue with a cell in which I'm changing a certain date.

Currently I'm putting the date in it as such: 2021/01/16. When I change the date, the input changes to 22-4-2021 (in the top) for example, even when I've selected the format to be yyyy/mm/dd.

Is there a way to force it stay on that format? I'm using a hyperlink with a index and match formula which keep failing because of way it changes the input. It won't recognize the yyyy/mm/dd date in other sheets because of it.

Any help is much appreciated.
Regards.

 

[jasonb75]

he input changes to 22-4-2021 (in the top) for example

If you mean the formula bar, that is based on your regional system settings and can not be changed. The format is only a visual effect on the cell.

To preserve the format for a hyperlink you would need to nest the TEXT function with your existing formula, otherwise it will likely be using the date serial number rather than the actual date.

 

[SteveNL86]

Thanks, I'll take a look if I can get that to work then.