Date Function
Using Dates in Calculations
Excel converts dates to serial numbers between 1 and 2,958,525 corresponding to the 10-millenium period from January 1,1900 through December 31, 9999. The date January 1, 1990 is assigned the serial number 1 whereas December 31, 9999 is assigned the serial number 2,958,525.
Converting dates to numbers allows use in calculations. To find the number of days between two dates subtract one from the other. For example, to know how many days a checkbook register has been in use from today’s date, enter the TODAY function in one cell and subtract the other date by using the cell reference as shown below in the formula bar =A2-C2. The cell is formatted as a date but by right-clicking in the cell and choosing Format Cells, selecting the Number tab and choosing the General category, the result changes to the number of days between the two dates.
Also, you can use the DATE function in a calculation. The DATE function returns the serial number of a particular date and is most useful in formulas where year, month, and day are formulas, not constants. Microsoft Help warns, "Dates should be entered by using the DATE function, or as results of other formulas or functions. For example, use DATE(2008,5,23) for the 23rd of May, 2008. Problems can occur if dates are entered as text."
The syntax of the DATE function is =DATE(year, month, day). It returns the serial number of the date entered as the argument and formats the cell with a date format. Below, the formula in B3 is =DATE(2006,7,4)-A3 as you can see in the formula bar. Cell A3 uses the =TODAY() function to return the current date as a serial number whose value is updated to the current date each time the worksheet is opened. The initial format is date, but by right clicking on the cell, choosing “Format Cells” on the shortcut menu, and selecting the Number tab, you can change the category to “Number” so that the value in the cell is no longer formatted as a date but changes to the number of days between the two dates. To view a date as its underlying serial number, choose the General category.
Practice: Using the Date Function
- Download days.xls workbook. Open the file to create a worksheet that automatically displays how many days until certain special dates. You will use the DATE function to calculate the number of days between two dates.
- In cell A3, type the =TODAY() function.
- Tab over to cell B3 and click the Insert Function button
. - When the Insert Function dialog box opens, type DATE in the “Search for a function” box and hit the Go button.
- Choose DATE in the “Select a function” list and click OK, which brings up the following function argument box.
- Enter the four-digit year and then tab to Month and enter 7. Tab to Day, enter 4, and click OK.
- Then click in the formula box and subtract cell A3 from the date. Click the green arrow beside the formula bar to enter the function.
- The format appears as a date so right-click on the cell and choose “Format cells.”
- Select the Number tab and under “Category” choose Number or General.
- Tab over to cell C3 and instead of using the Insert Function button enter =date(year,month,day), putting in the correct year month and day for each of the dates indicated in order to calculate the number of days until that date. You could also use cell references with the DATE function such as =date(a2,b2,c2) (if you had dates or formulas for dates entered in these cells).
- Complete the rest of the worksheet. Save your work and then go to the next section on Grouping Worksheets.
Click here to return to Course Topics.