Using Dates Within Excel

Dates can be daunting for the beginner within Excel, particularly as Excel stores dates as decimal numbers and not actual dates. This is due to the fact that many people need to use date cells within formulas. A simple example would be a worksheet where there are two columns, one as a start date of a project and the next as an end date. If someone needed to calculate the length of a project then they could use a formula to easily find the total project time. Excel stores dates as numbers so these calculations can be performed. This does mean that you will need to consider date formats when using dates within your worksheet. If you accidentally format the column as number type then all your dates will appear as meaningless numbers!

There are many different ways to format dates and many formulas that can be performed upon them. There are some simple functions you can use to get started. Open Excel and type the following into the first cell (A1) and then click on a different cell or press the enter key:

=NOW()

Excel will automatically add the current date and time in the cell and format this cell as a date, but it may not be the format you would like. Right click on the cell and choose 'Format Cells.' In the first tab you will see that you can select 'date' as the format type and select a format from the list. You can also choose 'Custom' and set your own format type such as:

dd/mm/yyyy hh:mm

You can format any date cell in this way and it is advisable to do so in order to keep the format consistent throughout the worksheet. It can look very unprofessional and confusing if there are several date formats in one sheet. You can format an entire column in a particular date format by right clicking on the column header and formatting as shown above. Experiment with different date formats and consider your target audience. Different locations across the globe use different date formats. It is best to find out what format they use and customise it for them to avoid confusion.

Once you have mastered the formatting of the date cell, it is useful to attempt some simple formulas. In the cell B1 type 01/01/2009 and in the cell underneath B2 type 01/02/2009. In cell C1 type the following formula and press the enter key:

=SUM(B2-B1)

The result of this formula should appear as '31/01/1900.' This is clearly not what we are aiming for! This is because the cell where the formula is typed will automatically be set as date since you are subtracting two date cells. Format the result cell as a number and the result should appear as 31. We can tell there are 31 days between the two dates. This is a very simplified example and in most professional worksheets, you will not perform calculations on dates in this way. Instead it is more likely that you will use the DATEDIF function. This function allows you to perform the calculation, but can give you the result in the time interval you specify such as months, years or days. Delete the above formula in cell C1 and type the following:

=DATEDIF(B1, B2, "d")

When you press the enter key, you will notice that the result is the same. It is basically telling Excel to perform the same calculation. Now change the formula to the following:

=DATEDIF(B1, B2, "y")

You will notice that the formula result is now zero as we have asked Excel to calculate the number of years between the two dates by entering "y" as the interval. It may be that you will need to calculate the difference between today's date and a large number of dates given in a column. It would be possible to use the above formula, but it will be necessary to add a column called today's date which will repeat over and over and will need to be continuously updated. Instead, you can use the =NOW() function given above in one cell which will refresh when the workbook is opened. You can then add a Datedif formula using an absolute reference to this cell. An absolute reference means a cell in a formula that will never change. When you drag a formula to other cells, Excel will automatically update the formula to be appropriate for the row and cell change. An absolute reference tells Excel to use the same cell and not automatically update the formula. The following is an example of the formula you may use:

=DATEDIF(B1, $A$1, "d")

In this formula the cell A1 contains the =NOW() formula i.e. the current date. B1 is the date I want to calculate from. I am requesting how many days between a date in the past to the current date. The dollar signs creates the absolute reference. If I drag the formula down in a column, B1 will be incremented for each row but the current date cell will remain the same. In this way I can use the same formula for many rows of data.

The simple examples shown above are meant only as a taster to the magical world of Excel date formulas! Worksheets containing date information are extremely common and it is vital that you understand formatting and some formulas in order to make sense of the data. If you use Excel regularly it would be worth investing in a course to cover these topics and others in more depth. You can never know too much about excel and it has infinite uses in the workplace. You don't need to know everything, you just need to know more that the person sitting at the desk next to you so get involved!