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

[TABLE="width: 313"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]01/01/18[/TD]
[TD]1st Monday[/TD]
[/TR]
[TR]
[TD]02/01/18[/TD]
[TD]1st Tuesday[/TD]
[/TR]
[TR]
[TD]03/01/18[/TD]
[TD]1st Wednesday[/TD]
[/TR]
[TR]
[TD]04/01/18[/TD]
[TD]1st Thursday[/TD]
[/TR]
[TR]
[TD]05/01/18[/TD]
[TD]1st Friday[/TD]
[/TR]
[TR]
[TD]06/01/18[/TD]
[TD]1st Saturday[/TD]
[/TR]
[TR]
[TD]07/01/18[/TD]
[TD]1st Sunday[/TD]
[/TR]
[TR]
[TD]08/01/18[/TD]
[TD]2nd Monday[/TD]
[/TR]
[TR]
[TD]09/01/18[/TD]
[TD]2nd Tuesday[/TD]
[/TR]
[TR]
[TD]10/01/18[/TD]
[TD]2nd Wednesday[/TD]
[/TR]
[TR]
[TD]11/01/18[/TD]
[TD]2nd Thursday[/TD]
[/TR]
[TR]
[TD]12/01/18[/TD]
[TD]2nd Friday[/TD]
[/TR]
[TR]
[TD]13/01/18[/TD]
[TD]2nd Saturday[/TD]
[/TR]
[TR]
[TD]14/01/18[/TD]
[TD]2nd Sunday[/TD]
[/TR]
[TR]
[TD]15/01/18[/TD]
[TD]3rd Monday[/TD]
[/TR]
[TR]
[TD]16/01/18[/TD]
[TD]3rd Tuesday[/TD]
[/TR]
[TR]
[TD]17/01/18[/TD]
[TD]3rd Wednesday[/TD]
[/TR]
[TR]
[TD]18/01/18[/TD]
[TD]3rd Thursday[/TD]
[/TR]
[TR]
[TD]19/01/18[/TD]
[TD]3rd Friday[/TD]
[/TR]
[TR]
[TD]20/01/18[/TD]
[TD]3rd Saturday[/TD]
[/TR]
[TR]
[TD]21/01/18[/TD]
[TD]3rd Sunday[/TD]
[/TR]
[TR]
[TD]22/01/18[/TD]
[TD]4th Monday[/TD]
[/TR]
[TR]
[TD]23/01/18[/TD]
[TD]4th Tuesday[/TD]
[/TR]
[TR]
[TD]24/01/18[/TD]
[TD]4th Wednesday[/TD]
[/TR]
[TR]
[TD]25/01/18[/TD]
[TD]4th Thursday[/TD]
[/TR]
[TR]
[TD]26/01/18[/TD]
[TD]4th Friday[/TD]
[/TR]
[TR]
[TD]27/01/18[/TD]
[TD]4th Saturday[/TD]
[/TR]
[TR]
[TD]28/01/18[/TD]
[TD]4th Sunday[/TD]
[/TR]
[TR]
[TD]29/01/18[/TD]
[TD]5th Monday[/TD]
[/TR]
[TR]
[TD]30/01/18[/TD]
[TD]5th Tuesday[/TD]
[/TR]
[TR]
[TD]31/01/18[/TD]
[TD]5th Wednesday[/TD]
[/TR]
</tbody>[/TABLE]

 

[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")