Week Number in a month

[AdrianEXC]

Good day everyone,

Hello. I want to be able to show what "Week Number" in a month a particular date falls.

Our week starts on a Saturday and ends on a Friday. Our month's cut off is the last Friday of the month. Meaning, if it is the last Friday of the month today, then tomorrow Saturday will be "Week 1" of the new month.

Example, our Week 4 of May is May19 to May25. The last Friday of the month is May25, so May26 falls into Week 1 of June (that would be May26-Jun1).

Sorry if my explanation is not clear. Any help is highly appreciated.

 

[Tetra201]

Try

=CEILING((A1-WORKDAY.INTL(EOMONTH(WORKDAY.INTL(A1-1,1,"1111011"),-1)+1,-1,"1111011"))/7,1)

 

[AdrianEXC]

Try

=CEILING((A1-WORKDAY.INTL(EOMONTH(WORKDAY.INTL(A1-1,1,"1111011"),-1)+1,-1,"1111011"))/7,1)

I'm bashful to bother you for an explanation on how this works, but THIS WORKS PERFECT! Thank you so much!

 

[Tetra201]

You are most welcome.

Here is how it works:
1) Find the end-of-week Friday (EOWF) for the date under question (EOWF =WORKDAY.INTL(A1-1,1,"1111011"));
2) Find the end of the previous month (EOPM) with regard to EOWF (EOPM = EOMONTH(EOWF,-1));
3) Using EOPM, find the last Friday (LFPM) in that month (LFPM =WORKDAY.INTL(EOPM+1,-1,"1111011"));
4) Subtract LFPM date from the date under question to find the difference in days (NDAYS =A1-LFPM);
5) Divide the result by 7 and round up to the whole number (WNUM =CEILING(NDAYS/7,1).