Count nth week of month starting from Sunday...how to do this?

[PP3321]

My requirement is to display week number, based on Sunday.
So, 1st week starts from the 1st Sunday of the month.

<My Current Solution>
I used Countif function.

<Problem of above solution>
Problem is that when the month changes, I cannot reset to 0 and start again.

I would be grateful if you could give me clues how I can achieve this.
Macro, Formula, Date Functions...any tool is OK...

*I am using Excel 2010

Thanking you in advance...

 

[PP3321]

Sorry I achieved using below method:

=IF(B4<>B3,0, IF(C3="Sun",1+D2,D2))

 

[PP3321]

Correction:

=IF(B2<>B3,0, IF(C3="Sun",1+D2,D2))

 

[NARAYANK991]

Hi ,

Have you resolved your problem or not ? It is not clear.

See if the attached file does what you want.

Narayan

 

[PP3321]

Yes this is solved!
Thank you!!!!!

 

[shrivallabha]

You can also choose to use following construct which is not dependent on previous cell's result. It will refer to the date in column A.
=INT((A2-(EOMONTH(A2,-1)+9-WEEKDAY(EOMONTH(A2,-1)+1)))/7)+1

 

[PP3321]

@shrivallabha
Wow...I have never heard of EOMONTH function...
Thank you so much for your advice. It is helping me to learn...

 

[bosco_yip]

You can also choose to use following construct which is not dependent on previous cell's result. It will refer to the date in column A.
=INT((A2-(EOMONTH(A2,-1)+9-WEEKDAY(EOMONTH(A2,-1)+1)))/7)+1

The above formula result will be failed if 1st of the month is Sunday.

Example in the month of Jan 2017, Oct 2017, April 2018….etc.

01/01/2017 (Sun)… return 0 (should be 1)
02/01/2017 (Mon)… return 0 (should be 1)
………………..
………………..
01/10/2017 (Sun)… return 0 (should be 1)
02/10/2017 (Mon)… return 0 (should be 1)
………………..
………………..

Regards
Bosco

 

[PP3321]

@bosco_yip
Thank you for that. Let me have a look...

 

[shrivallabha]

Bosco is right! I had not tested above condition. Here's revised approach which takes care of Bosco's point.
=CEILING((DAY(A2)+WEEKDAY(EOMONTH(A2,-1))-7)/7,1)
It does not rely on previous cell's result like previous formula.

 

[bosco_yip]

Bosco is right! I had not tested above condition. Here's revised approach which takes care of Bosco's point.
=CEILING((DAY(A2)+WEEKDAY(EOMONTH(A2,-1))-7)/7,1)
It does not rely on previous cell's result like previous formula.

Thanks shrivallabha,

1] Nice formula, improved and made shorter.

2] 1st of the month is Sunday problem is solved.

3] Some of the months starting days should give 0, however the formula return #NUM!,

e.g. 01/06/2016, 02/06/2016, 02/06/2016, 01/07/2016, 01/08/2016……etc.

Regards
Bosco

 

[shrivallabha]

Thanks shrivallabha,

1] Nice formula, improved and made shorter.

2] 1st of the month is Sunday problem is solved.

3] Some of the months starting days should give 0, however the formula return #NUM!,

e.g. 01/06/2016, 02/06/2016, 02/06/2016, 01/07/2016, 01/08/2016……etc.

Regards
Bosco

Bosco,

Thanks for testing. I do not get this error mentioned in 3rd point. It gives me 0.

I am attaching my file for reference. Could you check if it also shows erroneous results at your end?

 

[bosco_yip]

Bosco,

Thanks for testing. I do not get this error mentioned in 3rd point. It gives me 0.

I am attaching my file for reference. Could you check if it also shows erroneous results at your end?

Thanks shrivallabha,

I find the problem, my testing file is a Excel 2003 xls file, your attached file is a xlsx file.

Regards
Bosco