Use Microsoft Excel’s date and time functions

JD Sartain shows how to go back and forth in time with retirement dates, expiration dates, and more

The Excel Date & Time functions we’re covering here – EDATE, YEARFRAC, EOMONTH, and NETWORKDAYS.INTL – are four of the many used for counting days. For each function listed below, we’ll define it first, then show the function’s arguments, which are the values that functions use to perform calculations. Then we’ll show a sample of the function’s syntax – how the formula is arranged, which includes the function’s name, parentheses, comma separators, and its arguments.

Note that Arguments are always surrounded by parentheses, and individual arguments are separated by commas.

EDATE()

EDATE is a practical function for returning a date some number of months in the future or past, using a positive value for future dates, a negative value for past dates. For instance, you can use this function to calculate a retirement date or expiration date, to calculate someone’s age from a birth date, or to add a given number of years to a specified date.

The arguments for this function are: start_date: specify a date to represent the start date (must be in a valid Excel serial number format). months: the number of months before or after the start_date. The syntax looks like this: =EDATE(start_date,months). 1. For this example, let’s calculate some retirement dates for our colleagues at work. Open a blank worksheet and enter the following field names as column titles in cells A3, B3, C3, D3, and E3: Name, Birthday, Retirement Date, Time Left, and In Years. 2. Enter some names and birthdays in columns A and B. 3. Click cell C4, then go to Formulas > Date & Time, and select EDATE from the list. You may also enter the EDATE() formula manually in cell C4: =EDATE(B4,12*62). If you don’t want to calculate and enter the specific number of months, it’s lots easier to multiply 12 times the retirement age (that is; 12 months per year times the age of 62 years). 4. Copy the formula from cell C4 to cells C5 through C13, and you get the date each individual can retire. See the next function YEARFRAC() to calculate the time left for each employee.

YEARFRAC()

This function calculates the number of days between two dates as a decimal number, which represents a fraction. This function is particularly useful because other Excel Date & Time functions return only a whole number. Use this formula to calculate retirement dates, to get one’s age from a birthday, to calculate years between dates, to ascertain the percent of the year that’s complete (so far), and so on. Note that Excel uses whole days between two dates to calculate the fraction of a year as a decimal value. The arguments for this function are: start_date: the start date. end_date: the end date. basis (optional): the type of day count basis (or code).

The syntax looks like this: =YEARFRAC(start_date,end_ date,basis).

Although Excel allows spaces between arguments, you still cannot use spaces between the function and the opening parentheses. Most old timers just remove all spaces from their formulas to avoid any chance of such errors.

Enter this formula in cell D4: =YEARFRAC(TODAY(),C4,1). The start_date in this formula is TODAY() – as opposed to an actual date. The TODAY() function returns today’s date. The date in cell C4 is the end_date, and the optional basis is 1, which means the actual days in the months and the actual days in the years.

This may seem confusing, but Excel provides five options for calculating the days and years in this formula. Some accountants in Europe and the US work on 30-day months and 360-day years; other systems are set up for actual days in a month, but only 360 days in a year; and some are defined as actual days in each month and year. The codes for each option are as follows: 0 = US NASD 30-day months/360-day years. 1 = Actual days in the months/Actual days in the years. 2 = Actual days in the months/360 days in the years. 3 = Actual days in the months/365 days in the years. 4 = European 30 days in the months/360 days in the years.

When the function is entered, if you press any key (such as the space bar) after the last argument (C4), Excel provides a pop-up menu that lists the five options above. Select the appropriate code from the list and press Enter.

If you do not enter a basis number, the default is 0, which means 30-day months and 360-day years – not a good choice if you’re looking for real-time results. The last column (E) in this spreadsheet shows how column D looks when it’s formatted as a fraction using quarters. For example, E4 is a hair less than two years, while E5 is a hair over one year. E6 is 1-1/4 years, E7 is 8-3/4 years, and so on.

EOMONTH()

