Counting partial weeks in a month

[Tashia]

Hi~ Can someone assist me in writing a formula that will give the following results:

Condisdering the work week is Monday - Friday, return:

0 = the first partial week (for example the 1st of the month starts on a Tuesday)
1 = the 1st full week
2 = 2nd full week
3 = 3rd full week
4 = 4th full week
5 = if the month ends on a partial week (for example the 31st lands on a Thursday)

Thanks!!

[daddylonglegs]

So your input is a date and you want to know which week [of the month] it falls within?

This formula will give you 4 for today [24th June 2008], 5 for 30th June then 0 for 1st July, is that right?

=INT((DAY(A1)-WEEKDAY(A1-1))/7)+1

Assumes date in A1.

Note: If the 31st of a month is a Thursday then there can only be 3 complete weeks previously that month so it'll give 4

[Tashia]

That's correct. Thanks for your help!

[okl1234]

hi Daddylonglegs, your formula to uncover a partial week works most of the time- but do you also have a formula that has a default value for the last partial week of the month? ideally, the formula should return unique values for both the first and last partial weeks of the month.
(those partial weeks will have a separate set of computation)

I face this difficulty because while "0" and "5" most definitely means a partial week, the value "4" can mean either a partial week or a complete week.

for example, using Mon-Sun as one week... for the month of Dec'15-

- the week of 1-6 Dec'15 is a partial week and hence using your formula, the week is assigned the value of "0"
- the week of 7-13 Dec'15 is a complete week and hence using your formula, the week is assigned the value of "1"
- the week of 14-20 Dec'15 is a complete week and hence using your formula, the week is assigned the value of "2"
- the week of 21-27 Dec'15 is a complete week and hence using your formula, the week is assigned the value of "3"
- the week of 28-31 Dec'15 is a partial week and hence using your formula, assigned a value of "4"

[okl1234]

I should also add that I am planning to write an IF function based on whether the week of the month is partial or complete... using your formula for example-

if(or(INT((DAY(A1)-WEEKDAY(A1-1))/7)+1)=0,(INT((DAY(A1)-WEEKDAY(A1-1))/7)+1)=5),[value if true],[value if false].

the problem is that "4" can also be a partial week!

[okl1234]

well, i've pretty much solved my own problem!

for those who wish to know, what i've done is essentially add another test for the days that fall in week 4 using the eomonth function; i.e.
- if the date in cell A1 falls on week 4 of the said formula and is also a Monday, then +6 days to it; if the result is <= eomonth(A1,0), then "no value"
(something else will happen if A1+6 is >eomonth(a1,0)

the same test will have to apply for Tue, Wed, Thu, Fri and Sat... i end up with a pretty darn long formula, but well- it does the job!