Calendar-like question

cpupartsplz

New Member
Joined
Oct 18, 2006
Messages
12
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
Joined
Mar 2, 2002
Messages
1,934
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
Joined
Oct 18, 2006
Messages
12
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
Joined
Mar 2, 2002
Messages
1,934
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
Joined
Oct 18, 2006
Messages
12

ADVERTISEMENT

Thanks!

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

cpupartsplz

New Member
Joined
Oct 18, 2006
Messages
12
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
Joined
Mar 2, 2002
Messages
1,934
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
Joined
Mar 2, 2002
Messages
1,934
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
 

Forum statistics

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