Nov 02 2022 12:00 AM
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
Nov 02 2022 12:17 AM - edited Nov 02 2022 12:18 AM
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")
Nov 02 2022 12:22 AM
Nov 02 2022 12:36 AM - edited Nov 02 2022 12:37 AM
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")
Nov 02 2022 01:37 PM
(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)
Nov 02 2022 01:59 PM
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!)
Nov 02 2022 02:35 PM
Nov 02 2022 02:41 PM
Perhaps we might be able to help you better if you told us why you want to do this and what you need it for.
Nov 02 2022 02:54 PM
Nov 02 2022 03:12 PM
I think you've just ruled out all possible solutions...
Moreover, recognizing ALL date formats is difficult: if you have a text value 02/11/2022, you have no way of knowing it is the 2nd of November (standard Australian date format) or the 11th of February (USA date format).
Nov 02 2022 03:29 PM - edited Nov 02 2022 03:30 PM
If I can get the behaviour from the TEXT function that it has on computers set to Australian standard (recognizes both 02/11/2022 AND 2022-11-02) I'll call it a win! Is there any way to accomplish this on a computer set to ISO standard dates?
(NB: I'm currently playing around with LCID stuff and will post in this thread if I find a valid workaround)
Nov 02 2022 03:54 PM
It would require "slicing the text" but you don't want to do that...
=IF(ISNUMBER(A2),TEXT(A2,"yyymmdd"),RIGHT(A2,4)&MID(A2,4,2)&LEFT(A2,2))
Nov 02 2022 04:15 PM
Nov 03 2022 12:26 AM
@Austen_E By marking your own answer as the best response you have basically closed this thread. Consider to unmark it!
Have you tried importing the CSV with the legacy text wizard or Power Query (PQ), rather than just opening the CSV in Excel?
I would favour PQ as you desire an automated solution and mentioned that the CSV always comes with dates like DD/MM/YYYY.
In PQ you connect to the CSV and tell it that "this column contains dates that come in a particular local date format". You can choose from all locales that exist in the world. Pick a European one like English-UK. That will will do in this case, I believe.
Then PQ will transform the dates to real date values, that are recognised by the user's system. Set it up correctly once and teach the user to push a "Refresh All" button when a new CSV needs to be processed.
Nov 03 2022 02:43 PM