Excel Date/Time Formats
Excel the well known spreadsheet application enables great calculating functionality, providing you understand some of the rules that apply. Almost all projects and applications in Excel use dates and times in some way. A project may need to determine the number of days between two dates, or determine the date that is so many days in the future.
Before you can take advantage of some of the more power date and time formulas, you have to understand how Excel stores dates and times. Regardless of how you have formatted a cell to display a date or time, Excel always internally stores dates And times the same way.
If you share workbooks across buildings, areas or globally the date and time format must be set to a common standard or you will experience problems with the end results. Excel date and time format must be set in the Regional Settings.
How do I achieve this?
Click the start button, Control Panel, Regional and Language settings, the Regional and Language Options dialogue box opens.
Click the Customize this format button, the Customize Regional Options dialogue box opens. Select the Date tab. Under the heading Date Formats click the space and (physically) type in the preferred date format ie dd/MM/yyyy. Do the same for the Time format.
This will ensure that no matter how you share a workbook, where more than one person actively adds data the date and time calculations are not corrupted.
Companies would benefit if they took action and made a corporate decision to standardise a specified date/time format that all employees are required to adhere to.
Geraldine Somerset 23/07/2010