By default, Excel stores the date January 1, 1900 as serial number 1, and January 1, 2016 as serial number 42,370. Which means that it is 42,369 days after January 1, 1900.
Now that we have that out of the way let me show you 4 simple Excel date functions that you should know.
Looking for a function that will always display today's date in a cell regardless of when the spreadsheet is open? Then the TODAY function is what you are looking for.
The TODAY function can also be used to count the difference between dates and even count down to a future date. The TODAY function does not take any arguments so don’t put anything in between the parentheses.
Examp
Example 2: In this example I am looking for the total number of days between the start date and today's date. Click in a blank cell and type in =TODAY()-B3 (Replace B3 with your start date). The result is displayed as a date. Open the Format Cells dialog box and change the date to a number.
Example 3: In this example I would like to return the difference between todays date and a future date. Click in a blank cell and type = then click on the cell with the future date. Subtract and type in TODAY(). The result is displayed as a date. Open the Format Cells dialog box and change the date to a number.
Pro tip: Working on a whole column of data using Excel date functions? Do you know about the fill handle?
The NOW function displays the current date and time whenever you open your worksheet. To find a future date type in =NOW()+30 to get the date 30 days from now (replace the 30 with your desired number of days). The NOW function does not take any arguments so don’t put anything in between the parentheses.
Exam
Pro tip: Getting unsightly errors? We can teach you how to replace those with IFERROR.
The DAYS function returns the number of days between two dates in a spreadsheet. The function only requires two arguments (end date, start date). It is important to select the end date followed by the start date otherwise your results will display a negative number of days. If this happens reverse the order of the cells you selected.
Example: In this example I am looking for the total number of days between the ending date and beginning date. Click in a blank cell and type in =DAYS(C8,B8) (Replace C8 and B8 with your ending and beginning dates). The result is displayed as a number.
Don't forget, Excel natively saves dates as serial numbers. Here is a screenshot of the Function Arguments dialog box when working with the DAYS function.
The TODAY and DAYS functions are great at finding the number of days between two dates. Let’s suppose you are only interested in the total number of workdays between two dates. That is where NETWORKDAYS comes in. NETWORKDAYS excludes weekends and holidays. This function uses the same format as the DAYS function described above.
Example: In this example I am looking for the total number of workdays in the month of July minus any holidays. Click in a blank cell and type in =NETWORKDAYS(B8,C8) (Replace B8 and C8 with your beginning and ending dates). There is an optional third argument that allows you to exclude state, federal, or floating holidays. In the screenshot below I added July 4 to a cell to include in the list of holidays.
Here is what the Function Arguments dialog box looks like. There are 21 workdays minus July 4 for a total of 20 workdays.
As you can see Excel date functions provide a quicker, more reliable ways to find the number of days between dates without having to count the days looking at a calendar! I don't know about you, but I always lose my place and have to start counting over again :-)
For more useful functions, see our blog post on the top 5 Excel hacks.