Use this function to determine the date of the last day of the month (in future or past months). Because when creating spreadsheets with many dozens of calculations, poems and calendars cannot be included in a formula that’s needed to produce numeric results. This function returns a serial number, which represents a specific date in Excel. The arguments for this function are: start_date: a date that represents the start date in a valid Excel serial number format. months: the number of months before or after the start_date.

The syntax looks like this: =EOMONTH (start_date, months). For months, use a positive number for future dates and a negative number for past dates. 1. Enter 10 dates in cells A4 to A13. 2. Enter the following formula in cell B4: =EOMONTH(A4,1). A4 is the date in that cell, and the number ‘1’ means one month. This means show the last day of the month for one month past the date in cell A4. Notice that the program returns an Excel serial number. 3. Next, position your cursor on Cell B4 and press the function key F2 (Edit). Cursor over to the letter ‘A’ in the formula and press function key F4 (absolute reference) three times, or until the formula looks like this: =EOMONTH(£A4,1). 4. For those unfamiliar with this feature, the dollar sign in front of the letter ‘A’ means that column reference will not change when the formula is copied. Now, when we copy the formula in A4 down and over, the cell rows change but the column remains the same. 5. Next, format the serial number in cell B4 to a medium-long date; that is, Mon Feb 26, 2016. 6. Copy cell B4 down from B5 through B13. Note that the mediumlong number format also copies with the formula. 7. Next, copy the formula in B4 to C4, D4, and E4. Edit each formula to reflect the new month numbers: Change the ‘1’ in C4 to number 6. Change the ‘1’ in D4 to ‘12’ and the ‘1’ in E4 to number ‘18.’ Now copy C4 through E4 to C13 through E13. 8. Remember to widen the columns to accommodate the new formats, then notice how quickly you can find out this information using this valuable function.

NETWORKDAYS.INTL()

This function calculates the number of working days between two specified dates, excluding weekends. This is useful if you’re counting work days (or school days) in a quarter, semester, or year.

The difference between this function and others like it is the option to choose which days are counted as weekend days. Not everyone gets Saturdays and Sundays off. Some get Mondays and Tuesdays off, others Wednesdays and Fridays. With this function, you can set the weekend days to your individual schedule.

In addition, this function allows you to select and set the individual holidays in a specified timeframe. For example, in the fourth quarter of the calendar year there are two holidays in October, two in November, and two in December if you include Halloween and Christmas Eve. If not, then you can set the holidays to four in the fourth quarter instead of six.

These are the code numbers that correspond with the days of the week needed to custom-define your weekend days. Number Weekend days 1. 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

If you leave this parameter blank (or unidentified), it defaults to number 1.

Enter holidays as a range of cells where you have specified the actual holiday dates (for example, F4:F10) or as a list of serial numbers that represent the actual holiday dates. The arguments for this function are: start_date: the start date. end_date: the end date. weekend: setting for which days of the week should be considered weekends (optional parameter). holidays: a reference to dates that should be considered non-work days (optional parameter). The syntax looks like this: =NETWORKDAYS.INTL (start_date, end_date, [weekend], [holidays]). 1. Enter the following field/column headers over columns A,B, C, F, and G, respectively: Start Date, End Date, Number of Work Days. Finally, enter Holidays (centred and merged over F and G). 2. Enter some random dates in columns A and B. Be sure the End Date is not a date before the Start Date. 3. Enter some random holidays (names and dates) into columns F and G. 4. Position your cursor in cell C4. Go to Formulas > Date & Time, and select the NETWORKDAYS.INTL function. 5. In the Function Arguments dialog box, click inside the Start_Date field box, then click your cursor in cell A4. 6. Press the Tab key down to the field box End_Date and click your cursor in cell B4. 7. Press the Tab key down to the field box Weekend and enter one of the defined weekend codes (remember, 1 = Saturdays and Sundays). 8. Press the Tab key down to the field box Holidays, then select/ highlight cells G4 through G11. Click OK.

Before you copy this formula from cell C4 down to C5 through C11, use the function key F4 to make the Holiday cells absolute: =NETWORKDAYS.INTL(A4,B4,1,£G£4:£G£11), so the days in the Holidays range are always G4 through G11.