Dates and date functions

To illustrate the most commonly used month-day-year formats in Microsoft Excel 2016, let’s suppose today is January 4, 2004. You can enter this date as any of the following:

• 1/4/2004

• 4-Jan-2004

• January 4, 2004

• 1/4/04

If you use only two digits to represent a year, and the digits are 30 or higher, Excel assumes the digits represent years in the twentieth century; if the digits are lower than 30, Excel assumes they represent years in the twenty-first century. For example, 1/1/29 is treated as January 1, 2029, but 1/1/30 is treated as January 1, 1930. Each year, the year treated as dates in the twenty-first century increases by one.

Answers to this chapter’s questions

When I enter dates into Excel, I often see a number such as 37625 rather than a date such as 1/4/2003. What does this number mean, and how do I change it to a normal date?

The way Excel treats calendar dates is sometimes confusing to the novice. The key is understanding that Excel can display a date in a variety of month-day-year formats, or it can display a date in serial format. A date in serial format, such as 37625, is simply a positive integer that represents the number of days between the given date and January 1, 1900. Both the current date and January 1, 1900 are included in the count. For example, Excel displays January 3, 1900, in serial format as the number 3, which means there are three days between January 1, 1900, and January 3, 1900 (including both days).

Figure 7-1 shows the worksheet named Serial Format in the file Dates.xlsx. Suppose you are given the dates shown in cells D5:D14 in serial format. For example, the value 37622 in cell D5 indicates a date that is 37,622 days after January 1, 1900 (including both January 1, 1900, and day 37,622). To display these serial dates in the month-day-year format, copy them to E5:E14. Select the cell range E5:E14, right-click the selection, and click Format Cells. (At any time, by the way, you can bring up the Format Cells dialog box by pressing Ctrl+1.) Now select the date format you want from the list shown in Figure 7-2. The dates in E5:E14 will be displayed in date format, as you can see in Figure 7-1. If you want to format dates in the serial number format, select E5:E14, right-click the selection, and choose Format Cells, General.

Simply changing the date format of a cell to General will yield the date in serial format. Another way to obtain the date in serial format is to use the DATEVALUE function and enclose the date in quotation marks. For example, in the Date Format worksheet of the file Dates.xlsx, cell I5 contains the formula DATEVALUE(“1/4/2003”). Excel yields 37625, which is the serial format for January 4, 2003.

Can I use a formula to automatically display today’s date?

Displaying today’s date with a formula is easy, as you can see by looking at cell C13 of the Date Format worksheet from the file Dates.xlsx, as shown in Figure 7-3. Entering the TODAY() function in a cell will display today’s date. I created this screenshot on June 15, 2016.

How do I determine a date that is 50 workdays after another date? What if I want to exclude holidays?

