Date & Time Format Issues

[Darren_Murphy]

If you’re allowing users to enter dates as strings, then that is just asking for trouble.
My first bit of advice would be to avoid that if possible, and use a date picker.

If you don’t want to do that, then my suggestion would be to use a math column to convert the entered “dates” to integers, and then work with those. Use the following:

Year(Date) * 10000
+ Month(Date) * 100
+ Day(Date)

The math column seems to be pretty good at dealing with dates as strings, so you might have better success with that.

The last thing I would use in this situation is the Format Date column. I gave up on that many many months ago, and I just don’t use it at all now. I wrote a bit about why below:

Time Format

The other thing you need to do is test it at different times of the year. Early on, when I was trying to use it, I found that it would sometimes get the day and month mixed up, but only under certain circumstances, and only if the date was ambiguous. For example, today is September 25th. In the US, they write that as 9/25/2022. In the real world, we write it as 25/9/2022. But either way, it’s obvious that it’s September 25. It can’t be anything else, because there is no 25th month. But, le…