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 the Tools menu and select Add-Ins. Ensure that the Analysis ToolPak checkbox is selected. If it isn't then select it and Excel will install the Add-In.
How Excel handles Dates
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.
Number of Days
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. If you take a look at Figure 1
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 24 days.
Figure 1: Excel Dates
Number of Business Days
Ok, that's easy, what about business days? Excel has a function for that called NETWORKDAYS. The NETWORKDAYS function has three parameters that it will accept. They are: start_date, end_date, and 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 Holiday 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 NETWORKDAYS(A3,A4,H4:H12) which would isn't correct since the holiday list is from H3 to H11.