# Calculating the first and third Thursdays

#### joshgriga

##### New Member
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))

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
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))

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
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
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

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

#### T. Valko

##### Well-known Member
Pure genius..this works great..thanks again!
You're welcome!

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

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
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
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
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!

Replies
5
Views
100
Replies
18
Views
582
Replies
6
Views
148
Replies
9
Views
89
Replies
2
Views
96

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?

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