# Calendar-like question

#### cpupartsplz

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

### Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

#### eliW

##### Well-known Member
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

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

##### Well-known Member
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

##### New Member

Thanks!

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

#### cpupartsplz

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

##### Well-known Member
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

##### Well-known Member
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

Replies
2
Views
167
Replies
10
Views
219
Replies
7
Views
152
Replies
9
Views
145
Replies
1
Views
604

1,136,878
Messages
5,678,302
Members
419,753
Latest member
Sallylwy

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

### Which adblocker are you using?

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

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