Deciphering the Puzzle (v1.0)

Back to our formula, lets convert it into “plain English” (or close to, at least) to see just what’s occurring in the logic being processed:

The formula:

=IF(AND((WEEKDAY([End Date],2))<(WEEKDAY([Start Date],2)),((WEEKDAY([Start Date],2))-(WEEKDAY([End Date],2)))>1),(((DATEDIF([Start Date],[End Date],"D")+1))-(FLOOR((DATEDIF([Start Date],[End Date],"D")+1)/7,1)*2)-2),(((DATEDIF([Start Date],[End Date],"D")+1))-(FLOOR((DATEDIF([Start Date],[End Date],"D")+1)/7,1)*2)))

The (albeit somewhat confusing) translation:

If the number of the day of the week in the “End Date” column (with “1” equaling “Monday” through “7” equaling “Sunday”) is less than the number of the day of the week in the “Start Date” column (with “1” equaling “Monday” through “7” equaling “Sunday”), AND the number of the day of the week in the “Start Date” column (with “1” equaling “Monday” through “7” equaling “Sunday”) minus the number of the day of the week in the “End Date” column (with “1” equaling “Monday” through “7” equaling “Sunday”) is greater than 1, count the number of days that separate the “Start Date” from the “End Date” plus 1, minus the number of days that separate the “Start Date” from the “End Date” plus 1 divided by 7 and rounded down to the nearest 1 number and multiplied by 2 with 2 subtracted from the result.  If the number of the day of the week in the “End Date” column (with “1” equaling “Monday” through “7” equaling “Sunday”) is less than the number of the day of the week in the “Start Date” column (with “1” equaling “Monday” through “7” equaling “Sunday”), AND the number of the day of the week in the “Start Date” column (with “1” equaling “Monday” through “7” equaling “Sunday”) minus the number of the day of the week in the “End Date” column (with “1” equaling “Monday” through “7” equaling “Sunday”) is not greater than 1, count the number of days that separate the “Start Date” from the “End Date” plus 1, minus the number of days that separate the “Start Date” from the “End Date” plus 1 divided by 7 rounded down to the nearest 1 multiplied by 2, and display the result.

Whew!!!  That was a mouthful (told you it’d be somewhat confusing).

Let’s try and simplify this some to help clear it up.

We know we’re using the “Return_type”  of 2 for each and every “WEEKDAY” function (resulting in “Monday” equaling “1” through “Sunday” equaling “7), so we can skip mentioning it each time in the paragraph.

We also know what our question is, so we don’t have to repeat it in the “FALSE” side of the “IF”.

Since we also now know how the “WEEKDAY”, “DATEDIF”, and “FLOOR” functions work, we don’t need to expand each of them out, so we’ll just replace the long-explanation of each one with their actual formula instead.

Stripping out each of these three pieces should make it clearer to understand (no guarantees at this point).

Deciphering the Puzzle (v1.1)

Our question:

Part 1: If WEEKDAY(“End Date”,2) is less than WEEKDAY(“Start Date”)
Part 2: If WEEKDAY(“Start Date”,2) plus one, minus WEEKDAY(“End Date”,2) is greater than one

Result if both parts of the question are “TRUE”:

Take the DATEDIF(“Start Date”,”End Date”,”D”) plus 1, then subtract the FLOOR value (to the nearest “1”) divided by 7 of the DATEDIF(“Start Date”,”End Date”,”D”) plus 1 multiplied by 2, then subtract 2 from the result.

Result if either or both of the pieces of the question are “FALSE”:

Take the DATEDIF(“Start Date”,”End Date”,”D”) plus 1, then subtract the FLOOR value (to the nearest “1”) divided by 7 of the DATEDIF(“Start Date”,”End Date”,”D”) plus 1 multiplied by 2.

Clear as mud yet?  Let’s try and make it a little simpler.

Our question is just an “IF” with two parts that must both result in “TRUE” for the entire question to be “TRUE” (an “AND” function).  We’re using the “WEEKDAY” function to determine the day of the week for both the “Start Date” and “End Date” along with some simple math to determine if the “End Date” occurred later than the “Start Date”.

Probably the best approach to really be able to see just what’s going on is to plug in a few test dates and run through the calculations, which is what we’ll begin with next time.

Till then…

- Dessie

Dessie Lunsford
Points-of-Sharing