Videos
I get addresses that people manually type in and they are all over the place in terms for formatting. Some people say West some people just use W. Some times the W is in front of the street number like W 1203 Dan Street and it should be “1203 West Dan street”.
You probably get the point. Is there a way a formula can take all the various inputs and create one standard address forma? Doing each by hand right now…
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.