Date and Day Programming

[Guest]

In my spreadsheet, I have some inspection dates of some materials that I
have. I have to inspect these items on a "julian date" (30-day) interval.
For example:

A1 --> inspected date is 1 February 2006 (which is a Wednesday)
B1 --> Next inspection due date is 30 days from inspected date (may or may
not be
a Wedensday)

I have to inspect this item every 30 days, so I used the formula:

(In cell B1) "=A1+30"

This gave me 30 days after the inspected date, but I want the inspection to
show due on the Wednesday before the 30-day becomes due. Is there a way to
tell Excel to defer to the Wednesday immediately before the 30-day inpsection
is due?

 

[Bob Phillips]

=A1+30-CHOOSE(WEEKDAY(A1),6,0,1,2,3,4,5)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Guest]

Hey thanks Bob for your help. I used that formula in my cell, and it
returned a number of 25. What exactly is this displaying to me? I just
don't understand what exactly this formula is accomplishing. Thanks.

 

[Guest]

Bob's formula subtracts a number based on the weekday of your planned
inspection date to calculate the date 28 days after to ensure it falls on a
Wednesday. I tried it and it works OK for me. B1 must be formatted as date.

 

[Guest]

thank you very much for the explanation.

Bob's formula subtracts a number based on the weekday of your planned
inspection date to calculate the date 28 days after to ensure it falls on a
Wednesday. I tried it and it works OK for me. B1 must be formatted as date.