Test, Test, and Retest (then add 1 for good measure)

We’ll be using 4 different sets of dates in order to be able to test for the full range of possibilities with our formula:

Test #1: “Same Day”
Start Date: 5/12/2009
End Date: 5/12/2009

Test #2: “No Weekend”
Start Date: 5/18/2009
End Date: 5/22/2009

Test #3: “With Weekend”
Start Date: 5/22/2009
End Date: 5/26/2009

Test #4: “Multiple Weekends”
Start Date: 5/11/2009
End Date: 5/25/2009

We’ll plug in each of the test dates above, and run through them one by one.

Test #1: “Same Day”
Start Date: 5/12/2009
End Date: 5/12/2009

Functions:

WEEKDAY([Start Date],2) = WEEKDAY(“5/12/2009”,2) = Tuesday = 2
WEEKDAY([End Date],2) = WEEKDAY(“5/12/2009”,2) = Tuesday = 2
DATEDIF(“5/12/2009”, “5/12/2009”,"D") = 0

Our “Condition” part of the “IF” becomes:

IF(AND(2<2,(2-2)>1)

Breaking this apart, our two conditions to check for:

Is 2 less than 2? (FALSE)
Is 2 minus 2 greater than 1? (FALSE)

Since both of these are False (remember, in an “AND” function both conditions must be True for the entire condition to be True), we move to the “FALSE” side of the “IF”, which by replacing its functions with our values, becomes:

(((DATEDIF(“5/12/2009”, “5/12/2009”,"D")+1))-(FLOOR((DATEDIF(“5/12/2009”, “5/12/2009”,"D")+1)/7,1)*2))

Completing some of the functions:

(((0+1))-(FLOOR((0+1)/7,1)*2))

Cleaning it up some:

(1-(FLOOR(1/7,1)*2))

And again:

(1-(FLOOR(0.14286,1)*2))

One more time:

(1-(0*2))

Finishing off the math:

(1-(0*2)) = (1-0) = 1

Result = 1 since the “Start Date” and “End Date” are both on the same day, 1 day has elapsed.

Test #2: “No Weekend” (both dates in same week)
Start Date: 5/18/2009
End Date: 5/22/2009

Functions:

WEEKDAY([Start Date],2) = WEEKDAY(“5/18/2009”,2) = Monday = 1
WEEKDAY([End Date],2) = WEEKDAY(“5/22/2009”,2) = Friday = 5
DATEDIF(“5/18/2009”, “5/22/2009”,"D") = 4

Our “Condition” part of the “IF” becomes:

IF(AND(5<1,(1-5)>1)

Breaking this apart, our two conditions to check for:

Is 5 less than 1? (FALSE)
Is 1 minus 5 greater than 1? (FALSE)

Since both of these are False, we move to the “FALSE” side of the “IF”, which by replacing its functions with our values, becomes:

(((DATEDIF(“5/18/2009”, “5/22/2009”,"D")+1))-(FLOOR((DATEDIF(“5/18/2009”, “5/22/2009”,"D")+1)/7,1)*2)))

Completing some of the functions:

(((4+1))-(FLOOR((4+1)/7,1)*2)))

Cleaning it up some:

(5-(FLOOR(5/7,1)*2))

And again:

(5-(FLOOR(0.71428,1)*2))

One more time:

(5-(0*2))

Finishing off the math:

(5)-(0*2)) = (5-0) = 5

Result = 5 since the “Start Date” is on Monday and “End Date” is on the Friday of the same week, 5 days have elapsed.

Test #3: “With Weekend” (weekend days in between start and end dates)
Start Date: 5/22/2009
End Date: 5/26/2009

Functions:

WEEKDAY([Start Date],2) = WEEKDAY(“5/22/2009”,2) = Friday = 5
WEEKDAY([End Date],2) = WEEKDAY(“5/26/2009”,2) = Tuesday = 2
DATEDIF(“5/22/2009”, “5/26/2009”,"D") = 4

Our “Condition” part of the “IF” becomes:

IF(AND(2<5,(5-2)>1)

Breaking this apart, our two conditions to check for:

Is 2 less than 5? (TRUE)
Is 5 minus 2 greater than 1? (TRUE)

Since both of these are True (in the “AND” function, all parts must evaluate to True in order for the entire check to be True), we move to the “TRUE” side of the “IF”, which by replacing its functions with our values, becomes:

(((DATEDIF(“5/22/2009”, “5/26/2009”,"D")+1))-(FLOOR((DATEDIF(“5/22/2009”, “5/26/2009”,"D")+1)/7,1)*2)-2)

Completing some of the functions:

(((4+1))-(FLOOR((4+1)/7,1)*2)-2)

Cleaning it up some:

(5-(FLOOR(5/7,1)*2)-2)

And again:

(5-(FLOOR(0.71428,1)*2)-2)

One more time:

(5-(0*2)-2)

Finishing off the math:

(5-0-2) = 3

Result = 3 since the “Start Date” is on Friday and “End Date” is on the Tuesday of the following week, 5 days have elapsed with 2 of them being weekend days, so we subtract 2 from the total giving us a final result of 3 (counting Friday, Monday, and Tuesday).

Test #4: “Multiple Weekends” (more than one set of weekends)
Start Date: 5/11/2009
End Date: 5/25/2009

Functions:

WEEKDAY([Start Date],2) = WEEKDAY(“5/11/2009”,2) = Monday = 1
WEEKDAY([End Date],2) = WEEKDAY(“5/25/2009”,2) = Monday = 1
DATEDIF(“5/11/2009”, “5/25/2009”,"D") = 14

Our “Condition” part of the “IF” becomes:

IF(AND(1<1,(1-1)>1)

Breaking this apart, our two conditions to check for:

Is 1 less than 1? (FALSE)
Is 0 greater than 1? (FALSE)

Since both of these are False, we move to the “FALSE” side of the “IF”, which by replacing its functions with our values, becomes:
(((DATEDIF(“5/11/2009”, “5/25/2009”,"D")+1))-(FLOOR((DATEDIF(“5/11/2009”, “5/25/2009”,"D")+1)/7,1)*2)))

Completing some of the functions:

(((14+1))-(FLOOR((14+1)/7,1)*2)))

Cleaning it up some:

(15-(FLOOR(15/7,1)*2)

And again:

(15-(FLOOR(2.14285,1)*2)

One more time:

(15-(2*2))

Finishing off the math:

(15-(2*2)) = (15-4) = 11

Result = 11 since the “Start Date” is on a Monday and “End Date” is also on a Monday, but two weeks later, we count the total number of days between the two (15) then subtract the number of weekend days (4) giving us a total of 11 days elapsed.

In the next part of this series, we’ll be wrapping things up with a quick revisit to our (now more understandable) formula, and summary.

Till then…

- Dessie

Dessie Lunsford
Points-of-Sharing