Passing a spreadsheet around between organizations has a hidden problem; one that can easily make trouble. And the trouble comes, not from the spreadsheet, but from the default date setting on the computer.
Excel uses the default date setting to interpret the order of date information. Whether its’ Month-Day-Year or Day-Month-Year, or even Year-Month-Day, that information comes from the OS date settings. These are settings that we don’t often think about once we’ve set them. And typically, they are the same throughout an organization.
But take the spreadsheet you’ve designed, that uses Month-Day-Year into a Day-Month-Year organization, and all sorts of problems crop up.
The first problem is that you might not notice immediately; if July 6, turns into June 7 – that might not jump out at you as a problem. If you are lucky, you’ll spot something weird about the 12th of Month 21 …
So how do you nail down those dates so they can’t shift? One strategy is to break up your date entry into your preferred format, and then rebuild the date using the DATE function.
The syntax for the DATE function is =DATE(year, month, day)
Here you can see the DATE function is building a date from the values in three separate cells: A3, B3 and C3 and the formula looks like this =DATE($C3,$B3,$A3)
Another advantage of this strategy is that Data Validation can be applied to these cells; ie the day column can be restricted to whole numbers between 1 and 31, the month column to whole numbers between 1 and 12 and the year column as well. In the sample file I’m using, the column holding the complete date (D) is hidden from the user. They will only see columns A thru C. The complete (and correct) date is referenced in formulas.
An alternate strategy would be to use the DATE function to extract the correct order from a whole date typed into a cell. In this case you would need to rely on the users to enter the date consistently regardless of their date system. I would recommend a custom date format be applied and a comment to tell the user what the required date format is. Breaking the date up avoids this reliance on the user’s compliance.