Lotus Notes Formula Using @TextToTime In USA And Europe
Most of you know that in the United States dates are shown in MM/DD/YYYY format, where many parts outside the U.S. use DD/MM/YYYY format. (And us Americans are also clueless about the metric system that everyone else uses, but that’s another topic). Anyway, on to the tip. What if you have a text date in a certain format and need to convert it to a Notes date-time value?
I did some checking – one machine in MM/DD/YYYY format (I’ll call this “US format”), and another machine in DD/MM/YYYY format (I’ll call this “Europe format”). I took a text string of “12/31/2004″ and applied @TextToTime to the text string. In the US format, the date value was 12/31/2004 and in the Europe format, the date value was 31/12/2004, which are both correct.
However, if I changed the text string to “01/02/2004″ and then applied @TextToTime to it, both the US and Europe formats were 01/02/2004, which would be January 2nd in the US and February 1st in Europe. I needed to come up with a way to make sure the text was in the right format before applying @TextToTime to it.
(Basically, I knew the text was always going to be in MM/DD/YYYY format, but I needed a way to find out what setting the user had to make sure that when @TextToTime was applied, the date-time value would end up being correct).
Based on my earlier testing, I knew that using a date of December 31st would apply correctly – the resulting value would be either 12/31 or 31/12 based on your format. So I used this knowledge to find out if the format was MM/DD/YYYY or DD/MM/YYYY. (My code doesn’t look at other formats right now; if someone wants to enhance the code or provide suggestions that will handle other formats, please post comments).
Dec31 := @TextToTime(“12/31/2000″);
IsMonthFirst := @If(@Left(@Text(Dec31); 2) = “12″; @True; @False);
Those two lines of code create a date-time value based on December 31st, 2000. Then applying @Text to that value will result in either “12/31/2000″ or “31/12/2000″, based on your format. If the first two characters are “12″ then the month is first in your format.
Months := @Left(DateString; “/”);
Days := @Right(@LeftBack(DateString; “/”); “/”);
Years := @RightBack(DateString; “/”);
The variable DateString holds my text date in MM/DD/YYYY format. (It’s built in a way that I can guarantee that the format is always that way, no matter where the user is located). So I split that string up into three pieces – months, days, and years.
NewDateString := @If(IsMonthFirst; Months + “/” + Days + “/” + Years; Days + “/” + Months + “/” + Years);
@TextToTime(NewDateString)
The function then builds a temporary text variable called NewDateString in either MM/DD/YYYY or DD/MM/YYYY format, based on whether the months came first or not in the earlier check of December 31st. Then that text string is converted to a date, and I can be sure it has the correct format (assuming I’m starting with one of the two formats discussed here).
Viewed 7462 times by 2081 viewers
