Return nth day of the month

[djxela]

I need a formula that will read a date and return the nth day of that month. For example 01/02/2019 = 1st Friday

 

[Macropod]

Try: =DAY(A1)&MID("thstndrdth",MIN(9,2*RIGHT(DAY(A1))*(MOD(DAY(A1)-11,100)>2)+1),2)&TEXT(A1, " dddd")

 

[Special-K99]

Or (I dont like this now Ive seen Macropod's much shorter formula!)

=QUOTIENT(A1-DATEVALUE("01/"&TEXT(A1,"mm/yyyy")),7)+1&LOOKUP(QUOTIENT(A1-DATEVALUE("01/"&TEXT(A1,"mm/yyyy")),7)+1,{1,2,3,4,5},{"st","nd","rd","th","th"})&TEXT(A1," dddd")

 

[Tom.Jones]

Or (I dont like this now Ive seen Macropod's much shorter formula!)

=QUOTIENT(A1-DATEVALUE("01/"&TEXT(A1,"mm/yyyy")),7)+1&LOOKUP(QUOTIENT(A1-DATEVALUE("01/"&TEXT(A1,"mm/yyyy")),7)+1,{1,2,3,4,5},{"st","nd","rd","th","th"})&TEXT(A1," dddd")

DATEVALUE is not necessary in your fomula.

=QUOTIENT(A1-("01/"&TEXT(A1,"mm/yyyy")),7)+1&LOOKUP(QUOTIENT(A1-("01/"&TEXT(A1,"mm/yyyy")),7)+1,{1,2,3,4,5},{"st","nd","rd","th","th"})&TEXT(A1," dddd")

 

[Eric W]

If you want to find the nth "Friday" of a given month and year, check out post # 2 of this thread:

https://www.mrexcel.com/forum/excel-questions/1083202-formula-show-dates-3rd-tues-every-month.html

 

[djxela]

Hi Macropod

Thank you for prompt assistance.
I think i might have been misunderstood. What i wanted the result to be is if that specific date is the 1st Friday, 2nd Friday, 4th Friday, or 1st Monday, 2nd Monday, 3rd Monday or 4th Monday of the month
Something line the below

01/01/18	1st Monday
02/01/18	1st Tuesday
03/01/18	1st Wednesday
04/01/18	1st Thursday
05/01/18	1st Friday
06/01/18	1st Saturday
07/01/18	1st Sunday
08/01/18	2nd Monday
09/01/18	2nd Tuesday
10/01/18	2nd Wednesday
11/01/18	2nd Thursday
12/01/18	2nd Friday
13/01/18	2nd Saturday
14/01/18	2nd Sunday
15/01/18	3rd Monday
16/01/18	3rd Tuesday
17/01/18	3rd Wednesday
18/01/18	3rd Thursday
19/01/18	3rd Friday
20/01/18	3rd Saturday
21/01/18	3rd Sunday
22/01/18	4th Monday
23/01/18	4th Tuesday
24/01/18	4th Wednesday
25/01/18	4th Thursday
26/01/18	4th Friday
27/01/18	4th Saturday
28/01/18	4th Sunday
29/01/18	5th Monday
30/01/18	5th Tuesday
31/01/18	5th Wednesday

<colgroup><col><col></colgroup><tbody>
</tbody>

 

[Tetra201]

See if the following formula works for you:

=CHOOSE(QUOTIENT(A1-EOMONTH(A1,-1)-1,7)+1,"1st","2nd","3rd","4th","5th")&TEXT(A1," dddd")

 

[Eric W]

Or:

=MID("1st2nd3rd4th5th",INT((DAY(A1)-1)/7)*3+1,3)&TEXT(A1," dddd")