In our daily work, we need to work with dates in Excel. Doing it the right way not only makes the task easy, but also ensures input of correct data. Once dates are correctly entered, we can find the number of days between two dates. This is necessary for interest calculation, fees calculation, etc. We can also calculate totals for a range of dates in case of sales data, group dates to get sales totals for a month and so on.
Suppose we input 05/12/10 in an Excel cell. Excel may retain that format or it may change the display to something else. The behaviour is often puzzling. Has Excel understood the date as 5th December 2010 (as I intended) or has Excel interpreted it as 12th May 2010 which will certainly result in wrong calculations? How do I enter dates as dd-mm-yy as we do in India? (while at the same time ensuring that my friend Bob in the US sees them as mm-dd-yy as they do there)
How do Excel dates work and how can we ensure better control over dates?
First step: Getting the dates settings right in Windows.
Windows Regional Settings (Windows XP):
Go to Start -->Control Panel --> Regional and Date Settings. In the dialog box (see below), select
English (United Kingdom) and click on Customize...:
On clicking Customize you will see the following dialog box:. (Ensure that you click on the Date tab at the top of the dialog box to make it the active tab):
Click on the down arrow to the right of the Short Date format and select dd-MM-yyyy format
Click on the down arrow to the right of the Date Separator and select from . (period/full stop) or - (hyphen) or / (slash) . You are indicating to windows that you will be using this as the date separator while inputting dates. Please select your preferred separator.
Click on the down arrow to the right of the Long Date format and select dd MMMM yyyy
Click on OK, OK and come out of the Control Panel.
Once we modified settings as above and assuming the date separator selected is "-" (hyphen), we can now input "10-05-10" and be sure that Excel has understood it as 10th May 2010.
(To verify this, select the cell with the date and press Ctrl + Shift + #
The date will now be displayed (formatted) as 10-May-10 or 10/May/10 depending on the date separator setting.)
How are two digit years interpreted? If we input a two digit year from 00 to 29, Excel will prefix it with 20. Thus 10 becomes 2010, 15 becomes 2015, 05 becomes 2005 and so on. If we input a date from 30 to 99, Excel will prefix it with 19. Thus 56 becomes 1956, 31 becomes 1931, 99 becomes 1999 and so on. If we intend to input 2030, we will need to input it as a four digit year i.e. 2030. Similarly, if we intend to input 1910, we will need to input it as 1910 and not 10 because 10 will be converted to 2010. (This is because of the settings in the Date tab of Control Panel - see graphic above).
Also, note that although we have input the date in the dd-mm-yy format as above, the person to whom we mail an Excel workbook will read the date in format as in his machine. So 10th May 2010 which is displayed as 10-05-2010 in my machine will be displayed as 05/10/2010 in his machine if his Regional Date settings are mm/dd/yyyy
Important Note: Excel stores dates internally as numbers. This is the reason why a date is correctly displayed as either 10-05-10 or 05/10/10 in machines with different date settings. Display of dates can be controlled through formatting which will be discussed separately.
(The above settings in Control Panel need to be made only once in a PC. If you are not able to do this in an office environment, it could be because you are not logged in with administrator rights. In which case, you need to contact the System Adminsitrator for help).