The function WORKDAY(start_date,#days,[holidays]) displays the date that is the number of workdays indicated by #days after a given start date. (A workday is a nonweekend day.) Holidays is an optional argument for the function that allows you to exclude from the calculation any dates that are listed in a cell range. Thus, entering the formula WORKDAY(C14,50) in cell D14 of the Date Format worksheet tells us that 3/14/2003 is 50 workdays after 01/03/2003. If we believe that the only two holidays that matter are Martin Luther King Day and Independence Day, we can change the formula to WORKDAY(C14,50,F17:F18), which is in cell E14. With this addition, Excel does not count 01/20/2003 in its calculations, making 03/17/2003 the fiftieth workday after 01/03/2003. Note that instead of referring to the holidays in other cells, you can enter them directly in the WORKDAY formula with the serial number of each holiday enclosed in { }. For example, WORKDAY(38500,10,{38600,38680,38711}) would find the tenth workday after the date with serial number 38500, ignoring Labor Day, Thanksgiving, and Christmas of 2005.

The function WORKDAY.INTL was introduced in Excel 2010. This function allows you to choose your own definition of a workday. The syntax is WORKDAY.INTL(start_date,days,weekend, [holidays]). The third argument lets you specify the definition of a day off. You can use the following codes to specify the definition of a day off:

1 or omitted	Saturday, Sunday
2	Sunday, Monday
3	Monday, Tuesday
4	Tuesday, Wednesday
5	Wednesday, Thursday
6	Thursday, Friday
7	Friday, Saturday
11	Sunday only
12	Monday only
13	Tuesday only
14	Wednesday only
15	Thursday only
16	Friday only
17	Saturday only

For example, in sheet Date Format of the file Date.xlsx, I computed in cell C27 (see Figure 7-4) the date 100 workdays after 3/14/2011, with Sunday and Monday as days off, with the formula WORKDAY.INTL(C23,100,2). In cell C28, I computed the date 100 workdays after 3/14/2011 when only Sunday is a day off by using the formula WORKDAY.INTL(C23,100,11). You can also enter the definition of days off with a string of seven ones and zeroes, where a one indicates a day off and the first entry in the string is Monday, the second is Tuesday, and so on. Thus, in cells D26 and D27 I duplicated our previous results with the formulas WOR KDAY.INTL(C23,100,”1000001”) and WORKDAY.INTL(C23,100,”0000001”), respectively.

How do I determine the number of workdays between two dates?

The key to solving this problem is to use the NETWORKDAYS function. The syntax for this function is NETWORKDAYS(start_date,end_date,[holidays]), where holidays is an optional argument identifying a cell range that lists the dates you want to count as holidays. The NETWORKDAYS function returns the number of working days between start_date and end_date, excluding weekends and any listed holidays. As an illustration of the NETWORKDAYS function, look at cell C18 in the Date Format worksheet of the file Dates.xlsx, which contains the formula NETWORKDAYS(C14,C15). This formula yields the number of working days between 1/3/2003 and 8/4/2003, which is 152. The formula NETWORKDAYS(C14,C15,F17:F18) in cell C17 yields the number of workdays between 1/3/2003 and 8/4/2003, excluding Martin Luther King Day and Independence Day. The answer is 152–2=150.

The function NETWORKDAYS.INTL was introduced in Excel 2010. Like the WORKDAYS.INTL function, NETWORKDAYS.INTL allows you to customize the definition of a weekend. For example, in cell C31 of the worksheet Date Format of the file Dates.xlsx, I computed the number of workdays (373) between 3/14/2011 and 8/16/2012, when Sunday and Monday are days off, with the formula NETWORKDAYS.INTL(C23,C24,2). (See Figure 7-4.) In cell D31, I computed the same result with the formula NETWORKDAYS.INTL(C23,C24,”1000001”).

I have 500 different dates entered in an Excel worksheet. How do I write formulas that will extract from each date the month, year, day of the month, and day of the week?

The Date Format worksheet of the file Dates.xlsx (see Figure 7-3) lists several dates in the cell range B5:B10. In B5 and B7:B9, I used four different formats to display January 4, 2003. In columns D:G, I extracted the year, month, day of the month, and day of the week for each date. By copying from D5 to D6:D10 the formula YEAR(B5), I extracted the year for each date. By copying from E5 to E6:E10 the formula MONTH(B5), I extracted the month (1=January, 2=February, and so on) portion of each date. By copying from F5 to F6:F10 the formula DAY(B5), I extracted the day of the month for each date. Finally, by copying from G5 to G6:G10 the formula WEEKDAY(B5,1), I extracted the day of the week for each date.

When the last argument of the WEEKDAY function is 1, then 1=Sunday, 2=Monday, and so on. When the last argument is 2, then 1=Monday, 2=Tuesday, and so on. When the last argument is 3, then 0=Monday, 1=Tuesday, and so on.

I am given the year, month, and day of the month for a date. Is there an easy way to recover the actual date?

The DATE function, whose arguments are DATE(year,month,day), returns the date with the given year, month, and day of the month. In the Date Format worksheet, copying from cell H5 to cells H6:H10 the formula DATE(D5,E5,F5) recovers the dates we started with.

My business has purchased and sold machines. For some, I have the date the machine was purchased and the date the machine was sold. Can I easily determine how many months we kept these machines?

The DATEDIF function can easily determine the number of complete years, months, or days between two dates. In file Datedif.xlsx (see Figure 7-5), you can see that a machine was bought on 10/15/2016 and was sold on 4/10/2018. How many complete years, months, or days was the machine kept? The syntax of the DATEDIF function is DATEDIF(startdate,enddate,time unit). If the unit is written as “y”, you get the number of complete years between the start and end dates; if the unit is written as “m”, you get the number of complete months between the start and end dates; and if the unit is written as “d”, you get the number of complete days between the start and end dates. Thus, entering DATEDIF(D4,D5,”y”) in cell D6 shows that the machine was kept for one full year. Entering the formula DATEDIF(D4,D5,”m”) in cell D7 shows the machine was kept for 17 complete months. Entering the formula DATEDIF(D4,D5,”d”) in cell D7 shows the machine was kept for 543 complete days. By the way, the DATEDIF function is not listed in the function wizard.

How can I place a static (unchanging) date in a worksheet?

Entering =TODAY() in a cell always returns today’s date. If we create a workbook, we might want the date we create it to always appear in the workbook. The keystroke combination Ctrl+; (the semicolon) will place today’s date in a cell, and unlike with the =TODAY() function, the date will never change. The workbook shown in Figure 7-6 (see Staticdate.xls) was created June 16, 2016, and that date will always show up in cell F6.