Calculating the first and third Thursdays

joshgriga

New Member
Joined
Oct 14, 2011
Messages
25
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?
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623
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

New Member
Joined
Oct 14, 2011
Messages
25
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

Well-known Member
Joined
May 9, 2009
Messages
16,623
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

New Member
Joined
Oct 14, 2011
Messages
25

ADVERTISEMENT

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

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623
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

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825

ADVERTISEMENT

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

Well-known Member
Joined
May 9, 2009
Messages
16,623
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

New Member
Joined
Oct 14, 2011
Messages
25
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

Well-known Member
Joined
May 9, 2009
Messages
16,623
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,129,804
Messages
5,638,455
Members
417,025
Latest member
MusterDuster

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top