Working with dates in Excel can sometimes be a little confusing. Excel stores dates as sequential numbers that can be used in calculations.
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.
The TODAY Function =TODAY()
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
le 1: In this example I am looking to return the current date in a cell. Click in a blank cell and type in =TODAY() followed by the enter key. The result is formatted as a short date. Open the Format Cells dialog box if you wish to change the formatting of the date. The function will continue to display the current date each time the worksheet is opened.
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 =NOW()
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
ple: In this example I am looking to return the current date and time in a cell. Click in a blank cell and type in =NOW() followed by the enter key. The result will display both the current date and time. Open the Format Cells dialog box if you wish to change the formatting of the date and time. The function will continue to display the current date and time whenever the worksheet is opened.
Pro tip: Getting unsightly errors? We can teach you how to replace those with IFERROR.
The DAYS Function =DAYS()
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 NETWORKDAYS Function =NETWORKDAYS()
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.
