Workday Function

Earlier this week I received a request for help from Nancy. She was having a problem with a date formula. She wanted to subtract 8 days from the current date.

Well that’s easy enough. =Today()-8  Since the day I wrote this post is Sept 25^th, the result is September 17^th (8 days prior).

A	B	C	D
Result	Formula	Current Date	9/25/2012
9/17/2012	=TODAY()-8

However she wanted to only count business days (Nancy did not want to include Saturday or Sunday).

So that changes the formula.

I needed to look only at Workdays. Fortunately Excel has just the function for this.

=WORKDAY(TODAY(),-8)

Since the day I wrote this post is Sept 25^th, the result is September 13^th (8 workdays prior).

	A	B	C	D
1	Result	Formula	Current Date	9/25/2012
2	9/17/2012	=TODAY()-8
3
4	9/13/2012	=WORKDAY(TODAY(),-8)

So what does the WORKDAY function do?

Well according to the Excel Help… Workday returns a number that represents a date that is the indicated number of working days before or after a date (the starting date). Working days exclude weekends and any dates identified as holidays. Use WORKDAY to exclude weekends or holidays when you calculate invoice due dates, expected delivery times, or the number of days of work performed.”

Now what is really handy here is the ability to identify holidays.

Syntax

WORKDAY(start_date,days,holidays)

Start_date     is a date that represents the start date.

Days     is the number of nonweekend and nonholiday days before or after start_date. A positive value for days yields a future date; a negative value yields a past date.

Holidays     is an optional list of one or more dates to exclude from the working calendar, such as state and federal holidays and floating holidays. The list can be either a range of cells that contain the dates or an array constant (array: Used to build single formulas that produce multiple results or that operate on a group of arguments that are arranged in rows and columns. An array range shares a common formula; an array constant is a group of constants used as an argument.) of the serial numbers that represent the dates.

Remarks from Microsoft…

Important   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 day of May, 2008. Problems can occur if dates are entered as text .

Microsoft Excel stores dates as sequential serial numbers so they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,448 days after January 1, 1900. Microsoft Excel for the Macintosh uses a different date system as its default.

If any argument is not a valid date, WORKDAY returns the #VALUE! error value.

If start_date plus days yields an invalid date, WORKDAY returns the #NUM! error value.

If days is not an integer, it is truncated.

Ways To Add In Excel

Today I want to address perhaps the most basic tasks in Excel… Adding cells.

To add numbers in two or more cells in Excel you need to create a formula.

The most basic way to add two or more cells is to use an addition formula. In this example I want to add the numbers in cells A2 B2 C2 D2 and E2. I start my formula with an equal sign. I then reference each cell and place the addition sign between each cell reference.

If I wanted to add cells A2 and B2, my formula would be =A2+B2. To add all 5 cells, I use the formula =A2+B2+C2+D2+E2.

	A	B	C	D	E	Addition Results	Formula
2	1	2	3	4	5	15	=A2+B2+C2+D2+E2

Now this is not the only way to add numbers. I can use cell references to help add clarity to my formula. Ask yourself what is easier to understand… =A2+B2 or =Sales + Tax? I can name individual cells or an entire range of cells. Then I can reference these names in my formula.

In this example I have selected cells A1 B1 C1 D1 and E1. I then typed RowsToAdd in the name box. This name now references this “Range of Cells” and I can use that name in formulas. =SUM(RowsToAdd).

Now in both of these examples I start my formula with the equal sign. However in the second formula I use the Sum function. The Sum function allows me to add all the cells within a range that I specify. In this example, =SUM(A1:E1) and =SUM(RowsToAdd) are the same. I am adding cells A1 B1 C1 D1 and E1. However I don’t need to list all the cells in between. The Sum function takes all the cells between my starting cell and ending cell. The Sum function is really handy when you want to add hundreds or thousands of cells to add.

I can also use the AutoSum feature to add cells. Simply highlight the cells that you wish to add together and click the AutoSum button on the Home table of the Ribbon (Excel 2007) under editing. If you don’t highlight the cells, Auto Sum will automatically select the closest range of data cells based on the following rules.

1.     The uninterrupted group of cells that has data above the active cell.

T   The uninterrupted group of cells that has data to the left of the active cell.
If there is no data in the cells above or to the left, Auto Sum will wait for you to select the cells to sum.

It’s always good idea to verify the cells selected whenever you use Auto Sum.

I can also quickly get the sum of cells by referencing the Status Bar. Select a range of cells then look at the sum in the bottom-right hand corner of the Excel window and the status bar will display the result.

AverageIf using cell reference instead of hard coded date

Over the weekend I received an email from Pam. She was experiencing difficulties using the AverageIf example from a previous posting of mine. Previous Post She wanted to use the average if formula but instead of hard coding a date in the criteria window, she wanted to use a cell reference instead.

In Pam’s example she was trying to use a cell reference in her criteria such as “=b2175” since she has a date in cell b2175. Unfortunately she was receiving a DIV error as Excel did not like the ways she coded her solution.

Let’s help her out today…

In the following example I want to look for dates greater than or equal to 2/19/2012 and perform my average if calculation. I can hard code my date (as in my original posting) in the formula by encompassing it inside of “”>= date “”.

=AVERAGEIF(C2:C10,">=2/19/2012",B2:B10)

However if I want Excel to reference a date in a different cell then I need to modify my formula slightly. The concept is the same but I first place quotes around my test “>=” greater than or equal to. I then add an & and then the cell reference.  I end up with ,">="&C7

Now the formula looks for dates greater than the date in cell C7 (2/19/2012)

In cell E2 I have the formula=AVERAGEIF(C2:C10,">="&C7,B2:B10). This references the date in cell C7. Notice that I need to place the “>=” and join that with the &.

	A	B	C	D	E	F
1	Item	Cost	Sale Date	Number Of Rows That Qualify	Average If	Formula and calculations
2	A	1.00	1/15/2012		7.50	=AVERAGEIF(C2:C10,">=2/19/2012",B2:B10)
3	B	2.00	1/22/2012
4	C	3.00	1/29/2012		7.50	=AVERAGEIF(C2:C10,">="&C7,B2:B10)
5	D	4.00	2/5/2012
6	E	5.00	2/12/2012
7	F	6.00	2/19/2012	1
8	G	7.00	2/26/2012	1
9	H	8.00	3/4/2012	1
10	I	9.00	3/11/2012	1
11		35.00		4

The result is the same as if you hard coded the date into the criteria.

Hope this helps you Pam.