1. I'm trying to find a date formula that would give me the date of the 2nd Wednesday of each month. I was able to narrow it down to this formula but some dates are correct and some dates are off.

C3= Thursday, January 8, 2009 D3= 5

=IF(D3<4.3-D3,11-D3)+C3.

Thank you.

2. Altering the formula from Post 758306

=C3-DAY(C3)+1+MOD(4-WEEKDAY(C3-DAY(C3)+1),7)+7

3. The formula works great but I tried to evaluate your formula but it appears that I need assistance to put them in laymen terms or me?

TIA.

4. Do you really want to know the mathematics behind the formula?

5. would it burn any more of my brain's cells? apparently you know I don't have that much left. btw, congratulations on the new Woody's. It looks great but the people behind it that makes it much better. Danke!

6. OK, here you go:

Let's take Sunday, February 22, 2009 as an example

C3 = the date (22-Feb-2009).
Day(C3) = the day of the month (22).
C3-DAY(C3) is 22 days before 22-Feb-2009 i.e. the last day of the previous month (31-Jan-2009).
C3-DAY(C3)+1 is one day after that, i.e. the first day of the current month (01-Feb-2009).
WEEKDAY(C3-DAY(C3)+1) is the day of the week of that date, where Sunday = 1, Monday = 2, …, Saturday = 7. In our example, it is 1.
4-WEEKDAY(C3-DAY(C3)+1) is 3 for Sunday, 2 for Monday, …, -3 for Saturday. In our example, it is 3.
MOD(4-WEEKDAY(C3-DAY(C3)+1),7) calculates the remainder of that number after integer division by 7; this is 3 for Sunday, 2 for Monday, 1 for Tuesday, 0 for Wednesday, 6 for Thursday, 5 for Friday and 4 for Saturday. In our example, it is 3.
This is the number of days you have to add to the first of the month to end up at the first Wednesday in the month.
So C3-DAY(C3)+1+MOD(4-WEEKDAY(C3-DAY(C3)+1),7) is the first Wednesday (04-Feb-2009)
Finally, 7 is added to this to give the second Wednesday in the month:
=C3-DAY(C3)+1+MOD(4-WEEKDAY(C3-DAY(C3)+1),7)+7
In our example, this is 11-Feb-2009.

7. Thank you for your explanation. I'll take an aspirin now

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•