So what about:
=TEXT(43592,"mm")&"/"&TEXT(43592,"dd")&"/"&TEXT(43592,"yy")
Or reference a cell where you have put your date.
Answer from JvdV on Stack OverflowHi! I am hoping someone can help me! I have information in column A of an excel. They are specifically names of companies. I want to standardize the naming convention of these names and then re-import into my software. Is there a way to have a master list of how we want the companies to be named and then it can match that up with the way that they are currently named and then update/change it so that we can re-import into our software with all the names being the same? I'm trying to avoid having to do this manually. Thanks!
Videos
So what about:
=TEXT(43592,"mm")&"/"&TEXT(43592,"dd")&"/"&TEXT(43592,"yy")
Or reference a cell where you have put your date.
Unfortunately I don't think it is possible - see the this link
Quote:
Problem: The value in the date_text argument is not in sync with the system’s date and time settings*
If your system date and time settings follow the mm/dd/yyyy format, then a formula such as =DATEVALUE(“22/6/2000”) will result in a #VALUE! error. But the same formula will display the correct value when the system's date and time is set to dd/mm/yyyy format.
Solution: Make sure that your system’s date and time settings (both Short time and Long time) matches the date format in the date_text argument.
I think the only solution is to make sure that your date is in the valid (i.e. recognised by the system) date format before any other function (like DATEVALUE) is applied.
It will be easy to achieve if your function is simply =text(5/7/19,[Windows date format]). You can reference your cell field (e.g. =[@Date]) and format a given cell using an "universal" date format (the ones that start with * sign).
However, if you are combining =text(5/7/19,[Windows date format]) with some other formula (e.g. when you want to display a full sentence like A very important event happened on 5/7/19) then, unfortunately, cell format won't save you here and we're out of solutions. In such case the end user would need to manually modify the date cell in order to make it recognisable.
I’ve searched everywhere and cannot find a solution to this. I want to format cells to a standard format where the user inputs a short number and the cell displays the text “AB23XXXXXXX.” The standardized portion is the “AB23” with the remaining portion being zeroes (AB230000000) but the result incorporates the input text. First example: user inputs the number “1” and the result is “AB230000001.” Another example: user inputs “23985” and the result is “AB230023985.” The result should always be 11 characters long, begin with AB23 and flex zeroes to fill the gaps.
Any solution is appreciated!