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?

 

[joshgriga]

Hmm, didn't know about that, but thanks..I enjoy learning more about this stuff. So is there a time when you would use TODAY() that you can't use NOW() and vice versa or are they interchangeable?

 

[T. Valko]

Hmm, didn't know about that, but thanks..I enjoy learning more about this stuff. So is there a time when you would use TODAY() that you can't use NOW() and vice versa or are they interchangeable?

It depends on the application.

The TODAY() function returns the current date like 10/27/2011.

The NOW() function returns the current date plus the current time like 10/27/2011 5:00 PM.

In Excel these values are represented as numeric values.

TODAY() = 40843
NOW() = 40843.7085021991

So, these 2 values are not interchangeable. However, in this application the EOMONTH function will truncate the decimal portion of the numeric value used for the date argument.

EOMONTH(NOW() =
EOMONTH(40843.7085021991

Which gets evaluated as:

EOMONTH(40843

 

[joshgriga]

Aah, I see..makes a lot of sense now that you put it like that. Again, I can't thank you enough!!!

 

[T. Valko]

Aah, I see..makes a lot of sense now that you put it like that. Again, I can't thank you enough!!!

You're welcome!