Excel® 2016 Formulas and Functions (2016)

---

Calculating a Person’s Age, Part II

The DATEDIF() function can greatly simplify the formula for calculating a person’s age. (See “Calculating a Person’s Age, Part I,” earlier in this chapter.) If the person’s date of birth is in a cell named Birthdate, the following formula calculates his current age:

=DATEDIF(Birthdate, TODAY(), "y")

NETWORKDAYS(): Calculating the Number of Workdays Between Two Dates

If you calculate the difference in days between two days, Excel includes weekends and holidays. In many business situations, you need to know the number of workdays between two dates. For example, when calculating the number of days an invoice is past due, it’s often best to exclude weekends and holidays.

This is easily done using the NETWORKDAYS() function (read the name as net workdays), which returns the number of working days between two dates:

NETWORKDAYS(start_date, end_date[, holidays])

For example, here’s an expression that returns the number of workdays between December 1, 2016, and January 10, 2017, excluding December 25, 2016, and January 1, 2017:

=NETWORKDAYS("12/1/2016", "1/10/2017", {"12/25/2016","1/1/2017"})

Figure 10.4 shows an update to the accounts receivable worksheet that uses NETWORKDAYS() to calculate the number of workdays that each invoice is past due.

Figure 10.4 This worksheet calculates the number of workdays that each invoice is past due by using the NETWORKDAYS() function.

DAYS360(): Calculating Date Differences Using a 360-Day Year

Many accounting systems operate using the principle of a 360-day year, which divides the year into 12 periods of uniform (30-day) lengths. Finding the number of days between dates in such a system isn’t possible with the standard addition and subtraction of dates. However, Excel makes such calculations easy with its DAYS360() function, which returns the number of days between a starting date and an ending date, based on a 360-day year:

DAYS360(start_date, end_date[, method])

For example, the following expression returns the value 1:

DAYS360("3/30/2016", "4/1/2016")

YEARFRAC(): Returning the Fraction of a Year Between Two Dates

Business worksheet models often need to know the fraction of a year that has elapsed between one date and another. For example, if an employee leaves after 3 months, you might need to pay out a quarter of a year’s worth of benefits. This calculation can be complicated by the fact that your company might use a 360-day accounting year. However, the YEARFRAC() function can help you. This function converts the number of days between a start date and an end date into a fraction of a year:

YEARFRAC(start_date, end_date[, basis])

For example, the following expression returns the value 0.25:

YEARFRAC("3/15/2016", "6/15/2016")

Using Excel’s Time Functions

Working with time values in Excel is not greatly different from working with date values, although there are some exceptions, as you’ll see in this section. Here you’ll work mostly with Excel’s time functions, which work with or return time serial numbers. All of Excel’s time-related functions are listed in Table 10.5. (For the serial_number arguments, you can use any valid Excel time.)

Table 10.5 Excel’s Time FunctionsFunction Description

Returning a Time

If you need a time value to use in an expression or a function, either you can enter it by hand if you have a specific date that you want to work with, or you can take advantage of the flexibility of three Excel functions: NOW(), TIME(), and TIMEVALUE().

NOW(): Returning the Current Time

When you need to use the current time in a formula, a function, or an expression, use the NOW() function, which doesn’t take any arguments:

NOW()

This function returns the serial number of the current time, with the current date as the assumed date. For example, if it’s noon and today’s date is December 31, 2016, the NOW() function returns the following serial number (although in the cell you see this displayed using the m/d/yyyy h:mm format):

42735.5

If you just want the time component of the serial number, subtract TODAY() from NOW():

NOW() - TODAY()

Just like the TODAY() function, remember that NOW() is a dynamic function that doesn’t keep its initial value (that is, the time at which you entered the function). Each time you edit the formula, enter another formula, recalculate the worksheet, or reopen the workbook, NOW() uptimes its value to return the current system time.

TIME(): Returning Any Time

A time consists of three components: the hour, minute, and second. It often happens that a worksheet generates one or more of these components, and you need some way of building a proper time out of them. You can do that by using Excel’s TIME() function:

TIME(hour, minute, second)

For example, the following expression returns the time 2:45:30 p.m.:

TIME(14, 45, 30)

Like the DATE() function, TIME() adjusts for wrong hour, month, and second values. For example, the following expression returns 3:00:30 p.m.:

TIME(14, 60, 30)

Here, TIME() takes the extra minute and adds 1 to the hour value.

TIMEVALUE(): Converting a String to a Time

If you have a time value in string form, you can convert it to a time serial number by using the TIMEVALUE() function:

TIMEVALUE(time_text)

For example, the following expression returns the time serial number for the string 2:45:00 PM:

TIMEVALUE("2:45:00 PM")

Returning Parts of a Time

The three components of a time—hour, minute, and second—can also be extracted individually from a given time, using Excel’s HOUR(), MINUTE(), and SECOND() functions.

The HOUR() Function

The HOUR() function returns a number between 0 and 23 that corresponds to the hour component of a specified time:

HOUR(serial_number)

For example, the following expression returns 12:

HOUR(0.5)

The MINUTE() Function

