Given a date range, count # of workdays per pay period the range falls in

Question

I have a list of roughly 6500+ rows in Excel2007. Each row represents the time that a person was on disability leave with a start date and an end date. If there is no end date, then the end date is set to 12/31/9999. A sample of some values is below:

Workers Compensation    4/7/2009 12/31/9999
Workers Compensation    5/21/2009 12/31/9999
Short Term Disability   8/27/2009 10/7/2009
Short Term Disability   6/22/2009 7/15/2009
Short Term Disability   1/21/2009 10/4/2009
Short Term Disability   4/8/2009 6/14/2009
Short Term Disability   6/19/2009 10/5/2009
Short Term Disability   8/19/2009 12/31/9999
Short Term Disability   5/30/2009 6/18/2009
Short Term Disability   7/9/2009 7/20/2009

The first date is the date they went on leave; the second date is the date they returned from leave.

I am trying to break out each row so that I can get a count of the number of days per pay period that the person was on leave. We pay bi-weekly with the first pay of 2009 covering the period between 12/14/08 and 12/27/08 and incrementing bi-weekly thereafter (pay #2 covers 12/28/08 thru 1/10/09 etc).

So, as an example, I know that the first row of data covers pay periods 200909 thru the current pay period (we are currently on pay period 200924). I also know that periods 200910 thru 200924 should have 10 days as the calculated figure (since there are 10 workdays per pay), and that the count for pay period 200909 should be 2 (since there are 2 workdays in that payperiod that overlap with when the employee was on leave.

I'd like to use a formula to evaluate this, rather than writing code. Any ideas? Thanks in advance.

Answer 1

Here, I am making a couple of assumptions:

1. The type of leave is under column A. The start and end dates are under columns B and C.
2. The rows start at row 1.
3. You are only concerned about pay periods starting December 14, 2008.
4. You are not taking bank/public holidays into account (although taking them into account is not that big a deal).
5. The worker's leave includes the end date.

I used several cells to solve this problem:

• In cell D1: =IF(C1=DATE(9999,12,31),TODAY(),C1)
• In cell E1: =B1 - MOD(B1-DATE(2008,12,14),14) - 1
• In cell F1: =D1 - MOD(D1-DATE(2008,12,14),14) - 1
• In cell G1: =(F1-E1)/14 - 1
• In cell H1: =(NETWORKDAYS(E1,B1) + NETWORKDAYS(F1,D1) + (G1 - 2) * 10)/ G1

Cell D1 determines whether the leave is ongoing. Cell G1 counts the number of pay periods between the start and end pay period. Cell H1 contains the answer you seek.

You can copy these cells for the other rows. Also, I would consider substituting TODAY() for 12/31/9999 for column C, if that's feasible.

UPDATED

I noticed that if no end date is specified, the value for the column corresponding with the pay period today's date belongs to should be 10. Also, I didn't ensure that for the first pay period the leave falls under, I should return the number of days the person is on leave for that period. Taking the above into account, as well as the clarifications provided, I came up with the following solution.

I make the same assumptions as above except that assumption 2 is replaced with the assumption that the rows start at row 2.

I defined the following names:

• FirstPayPeriod: =DATE(2008, 12,14)
• PayPeriodLength: =14
• MaxDaysPerPeriod: =NETWORKDAYS(FirstPayPeriod, FirstPayPeriod + PayPeriodLength)
• NoEndDate: =DATE(9999, 12, 31)

I defined the following cells and copied down the desired number of rows:

• Cell D2: =IF(C2 = NoEndDate, FirstPayPeriod + PayPeriodLength * ROUNDUP((TODAY() - FirstPayPeriod)/PayPeriodLength, 0) - 1, C2)
• Cell E2: =NETWORKDAYS(B2,D2)

I then defined the following cells for the top row:

• Cell F1: =FirstPayPeriod
• Cell G1: =F1 + PayPeriodLength

I then copied cell G1 across columns for as many pay periods I would like to represent. Of course, cell F1 and each subsequent cell to the right specify the start date of the pay period that the column represents.

I finally defined the following cell and copied to as many columns to the right as there are pay periods (EXCEPT FOR THE LAST PAY PERIOD - SEE BELOW), as well as copied down to as many rows as there is data:

• Cell F2: =IF(OR(G$1 <= $B2, F$1 > $D2), 0, MIN($E2, NETWORKDAYS(F$1, G$1 - 1), NETWORKDAYS($B2, G$1), NETWORKDAYS(F$1, $D2)))

For the last pay period, I did the following. Assuming that column AD holds data for the last pay period:

• Cell AD2: =IF(AC$1 > $D2, 0, MIN($E2, NETWORKDAYS(AD$1, AD$1 + PayPeriodLength - 1), NETWORKDAYS(AC$1, $D2)))

I then copied down as many rows as there is data.

Answer 2

Enter the following formulas

G1:12/26/2008
G2:=G1+14

and fill down to row 28. Then

D1:=IF(C1=DATE(9999,12,31),TODAY(),C1)
E1:=NETWORKDAYS(B1,INDEX($G$1:$G$28,MATCH(B1+13,$G$1:$G$28)))
F1:=10-NETWORKDAYS(D1+1,INDEX($G$1:$G$28,MATCH(D1+14,$G$1:$G$28)))

and fill down as necessary. Then

H1:{=SUM(($B$1:$B$10<=G1-14)*($D$1:$D$10>G1))}
I1:{=SUM(($B$1:$B$10<G1)*($B$1:$B$10>=G1-14)*($E$1:$E$10))}
J1:{=SUM(($D$1:$D$10>G1-14)*($D$1:$D$10<=G1)*($F$1:$F$10))}
K1:=SUM(H1*10,I1,J1)

Note that formulas surrounded by brackets need to be array entered - do not enter the brackets, rather commit the formula with control+shift+enter instead of just enter.

Obviously you can combine a bunch of columns for brevity if you like.

Answer 3

Pivot table method. Enter these formula

G1:12/26/2008
G2:=G1+14

and fill down to row 28. Then

A14: Date
A15: 1/21/2009
A16: =A15+1
B14: Count
B15: {=SUM(($B$1:$B$10<=A15)*(WEEKDAY(A15)<7)*(WEEKDAY(A15)>1)*($D$1:$D$10>=A15))}
C14: Week
C15: =INDEX($G$1:$G$28,MATCH(A15+13,$G$1:$G$28))

Fill A, B, and C formulas down to row 321 to stop on 11/23/09 (or farther if you want).

Select A14:C321 and create a pivot table. Put 'Week' in row area and 'Count' in the data area.