Excel 2016 contains a number of built-in Date functions that you can use in your spreadsheets. When you install and activate the Analysis ToolPak add-in, you have access to a number of additional Date functions — many of which are specially designed to deal with the normal Monday through Friday, five-day workweek (excluding, of course, your precious weekend days from the calculations).
TODAY
The easiest Date function has to be TODAY. This function takes no arguments and is always entered as follows:
=TODAY()
When you enter the TODAY function in a cell by clicking it on the Date & Time command button's drop-down list on the Ribbon's Formulas tab or by typing it, Excel returns the current date by using the following Date format:
9/15/2016
Keep in mind that the date inserted into a cell with the TODAY function is not static. Whenever you open a worksheet that contains this function, Excel recalculates the function and updates its contents to the current date. This means that you don't usually use TODAY to input the current date when you're doing it for historical purposes (an invoice, for example) and never want it to change.
If you do use TODAY and then want to make the current date static in the spreadsheet, you need to convert the function into its serial number. You can do this for individual cells: First, select the cell, press F2 to activate Edit mode, press F9 to replace =TODAY() with today's serial number on the Formula bar, and click the Enter button to insert this serial number into the cell.
You can do this conversion on a range of cells by selecting the range, copying it to the Clipboard by clicking the Copy button on the Home tab of the Ribbon (or pressing Ctrl+C), and then immediately pasting the calculated values into the same range by choosing the Paste Values option from the Paste command button's drop-down menu (or pressing Alt+HVV).
DATE and DATEVALUE
The DATE function on the Date & Time command button's drop-down menu returns a date serial number for the date specified by the year, month, and day argument. This function uses the following syntax:
DATE(year,month,day)
This function comes in handy when you have a worksheet that contains the different parts of the date in separate columns, similar to the one shown here. You can use it to combine the three columns of date information into a single date cell that you can use in sorting and filtering.
The DATEVALUE function on the Date & Time button's drop-down menu on the Formulas tab returns the date serial number for a date that's been entered into the spreadsheet as text so that you can use it in date calculations. This function takes a single argument:
DATEVALUE(<i>date_text</i>)
Suppose, for example, that you've made the following text entry in cell B12:
'5/21/2016
(Remember that when you preface an entry with an apostrophe, Excel inserts that entry as text even if the program would otherwise put it in as a value.) You can then convert this text entry into a date serial number by entering the following formula in cell C12 next door:
=DATEVALUE(B12)
Excel then returns the date serial number, 42511, to cell C12, which you can convert into a more intelligible date by formatting it with one of Excel's Date number formats (Ctrl+1).
You must convert the DATE and DATEVALUE functions into their calculated date serial numbers in order to sort and filter them. To convert these functions individually, select a cell, press F2 to activate Edit mode, and then press F9 to replace the function with the calculated date serial number; finally, click the Enter button on the Formula bar to insert this serial number into the cell. To do this conversion on a range of cells, select the range, copy it to the Clipboard by pressing Ctrl+C, and then immediately paste the calculated serial numbers into the same range by choosing the Paste Values option from the Paste command button's drop-down menu (or press Alt+HVV).
DAY, WEEKDAY, MONTH, and YEAR
The DAY, WEEKDAY, MONTH, and YEAR Date functions on the Date & Time command button's drop-down menu all return just parts of the date serial number that you specify as their argument:
DAY(serial_number) to return the day of the month in the date (as a number between 1 and 31).
WEEKDAY(serial_number,[return_type]) to return the day of the week (as a number between 1 and 7 or 0 and 6). The optional return_type argument is a number between 1 and 3; 1 (or no return_type argument) specifies the first type where 1 equals Sunday and 7 equals Saturday; 2 specifies the second type where 1 equals Monday and 7 equals Sunday; and 3 specifies the third type where 0 equals Monday and 6 equals Sunday.
MONTH(serial_number) to return the number of the month in the date serial number (from 1 to 12).
YEAR(serial_number) to return the number of the year (as an integer between 1900 and 9999) in the date serial number.
For example, if you enter the following DAY function in a cell as follows:
DAY(DATE(16,4,15))
Excel returns the value 15 to that cell. If, instead, you use the WEEKDAY function as follows:
WEEKDAY(DATE(16,4,15))
Excel returns the value 7, which represents Saturday (using the first return_type where Sunday is 1 and Saturday is 7) because the optional return_type argument isn't specified. If you use the MONTH function on this date as in the following:
MONTH(DATE(16,4,15))
Excel returns 4 to the cell. If, however, you use the YEAR function on this date as in the following:
YEAR(DATE(16,4,15))
Excel returns 1916 to the cell (instead of 2016).
This means that if you want to enter a year in the 21st century as the year argument of the DATE function, you need to enter all four digits of the date, as in the following:
DATE(2016,4,15)
Note that you can use the YEAR function to calculate the difference in years between two dates. For example, if cell B6 contains 7/23/1978 and cell C6 contains 7/23/2016, you can enter the following formula using the YEAR function to determine the difference in years:
=YEAR(C6)-YEAR(B6)
Excel then returns 38 to the cell containing this formula.
Don't use these functions on dates entered as text entries. Always use the DATEVALUE function to convert these text dates and then use the DAY, WEEKDAY, MONTH, or YEAR functions on the serial numbers returned by the DATEVALUE function to ensure accurate results.
DAYS360
The DAYS360 function on the Date & Time command button's drop-down menu returns the number of days between two dates based on a 360-day year (that is, one in which there are 12 equal months of 30 days each). The DAYS360 function takes the following arguments:
DAYS360(start_date,end_date,[method])
The start_date and end_date arguments are date serial numbers or references to cells that contain such serial numbers. The optional method argument is either TRUE or FALSE, where FALSE specifies the use of the U.S. calculation method and TRUE specifies the use of the European calculation method:
U.S. (NASD) method (FALSE or method argument omitted): In this method, if the starting date is equal to the 31st of the month, it becomes equal to the 30th of the same month; if the ending date is the 31st of a month and the starting date is earlier than the 30th of the month, the ending date becomes the 1st of the next month; otherwise, the ending date becomes equal to the 30th of the same month.
European method (TRUE): In this method, starting and ending dates that occur on the 31st of a month become equal to the 30th of the same month.