Calculating the first and third Thursdays

[joshgriga]

So I went ahead and bought the Mr. Excel book to learn my way around. Granted I've made some huge strides, I'm kind of stuck. Here's what the book has to calculate the first Thursday of the month (with the current date in cell A2):

B1 =EOMONTH(A2,-1)+1+MOD((7-WEEKDAY(EOMONTH(A2,-1)+1,15)),7)

Now the book says that this will work with excel 2007 and 2010 and I'm using 2007 but the result I get is: #NUm!
Based on my conclusion, when using the WEEKDAY function, "return_type" 11-17 does not work with excel 2007. You are left with only "return_type" 1-3; with 3 being the easiest to use.
With that said, here's what I came up with; it gave me the answer I'm looking for (10/6/2011) but that doesn't necessarily means it's working right!?!

B2 =EOMONTH(A2,-1)+5+MOD((3-WEEKDAY(EOMONTH(A2,-1)+1,3)),3)

And below is another way of coming up with the first Thursday that I found (I tested this one on Oct, Nov and Dec and it works, but it's a bit lengthy):

B3 =DATE(YEAR(TODAY()), MONTH(TODAY()), 1+7)-WEEKDAY(DATE(YEAR(TODAY()), MONTH(TODAY()), 8-5))

With this formula in mind, I was able to devise one for the third Thursday of the month (however I was unable to do so using the EOMONTH function):

C3 =DATE(YEAR(TODAY()), MONTH(TODAY()), 1+7*3)-WEEKDAY(DATE(YEAR(TODAY()), MONTH(TODAY()), 8-5))

Can someone please help me:
1. Confirm that:
A. The "return_type" 11-17 does not work with excel 2007.
B. The formula in B2 works without any faults.
C. The formula in B3 substitutes for B2 perfectly ok and without any faults.
D. The formula is C3 works without any faults.
2. Is there a formula that I can put in cell C2 that is similar to B2 using the EOMONTH function to calculate the third Thursday of the month?

 

[T. Valko]

So I went ahead and bought the Mr. Excel book to learn my way around. Granted I've made some huge strides, I'm kind of stuck. Here's what the book has to calculate the first Thursday of the month (with the current date in cell A2):

B1 =EOMONTH(A2,-1)+1+MOD((7-WEEKDAY(EOMONTH(A2,-1)+1,15)),7)

Now the book says that this will work with excel 2007 and 2010 and I'm using 2007 but the result I get is: #NUm!
Based on my conclusion, when using the WEEKDAY function, "return_type" 11-17 does not work with excel 2007. You are left with only "return_type" 1-3; with 3 being the easiest to use.
With that said, here's what I came up with; it gave me the answer I'm looking for (10/6/2011) but that doesn't necessarily means it's working right!?!

B2 =EOMONTH(A2,-1)+5+MOD((3-WEEKDAY(EOMONTH(A2,-1)+1,3)),3)

And below is another way of coming up with the first Thursday that I found (I tested this one on Oct, Nov and Dec and it works, but it's a bit lengthy):

B3 =DATE(YEAR(TODAY()), MONTH(TODAY()), 1+7)-WEEKDAY(DATE(YEAR(TODAY()), MONTH(TODAY()), 8-5))

With this formula in mind, I was able to devise one for the third Thursday of the month (however I was unable to do so using the EOMONTH function):

C3 =DATE(YEAR(TODAY()), MONTH(TODAY()), 1+7*3)-WEEKDAY(DATE(YEAR(TODAY()), MONTH(TODAY()), 8-5))

Can someone please help me:
1. Confirm that:
A. The "return_type" 11-17 does not work with excel 2007.
B. The formula in B2 works without any faults.
C. The formula in B3 substitutes for B2 perfectly ok and without any faults.
D. The formula is C3 works without any faults.
2. Is there a formula that I can put in cell C2 that is similar to B2 using the EOMONTH function to calculate the third Thursday of the month?

Try these...

A2: =TODAY()

For the 1st Thursday date of the month based on the date in A2:

=A2+8-DAY(A2)-WEEKDAY(A2-DAY(A2)-4)

For the 3rd Thursday date of the month based on the date in A2:

=A2+22-DAY(A2)-WEEKDAY(A2-DAY(A2)-4)

 

[joshgriga]

Awesome, those formulas work great.
So what if I wanted it to be the next first Thursday.
For example, lets say the date in A2 was today (10/25/2011) and I wanted it to calculate to next month's first Thursday (11/03/2011).
How would you do that?

 

[T. Valko]

Awesome, those formulas work great.
So what if I wanted it to be the next first Thursday.
For example, lets say the date in A2 was today (10/25/2011) and I wanted it to calculate to next month's first Thursday (11/03/2011).
How would you do that?

Try these...

For the 1st Thursday of the NEXT month:

=CEILING(EOMONTH(A2,0)+1-DAY(EOMONTH(A2,0)+1)+1-5,7)+5

For the 3rd Thursday of the NEXT month:

=CEILING(EOMONTH(A2,0)+1-DAY(EOMONTH(A2,0)+1)+1-5,7)+19

Note that these formula require that you are using the default 1900 date system.

 

[joshgriga]

Pure genius..this works great..thanks again!

 

[T. Valko]

Pure genius..this works great..thanks again!

You're welcome!

I was just thinking about this...

If you use the formula for the 1st Thursday then you can simply refer to that cell and add 14 to get the date for the 3rd Thursday.

For example:

A2 = some date

B2 = formula:

=CEILING(EOMONTH(A2,0)+1-DAY(EOMONTH(A2,0)+1)+1-5,7)+5

Then, for the 3rd Thurday date:

=B2+14

 

[barry houdini]

Hello Biff,

Couldn't you just use the same setup as you suggested for the current month, so instead of

=A2+8-DAY(A2)-WEEKDAY(A2-DAY(A2)-4)

for this month just use this for next month.....

=EOMONTH(A2,0)+8-WEEKDAY(EOMONTH(A2,0)-4)

Date system doesn't matter with that version

 

[T. Valko]

Hello Biff,

Couldn't you just use the same setup as you suggested for the current month, so instead of

=A2+8-DAY(A2)-WEEKDAY(A2-DAY(A2)-4)

for this month just use this for next month.....

=EOMONTH(A2,0)+8-WEEKDAY(EOMONTH(A2,0)-4)

Date system doesn't matter with that version

Yeah, I guess you could.

There's fewer "moving parts" in the EOMONTH version but it also has that ATP stipulation that goes with it.

 

[joshgriga]

Hey guys, I wanted to get rid of the date in A2, so I replaced A2 with TODAY() in the formula you provided and it works too!

=EOMONTH(TODAY(),0)+8-WEEKDAY(EOMONTH(TODAY(),0)-4)

 

[T. Valko]

Hey guys, I wanted to get rid of the date in A2, so I replaced A2 with TODAY() in the formula you provided and it works too!

=EOMONTH(TODAY(),0)+8-WEEKDAY(EOMONTH(TODAY(),0)-4)

If you want to trim a few more keystokes replace TODAY with NOW.

=EOMONTH(NOW(),0)+8-WEEKDAY(EOMONTH(NOW(),0)-4)

Barry is the best date formula person in the Excel community. Period!