The MINUTE() function returns a number between 0 and 59 that corresponds to the minute component of a specified time:

MINUTE(serial_number)

For example, if it’s currently 3:15 p.m., the following expression returns 15:

MINUTE(NOW())

The SECOND() Function

The SECOND() function returns a number between 0 and 59 that corresponds to the second component of a specified time:

SECOND(serial_number)

For example, the following expression returns 30:

SECOND("2:45:30 PM")

Returning a Time X Hours, Minutes, or Seconds from Now

As I mentioned earlier, TIME() automatically adjusts wrong hour, minute, and second values. You can take advantage of this by applying formulas to one or more of the TIME() function’s arguments. The most common use for this is to return a time that occurs X number of hours, minutes, or seconds from now (or from any time).

For example, the following expression returns the time 12 hours from now:

TIME(HOUR(NOW()) + 12, MINUTE(NOW()), SECOND(NOW()))

Unlike the DATE() function, the TIME() function doesn’t enable you to simply add an hour, a minute, or a second to a specified time. For example, consider the following expression:

NOW() + 1

All this does is add 1 day to the current date and time.

If you want to add hours, minutes, and seconds to a time, you need to express the added time as a fraction of a day. For example, because there are 24 hours in a day, 1 hour is represented by the expression 1/24. Similarly, because there are 60 minutes in an hour, 1 minute is represented by the expression 1/24/60. Finally, because there are 60 seconds in a minute, 1 second is represented by the expression 1/24/60/60. Table 10.6 shows you how to use these expressions to add n hours, minutes, and seconds.

Table 10.6 Adding Hours, Minutes, and Seconds

Summing Time Values

When working with time values in Excel, you need to be aware that there are two subtly different interpretations for the phrase “adding one time to another”:

Adding time values to get a future time—As you saw in the previous section, adding hours, minutes, or seconds to a time returns a value that represents a future time. For example, if the current time is 11:00 p.m. (23:00), adding 2 hours returns the time 1:00 a.m.

Adding time values to get a total time—In this interpretation, time values are summed to get a total number of hours, minutes, and seconds. This is useful if you want to know how many hours an employee worked in a week or how many hours to bill a client. In this case, for example, if the current total is 23 hours, adding 2 hours brings the total to 25 hours.

The problem is that adding time values to get a future time is Excel’s default interpretation for added time values. So, if cell A1 contains 23:00 and cell A2 contains 2:00, the following formula returns 1:00:00 AM:

=A1 + A2

The time value 25:00:00 is stored internally, but Excel adjusts the display so that you see the “correct” value 1:00:00 AM. If you want to see 25:00:00 instead, apply the following custom format to the cell:

[h]:mm:ss

Calculating the Difference Between Two Times

Excel treats time serial numbers as decimal expansions (numbers between 0 and 1) that represent fractions of a day. Because they’re just numbers, there’s nothing to stop you from subtracting one from another to determine the difference between them:

EndTime - StartTime

This expression works just fine, as long as EndTime is greater than StartTime. (I used the names EndTime and StartTime purposefully so you’d remember to always subtract the later time from the earlier time.)

