Rick,

Thank you so much for taking the time and breaking down how the formula
works. I have now added it to my script and I am going to go back and modify
previous sections of my code with your suggestions. :-D

- Jason

"Rick Rothstein" wrote:

CheckForDate = Date - 2 * (1 - (Weekday(Date, vbMonday) < 3))

** This is my understanding:
B/c my case statements subtract a minimum of 2 days (from whatever day it
happens to be) the first part makes sense. I get a little lost when the
Weekday(Date, vbMonday) < 3) is brought in. From what I can gather it
resets
the 'Weekday' start to Monday (which now makes the default value of
vbsunday
= 7 vs 1. )
Now b/c of that the two days where I subtract 4 instead of 2 fall inside
the
new defined range of weekday(date, vbMonday) 2 & 1 (Tuesday & Monday
respectfully) the formula would breakdown today as:

CheckForDate = Date - 2 * (1 - (Weekday(Date, vbMonday) < 3))
CheckForDate = Date (11/4/08) - 2 * (1 - (Weekday(Date, vbMonday) < 3))
CheckForDate = 11/2/08 * (1 - (Weekday(Date, vbMonday) < 3))
CheckForDate = 11/2/08 * (1 - 2)
CheckForDate = 10/31/08 *** [I think]

Your post has made my day... it is always my hope when I post some code that
the OP will actually try to decipher why it works as opposed to simply
copying it blindly.

It might be easier to understand how the code line works by expanding
expression (that is, multiply the 2 across the parentheses). If we do that,
then this...

CheckForDate = Date - 2 * (1 - (Weekday(Date, vbMonday) < 3))

becomes this...

CheckForDate = Date - 2 + 2 * (Weekday(Date, vbMonday) < 3))

The Date - 2 part you understand. As for the rest, let me first remind you
that logical expressions in VB evaluate to -1 when True. So, when the
weekday (with the week starting on Monday) evaluates to 1 or 2 (which as you
figured out occurs on Monday and Tuesday), the -1 that the logical
expression returns is multiplied by 2 and then added to the part you already
understand (remember, you are adding an negative value, so in reality it is
equivalent to a subtraction). The net result is that 2 days are subtracted
on all day except Monday and Tuesday when an additional 2 days are
subtracted as well.

--
Rick (MVP - Excel)