Forum Discussion
Excel TEXT function unexpected behaviour
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.
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.
- 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-41a6-90a5-ce1a8b0cdb9c)
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! 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.
HansVogelaar Sekoleyte thank you for the suggestions!
- 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. 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))
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)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).
- 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.
