nth day of week

[ipswich56]

Hi All, I have a need to calculate whether a date is the 3rd Thursday or 4th Tuesday etc.

I already know that the 17/01/2022 is a Monday (for example), but I'd like to know whether its the 3rd Monday, 4th Monday etc. Does anyone know how to do that?

Thanks

Ray

[LeoTaxi]

With date in A2

=ROUNDDOWN(DAY(A2)/7,0)+1  

Kind regards
Leo

[ChemistB]

Minor correction to Leo's formula

Please Login or Register  to view this content.

[ipswich56]

Hi Both, apologies if this is a duplicate, as I thought I had already posted - many thanks to you both for such a speedy reply.

I must say I do not understand the subtleties of the minor correction, as when I copy both of the formula into my spreadsheet they both work.

Many thanks again - really much appreciated .

Ray

[LeoTaxi]

I do not understand the subtleties of the minor correction

me to

kind regards
Leo

[KOKOSEK]

Check for June. That's funny, for whole 2021 only June shows difference.

[bebo021999]

(1)=ROUNDDOWN(DAY(A2)/7,0)+1
(2)=ROUNDDOWN((DAY(A2)-1)/7,0)+1

In first 7 days of month, every weekdays must be counted 1, and count 2 from day 8
(1) starts to count 2 from 7th day, 3 from 14th,... => wrong
(2) starts to count 2 from 8th day, 3 from 15th,...=>correct.
that why DAY(A2) minus 1.

[ChemistB]

Without the minus 1, the first week of each month only has 6 days which throws off the first day of each new week by one.
i.e. Jan 2022 begins on a Saturday so the next Friday (7 Jan 2022) should still be week 1 but it calculates as Week 2.

[LeoTaxi]

Yes i was wrong, i see now

thanks for correction and explication

Leo