Date functions in Excel
If you're using dates in one of your spreadsheets and you find yourself manually updating those dates then you should consider looking into the many Data and Time functions available in Excel. I'll cover some of the ones I find more useful and then provide some real world examples of how to use these functions.
Note: Some of the functions I discuss require that the *Analysis ToolPak add-in be installed. To do this in Excel, go to Tools->Add-Ins. Ensure that the Analysis ToolPak checkbox is selected. If it isn't then select it and Excel will install the Add-In.
Excel handles dates as sequential numbers starting from January 1, 1900. Any date in excel can be converted into a 'serial number' which makes it easy to perform calculations using dates. For example, January 1, 1900 is serial number 1 and February 10, 2006 is serial number 38758.
Say you have a large list of dates and you want to determine the average number
of days between dates. An example might be an organization that tracks when
incidents occur by logging the date of the incident. Look at Figure 1 above
and you can see that there is a list of dates when incidents occurred. The
second column calculates the number of days between those dates by simple use of
subtraction. The formula used in cell B3 is
=A3-A2 which gives a result of
What about business days? Excel has a function for that called
NETWORKDAYS function has three parameters that it will accept:
holidays. This is why I have a list of holidays
in column H, it tells the
NETWORKDAYS function to omit those dates as
business days. The
holidays parameter is optional but you must enter a
start_date and an
end_date. To calculate the business days the formula in
C3 looks like this:
=NETWORKDAYS(A2,A3,H$3:H$11) which returns 17. The
dollar signs prevent excel from automatically incrementing the cell numbers when
copying and pasting this formula. If we didn't use the dollar signs and copied
the C3 to C4 the formula would look like
which isn't correct since the holiday list is from H3 to H11.
Now that we have the number of days between incidents we can figure out a few
things such as the longest or average time between incidents. We can also use
TODAY() function to figure out how long it's been since the last incident.
TODAY() function returns today's date, which means whenever you open the
spreadsheet it will automatically have today's date in the cell. We also know
that the last date an incident occurred which allows us to figure out how many
days since the last incident. The functions are as follows: Today's Date in cell
=TODAY() and # Bus. Days Since Last Incident in cell F11:
=NETWORKDAYS(F10,F9,H3:H11). The dollar signs aren't really necessary here
since I'm not copying and pasting that formula.
In the month column we use the
MONTH function which takes a serial number as a
parameter. This is what the function looks like for cell D2:
which returns 1 for January. There is also similar functions such as
WEEKDAY which returns the day of the week for a specific date.
To count up the incidents per month use the
COUNTIF function which will only
count something in a list of numbers if it meets the criteria you specify.
Obviously for January we only want to count an incident in the list of incidents
if it happened when the month was 1. The
COUNTIF accepts 2 parameters in this
criteria. The range is the entire range of cells you want to
look at and the criteria tells the function when to count a cell. For the
January case the formula in cell F15 looks like this:
=COUNTIF(D$2:D$14,1). For February it is the same except the criteria is that
the Month has to be a 2, e.g.
These are just a few examples of what can be done with Dates in Excel. I encourage you to take a look at the Excel Help file as it contains a lot of great information on how to use the functions mentioned in this tutorial and others that were not.