Forum Discussion
Excel TEXT function unexpected behaviour
Unfortunately this is not correct! I tested with both manual inputs and Cell inputs - e.g. see below:
Input "=TEXT(Input, "yyyyMMdd)
02/11/2022 02/11/2022
02-11-2022 02-11-2022
2022-11-02 20221102
02-Nov-22 20221102
Can you please add a document that you have a problem?
i tried to show you how it works. did you write the formula below in quotation mark?
Formula: =TEXT("44867","yyyyMMdd")
(NB: This is not the same behaviour across computers! testing this on a colleague's computer resulted in every cell in the right column displaying "20221102" correctly - we have the same laptop model and Windows/Excel install versions)
It depends on your system date Format - does Excel recognize the first argument of TEXT as a date or not?
Apparently, 02/11/2022 and 02-11-2022 are not recognized as dates on your computer, so TEXT leaves them unchanged.
On a computer with system date format dd/mm/yyyy, the result would be different:
And on a computer with system date format mm/dd/yyyy:
(Note the first two!)
- Interesting! My computer region is Australia (DD/MM/YYYY), but I use ISO standard date formatting YYYY-MM-DD. That could be the problem.
How can I tell Excel (or the TEXT function) to treat a particular worksheet/input using DD/MM/YYYY even if that differs from my system clock settings? I'd need it to work across multiple computers in my org including ones set to ISO standard YYYY-MM-DD AND normal Australian date formatting DD/MM/YYYY.
