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

	A	B	C
1	12/05/2012	2nd Saturday	2
2	13/05/2012	2nd Sunday	2
3	14/05/2012	2nd Monday	3
4	15/05/2012	3rd Tuesday	3

<TBODY>
</TBODY>

Excel 2007

Worksheet Formulas 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> </TBODY>

<TBODY>
</TBODY>

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.