However, there’s one scenario in which this expression fails: If EndTime occurs after midnight the next day, there’s a good chance that it will be less than StartTime. For example, if a person works from 11:00 p.m. to 7:00 a.m., the expression 7:00 AM - 11:00 PM results in an illegal negative time value. (Excel displays the result as a series of # symbols that fill the cell.)

To ensure that you get the correct positive result in this situation, use the following generic expression:

IF(EndTime < StartTime, 1 + EndTime - StartTime, EndTime - StartTime)

The IF() function checks to see whether EndTime is less than StartTime. If it is, 1 is added to the value EndTime - StartTime to get the correct result; otherwise, just EndTime - StartTime is returned.

Case Study: Building an Employee Time Sheet

In this case study, you’ll put your new knowledge of time functions and calculations to good use building a time sheet that tracks the number of hours an employee works each week, takes into account hours worked on weekends and holidays, and calculates the total number of hours and the weekly pay. Figure 10.5 shows the completed time sheet.

Figure 10.5 This employee time sheet tracks the daily hours, takes weekends and holidays into account, and calculates the employee’s total working hours and pay.

Before starting, you need to understand three terms used in this case study:

Regular hours—These are hours worked for regular pay.

Overtime hours—These are hours worked beyond the maximum number of regular hours, as well as any hours worked on the weekend.

Holiday hours—These are hours worked on a statutory holiday.

Entering the Time Sheet Data

Let’s begin at the top of the time sheet, where the following data is required:

Employee Name—You’ll create a separate sheet for each employee, so enter the person’s name here. You might also want to augment this with the date the person started or other data about the employee.

Maximum Hours Before Overtime—This is the number of regular hours an employee has to work in a week before overtime hours take effect. Enter the number using the hh:mm format. Cell D3 uses the [h]:mm custom format, to ensure that Excel displays the actual value.

Hourly Wage—This is the amount the employee earns per regular hour of work.

Overtime Pay Rate—This is the factor by which the employee’s hourly rate is increased for overtime hours. For example, enter 1.5 if the employee earns time and a half for overtime.

Holiday Pay Rate—This is the factor by which the employee’s hourly rate is increased for holiday hours. For example, enter 2 if the employee earns double time for holidays.

Calculating the Daily Hours Worked

Figure 10.6 shows the portion of the time sheet used to record the employee’s daily hours worked. For each day, you enter five items:

Date—Enter the date the employee worked. This is formatted to show the day of the week, which is useful for confirming overtime hours worked on weekends.

Work Start Time—Enter the time of day the employee began working.

Lunch Start Time—Enter the time of day the employee stopped for lunch.

Lunch End Time—Enter the time of day the employee resumed working after lunch.

Work End Time—Enter the time of day the employee stopped working.

Figure 10.6 The section of the employee time sheet in which you enter the hours worked and in which the total daily hours are calculated.

The first calculation occurs in Total Hours Worked (column F). The idea here is to sum the number of hours the employee worked in a given day. The first part of the calculation uses the time-difference formula from the previous section to derive the number of hours between the Work Start Time (column B) and the Work End Time (column E). Here’s the expression for the first entry (row 9):

IF(E9 < B9, 1 + E9 - B9, E9 - B9)

However, you also have to subtract the time the employee took for lunch, which is the difference between Lunch Start Time (column C) and Lunch End Time (column D). Here’s the expression for the first entry (row 9):

IF(D9 < C9, 1 + D9 - C9, D9 - C9)

Let’s skip over to the Weekend Hours calculation (column H). The idea behind this column is that if the employee worked on the weekend, all of the hours worked should be booked as overtime hours. So, the formula checks to see whether the date is a Saturday or Sunday:

=IF(OR(WEEKDAY(A9) = 7, WEEKDAY(A9) = 1), F9, 0)

If the OR() function returns TRUE, the date is on the weekend, so the value from the Total Hours Worked column (F9, in the example) is entered into the Weekend Hours column; otherwise, 0 is returned.

Next up is the Holiday Hours calculation (column I). Here, you want to see if the date is a statutory holiday. If it is, all of the hours worked that day should be booked as holiday hours. To that end, the formula checks to see if the date is part of the range of holiday dates calculated earlier in this chapter:

{=SUM(IF(A9 = Holidays!F4:F13, 1, 0)) * F9}

This is an array formula that compares the date with the dates in the holiday range (Holidays!F4:F13). If a match occurs, the SUM() function returns 1; otherwise, it returns 0. This result is multiplied by the value in the Total Hours Worked column (F9, in the example). So, if the date is a holiday, the hours for that day are entered as holiday hours.

Finally, the value in the Non-Weekend, Non-Holiday Hours column (G) is calculated by subtracting Weekend Hours and Holiday Hours from Total Hours Worked:

=F9 - H9 - I9

Calculating the Weekly Hours Worked

Next up is the Total Weekly Hours section (refer to Figure 10.5), which adds the various types of hours the employee worked during the week.

The Total Hours value is a straight sum of the values in the Total Hours Worked column (F):

=SUM(F9:F15)

To derive the Weekly Regular Hours value, the calculation has to check to see if the total in the Non-Weekend, Non-Holiday Hours column (G) exceeds the number in the Maximum Hours Before Overtime cell (D3):

=IF(SUM(G9:G15) > D3, D3, SUM(G9:G15))

If this is true, the value in D3 is entered as the Regular Hours value; otherwise, the sum is entered.

Calculating the Weekly Overtime Hours value is a two-step process: First, you have to check to see if the sum in the Non-Weekend, Non-Holiday Hours column (G) exceeds the number in the Maximum Hours Before Overtime cell (D3). If so, the number of overtime hours is the difference between them; otherwise, it’s 0:

IF(SUM(G9:G15) > D3, SUM(G9:G15) - D3, "0:00")

Second, you need to add the sum of the Overtime Hours column (H):

=IF(SUM(G9:G15) > D3, SUM(G9:G15) - D3, "0:00") + SUM(H9:H15)

Finally, the Weekly Holiday Hours value is a straight sum of the values in the Holiday Hours column (I):

=SUM(I9:I15)

Calculating the Weekly Pay

The final section of the time sheet is the Weekly Pay calculation. The dollar amounts for Regular Pay, Overtime Pay, and Holiday Pay are calculated as follows:

Regular Pay = Weekly Regular Hours * Hourly Wage * 24
Overtime Pay = Weekly Overtime Hours * Hourly Wage * Overtime Pay Rate * 24
Holiday Pay = Weekly Holiday Hours * Hourly Wage * Holiday Pay Rate * 24

Note that you need to multiply by 24 to convert the time value to a real number. Finally, the Total Pay is the sum of these values.

From Here

For more information on formatting dates and times, see “Formatting Numbers, Dates, and Times,” p. 74.

For a general discuss of function syntax, see “The Structure of a Function,” p. 130.

To learn how to convert nonstandard date strings to dates, see “A Date-Conversion Formula,” p. 154.

To learn how to use CHOOSE() to convert the WEEKDAY() return value into a day name, see “Determining the Name of the Day of the Week,” p. 194.