SOLUTION: DAY and DATEONLY functions ignoring locale information

John C Murray

Users have been running into problems with the DAY function returning the wrong day and DATEONLY returning the wrong date. The reason is that these functions do not reference the user's locale information, and resolve against UTC time instead. That's fine if you live in the same timezone as Britain, but a tragedy if you live elsewhere on the globe.

This solution is based on converting the DAY component into text first and then manipulating it.

To simulate the DAY function, use the following:

= VALUE( LEFT( [Created Date]@row + "", 2 ) )

To simulate the DATEONLY function use something like:

= DATE( YEAR( [Created Date]@row), MONTH( [Created Date]@row ), VALUE( LEFT( [Created Date]@row + "", 2) ) )

This workaround will also work with other failing functions like WEEKDAY, WORKDAY, and so on

John C Murray

UPDATE:

My locale uses the date format DD/MM/YYYY, so the solution above needs to be modified to work in other locales. Here's an example for the USA which has been updated with extra robustness around change of month and change of year:

= VALUE( MID( [Created Date]@row + "", 4,2 ) )

To simulate the DATEONLY function use something like:

= DATE( VALUE( RIGHT( [Created Date]@row + "", 4 ) , VALUE( LEFT( [Created Date]@row + "", 2 ) , VALUE( MID( [Created Date]@row + "", 4, 2 ) ) )