Forum Discussion
Excel TEXT function unexpected behaviour
I'm in a pickle with an Excel formula using the TEXT function in a legacy spreadsheet used in my organization.
Unexpected Behaviour
Formula: =TEXT("2/11/2022","yyyyMMdd")
Expected Result: 20221102
Actual Result: 2/11/2022
Formula: =TEXT("02-11-2022","yyyyMMdd")
Expected Result: 20221102
Actual Result: 02-11-2022
Other Tests Performed
Formula: =TEXT(TODAY(),"yyyyMMdd")
Expected/Actual Result: 20221102
Formula: =TEXT("02NOV2022","yyyyMMdd")
Expected/Actual Result: 20221102
I'm absolutely baffled right now - this seems to work consistently on everyone else's computers but my own. What could be causing this?
-Austen
15 Replies
Hi there.
2.11.2022 in date format equals 44867 as a number. if you write 44867 instead of 2.11.2022 it will work.
Formula: =TEXT("44867","yyyyMMdd")
if you use cell reference instead of quotation mark, it always works no matter what the format is:
Formula: =TEXT("A1","yyyyMMdd")
Formula: =TEXT("B1","yyyyMMdd")
- "if you use cell reference instead of quotation mark, it always works no matter what the format is:"
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 20221102Can 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")
