Output Nth occurrence of day i.e. "First Monday of the month" from a Date

[mikechamberlain]

Hi guys, this is my first post. I am in desperate need of an experts help please:

I have a list of dates in excel (i.e. 12/05/2012) and I want to populate a corresponding column with the Nth occurrence of whatever day it happens to be in that particular month (i.e. 12/05/2012 would output "2nd Saturday in month")

Alternatively I would be happy with 2 columns being populated, one with the occurrence (i.e. 2) and the other with the day (i.e. Saturday).


Thanks in advance!!

 

[Special-K99]

=TEXT(A1,"dddd")
will give the day

=INT(DAY(A1)/7)+1
will give the ordinal number

 

[mikechamberlain]

I can't thank you enough!

 

[ukmikeb]

@Special-K99

If the month doesn't begin on a Monday your formula falls over, see below :-
Ordinals[TABLE="class: html-maker-worksheet"]
<TBODY>[TR]
[TH][/TH]
[TH]A
[/TH]
[TH]B
[/TH]
[TH]C
[/TH]
[/TR]
[TR]
[TH]1
[/TH]
[TD="align: right"]12/05/2012
[/TD]
[TD]2nd Saturday
[/TD]
[TD="align: right"]2
[/TD]
[/TR]
[TR]
[TH]2
[/TH]
[TD="align: right"]13/05/2012
[/TD]
[TD]2nd Sunday
[/TD]
[TD="align: right"]2
[/TD]
[/TR]
[TR]
[TH]3
[/TH]
[TD="align: right"]14/05/2012
[/TD]
[TD]2nd Monday
[/TD]
[TD="align: right"]3
[/TD]
[/TR]
[TR]
[TH]4
[/TH]
[TD="align: right"]15/05/2012
[/TD]
[TD]3rd Tuesday
[/TD]
[TD="align: right"]3
[/TD]
[/TR]
</TBODY>[/TABLE]
Excel 2007

Cell	Formula
B1	=INT((DAY(A1)-DAY(EOMONTH(A1,-1)+1))/7)+1&CHOOSE(INT((DAY(A1)-DAY(EOMONTH(A1,-1)+1))/7)+1,"st ","nd ","rd ","th ","th ")& TEXT(A1,"dddd")
C1	=INT(DAY(A1)/7)+1
B2	=INT((DAY(A2)-DAY(EOMONTH(A2,-1)+1))/7)+1&CHOOSE(INT((DAY(A2)-DAY(EOMONTH(A2,-1)+1))/7)+1,"st ","nd ","rd ","th ","th ")& TEXT(A2,"dddd")
C2	=INT(DAY(A2)/7)+1
B3	=INT((DAY(A3)-DAY(EOMONTH(A3,-1)+1))/7)+1&CHOOSE(INT((DAY(A3)-DAY(EOMONTH(A3,-1)+1))/7)+1,"st ","nd ","rd ","th ","th ")& TEXT(A3,"dddd")
C3	=INT(DAY(A3)/7)+1
B4	=INT((DAY(A4)-DAY(EOMONTH(A4,-1)+1))/7)+1&CHOOSE(INT((DAY(A4)-DAY(EOMONTH(A4,-1)+1))/7)+1,"st ","nd ","rd ","th ","th ")& TEXT(A4,"dddd")
C4	=INT(DAY(A4)/7)+1

<TBODY>
[TD="bgcolor: #ffffff"] Worksheet Formulas [TABLE="class: html-maker-worksheet"]
<TBODY>[TR]

</TBODY>

[/TD]
[/TR]
</TBODY>[/TABLE]

hth

 

[mikechamberlain]

Bravo Mike, thank you crazy amounts

 

[ukmikeb]

Thanks for the feedback.

Pleased to have helped solve your problem.

Good luck with your project.