Calendar-like question

[cpupartsplz]

I was wondering if it were possible to do the following...

Basically I want to enter MM/YY in one column and Excel have it spit out the second Thursday of that month in MM/DD form in another column.

I have been struggling with it for a while. And thought someone might have an idea how to do it.

Thanks!

Edit: I know how to add and use Macros, but not how to write code. If that helps...

 

[eliW]

Hi and welcome to the board!

Many possibilities, one of them:

=DATEVALUE("01/"&TEXT(A1,"mm/yy"))+CHOOSE(WEEKDAY("01/"&TEXT(A1,"mm/yy")),11,10,9,8,7,13,12)

I am sure there are shorter solutions.

Eli

 

[cpupartsplz]

Almost there...

Hey Eli,

Thanks for your help. I noticed one problem with that formula. It seemed to work from the beginning of the year, but in the month of October, it listed the month as 01 instead of 10 and the second thursday as 19 instead of 12. Any ideas?

What I ultimately want to happen is...

A1 = Date I enter...as MM/YY
A2 = 2nd Thursday of that month in that year...as MM/DD
A3 = A2 - Today()...in days

I thought about manually looking up every Thursday and putting it on another worksheet and just using a reference, but figured there was a less tedious way.

 

[eliW]

OK

I think that this is due to the differences between US and EUR date formats. My solution is based on dd/mm/yyyy format.

Try this new formula and see if it's working for you.

You of course may format the result date as you wish DD/MM or MM/DD and so on.


=DATEVALUE(TEXT(A1,"mm/yyyy"))+CHOOSE(WEEKDAY(TEXT(A1,"mm/yyyy")),11,10,9,8,7,13,12)
HTH

Eli

 

[cpupartsplz]

Thanks!

That's exactly what I was looking for!
Awesome. Thanks so much!

 

[cpupartsplz]

Just a quick question again. If I wanted to select say the 2nd Friday or 3rd Monday, how would I edit the code? I tried to lookup the "choose" function, but I didn't understand it in terms of a calendar.

 

[eliW]

OK

The first part of the formula:

=DATEVALUE(TEXT(A1,"mm/yyyy"))

finds the first date in the same month for any given date (A1).

The second part of the formula:

CHOOSE(WEEKDAY(TEXT(A1,"mm/yyyy")),11,10,9,8,7,13,12)

has 2 parts: the first:

WEEKDAY(TEXT(A1,"mm/yyyy")

determines what is the week day of the first date in the month.This becomes the first argument to the CHOOSE formula.

In CHOOSE you are choosing a value corresponding to the value of the first argument of the formula. (e.g. 1 -first, 2 -second and so forth). So to the first date in the month the formula adds the corresponding number of days to get to the "second Thursday".

So if the first date of the month happend to be Sunday -then you add 11 days to get to the second Thursday.
Monday +10
Tuesday+9
...
Friday+13

Hope this make sense to you.

If you understand that - it will be easy to you to calculate the date intervals needed to get 2nd Monday or 3rd Thursday.

HTH

Eli

 

[eliW]

The following formula is shorter and will give the same result:

=A1-DAY(A1)+CHOOSE(WEEKDAY(A1-DAY(A1)+1),12,11,10,9,8,14,13)


Eli