Software

Track business hours with Excel's WORKDAY function

Excel's DATE function is critical for performing calculations with dates, but it doesn't take into account weekends or holidays. To focus just on business hours, use the WORKDAY function instead. Here's how.

As an IT support professional, there are many situations that might require you to accurately track time. Perhaps your manager has asked you to estimate the cost of a software rollout. While Excel’s DATE function makes it easy to find the difference between two dates, problems can arise if you only want to calculate workdays. Fortunately, Excel offers a function to do the job. Excel’s WORKDAY function returns the number of workdays after a start date, and even considers holidays. Here’s how to prepare the spreadsheet:

Download this handy spreadsheet
Click here to download a fully functioning copy of the Excel spreadsheet Gregory Harris illustrates in this article. You will need a file unzip utility such as PKZIP or WinZip to extract the download file. You will also need Microsoft Excel 2000 to view the spreadsheet.

Starting out and adding on
Here's the situation. Our manager has just asked us to calculate the most cost-effective way to roll out a new software package by a specific deadline. We've decided to create a spreadsheet that calculates the per-hour cost of technicians with or without overtime and the impact of adding more workers to the job.

However, it soon becomes obvious that Excel's DATE function isn’t quite up to snuff for this task. While we can quickly convert hours worked into days and then add the elapsed time to the starting date, the result doesn't take weekends into account. Enter the WORKDAY function.

First, let’s set up the simpler elements of our spreadsheet. We want to track “Project A,” the rollout of a new software package, for our department. Let’s start by entering some basic data.

We’re beginning the project on Dec. 1, 2001, and we need to have it completed by the end of the year. The project will take a minimum of 160 hours to complete. Our assets include at least one technician, who makes a standard wage of $50 an hour and $75 per hour of overtime. We also have to pay our consulting firm a surcharge of $200 for each extra technician we use. (That won’t have any effect on today's example, but it’ll change the numbers when it comes time to mull different scenarios in my upcoming article on Excel's Scenario feature.) When we’d entered this information, our spreadsheet looked like the one in Figure A.

Figure A
Begin with some basic information.


Next comes a vital preparatory step. To use the WORKDAY function, we have to activate Excel’s Analysis ToolPak. To do so, choose Tools | Add-ins. In the resulting dialog box, select the Analysis ToolPak option, as shown in Figure B. (If the component isn’t installed, Excel might ask you to provide the Office CD-ROM.) Without this add-in activated, you can still enter the function, but you’ll get the #VALUE! or #NAME! error message.

Figure B
Activate Excel’s Analysis ToolPak to access the WORKDAY function.


More bookkeeping
We’re almost ready to introduce the WORKDAY function, but first, let’s enter the simpler formulas. In cell D14, add the formula
=D8*D9*D12

to calculate the regular wage cost; cell D15 contains the similar formula
=D8*D10*D13

to handle overtime. Cell D16 will total the regular and overtime hours using the formula
=D8*(D9+D10)

Rather than apply a lot of complicated IF logic, let’s just use some conditional formatting to warn us if the total hours are different from our project duration. Select cell D16 and choose Conditional Formatting from the Format menu. In the resulting dialog box, select Not Equal To from the second drop-down list and type 160 in the third text box, as shown in Figure C.

Figure C
Conditional Formatting will warn you if the hours worked don't match.


After applying the condition, click the Format button and apply any conspicuous formatting. We chose a red text color, but you can change the cell shading, apply a border, or establish any combination of settings. When you click OK, you should see no change because you currently have a result of 160 total hours in cell D16.

To determine the total labor cost, enter the formula
=(D8*(D9*D12))+(D8*(D10*D13))+(D11*(D8-1))

into cell D6. This formula multiplies the number of workers by regular and overtime hours and adds the $200 surcharge for each additional worker. Given the numbers already in the spreadsheet, Excel should provide the expected figure of $8,000. So far, so good!

The function
Now it’s time for the WORKDAY function’s debut. Its syntax is
WORKDAY(start_date,days,holidays)

where start_date is the date you want to use as a basis for calculation, days is the number of elapsed days, and holidays are any days you want to exclude from the count.
  • A negative figure for days provides the number of days prior to the start date.
  • You can enter days either as Excel serial date values or in the "MM/DD/YY" format, including quotes.
  • Any of the function’s arguments can be a cell reference or calculation.

So let’s see how we’d use this formula to estimate the project’s completion date. In cell D5, we enter
=WORKDAY("12/01/01",(D9/8),{"12/24/01",37250})

Doing so establishes our start date of Dec. 1, divides the total hours by eight to provide the number of elapsed days, and recognizes holidays on the 24th and 25th. Notice that we entered the holidays in array format but combined literal and serial date formats. When we hit [Enter], the function returns a value of 01/01/02, as you can see in Figure D.

Figure D
The WORKDAY function takes both working days and holidays into account.


Uh oh! Given the holidays, there’s no way one person working regular hours can complete the task by the 31st.

Stay tuned
Now that we know that our current situation is unworkable, it’s time to look for alternatives, and that’s where the Scenario feature comes in. In my next article, I’ll use the Scenario feature with this spreadsheet to determine whether to hire more consultants, use overtime, or both, without having to remember what we typed into various cells.

Share your Excel tips and tricks
Are you an Excel expert? Do you have a handy trick for solving a tough Excel problem? Post a comment to this article to share your tip with other TechRepublic members.

 
0 comments

Editor's Picks