Date Functions in Excel

Examples

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 the TODAY() function to figure out how long it's been since the last incident. The 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 F9: =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: =MONTH(A2) which returns 1 for January. There is also similar functions such as YEAR, DAY and 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 order: range, 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. =COUNTIF(D$2:D$14,2).

Give It A Try Yourself

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.