Excel TEXT function unexpected behaviour

Contributor

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

@Austen_E 

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")

Sekoleyte_1-1667373404366.png

 

"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 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")

@Sekoleyte 

 

Austen_E_0-1667421323737.png

(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)

@Austen_E 

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:

S1938.png

And on a computer with system date format mm/dd/yyyy:

S1939.png

(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.

@Austen_E 

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.

No worries Hans:
We have equipment that outputs CSV data with dates set (immutably, unfortunately) to 'DD/MM/YYYY' - this is processed into our reporting tools using an Excel worksheet that (among many other things) converts at least one date string to 'YYYYMMDD' using a TEXT function.

I 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 - ideally by figuring out a way to force the spreadsheet or TEXT function to accept either date format.

Workarounds that I've considered but don't want to do:
- Just slice the date string up manually instead of using TEXT (I would like the spreadsheet to be maximally robust against future equipment changes including e.g. one where the date output format changes)
- Just change the machine settings away from ISO standards (we are actually moving as an org in the other direction!)
- Anything more complicated than changing the formula/worksheet settings (this spreadsheet is not mine, and it's got a definite spaghetti code vibe - I'm concerned that more complicated 'fixes' will unstick other bits and require a lot more effort)

Possible there is something else that will solve this problem, but I anticipate this coming up again in other situations while we swap our machines over to ISO standard dates, so I'd like to learn if there is any way to do a fully general fix for legacy workbooks to recognize non-system date formats.

@Austen_E 

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).

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)

@Austen_E 

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))

OK - so far from various attempts at satisfactory workarounds it looks like the TEXT function is just locked into the OS date/time settings, and has reduced functionality when the OS is using ISO standard versus e.g. Australian standard (where TEXT also recognizes ISO standard dates).

This issue appears to replicate with the DATEVALUE function, and appears to be a generalization of a broader problem where individual Excel files, worksheets, and cells cannot be set to use anything but the system Date format.

Country codes allow you to specify output to a specific region, but apparently cannot be used to specify inputs as being from a specific region.

I'll keep this thread open in case anyone has a brilliant solution to the meta-problem, and will use one of the less satisfying hacky workarounds to put a band-aid over the object level problem.

@Hans Vogelaar @Sekoleyte thank you for the suggestions!

@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.

 

 

Good call! I have unmarked it for now.

The whole file already uses PQ to process the CSV! I've been trying to avoid digging too hard into it (since at the end of the day, this is a legacy tool and the whole process is going to be hopefully automated soon enough).

However, curiosity got the better of me this morning and I cracked open the Query structure.

The date is already correctly transformed into the system setting (2022-11-02) until the very last step! There is a final TransformColumnTypes which turns it back into text - despite the lack of a culture parameter it is defaulting to Australian standard (even though my machine is running ISO standard). Very curious!

So it takes the (correct) 2022-11-03, and then transforms it to 3/11/2022, which then TEXT (apparently locked to system settings) cannot actually convert back to a date later - I guess PQ and Excel don't run off the same logic here.

It took far more work than it should have to find the list of culture codes (https://learn-microsoft-com.translate.goog/en-us/openspecs/windows_protocols/ms-lcid/a9eac961-e77d-4...)

However, stymied again! I can tell TransformColumnTypes to convert the date to en-AU or en-US, but there appears to be NO option to Transform it to ISO standard formatted text!

So - more questions:
- Why does TransformColumnTypes default to converting ISO to en-AU dates, but TEXT fail at recognizing en-AU dates on the same system?
- Is it possible to tell TransformColumnTypes to convert a date to ISO standard? Or only to e.g. en-AU or en-US?

Curiouser and curiouser!