Week num required

[Ravi Verma]

Hi Excel Champs,

need your help in generating an excel formula to get the desired week number. I have attached the xls file for your kind perusal.

Regards,

 

[sudipto3003]

Hi Ravi,

You can try this......
http://www.rondebruin.nl/win/s8/win001.htm

 

[Ravi Verma]

Hi Sudipto3003,

It dint help me .. My concern is not rfealted to week numbers in a year .. my concern is that i need to allocate 1-5 basis the date .. if you check my attached file i have highlighted the desired result in yellow color.

Please help

 

[tomas]

Hm. 30.3.2015 is monday and First week in april and 27.4.2015 is monday but it's fifth week of april. So You should specify how to define weeks which are in both months because it's not possible to deduct that based on available data.

 

[sudipto3003]

may be the following formula can help you...
=IF(IF(MONTH(E5)=1,WEEKNUM(E5),WEEKNUM(E5-1)-WEEKNUM(E5-DAY(E5)-6))=0,1,IF(MONTH(E5)=1,WEEKNUM(E5),WEEKNUM(E5-1)-WEEKNUM(E5-DAY(E5)-6)))
late me know

 

[Khalid NGO]

Hi Verma,
Adding to tomas,

same confusion with other dates like 1st April (week 1)
1st May (week 5) etc

needs more clarification.

Regards,

 

[Ravi Verma]

may be the following formula can help you...
=IF(IF(MONTH(E5)=1,WEEKNUM(E5),WEEKNUM(E5-1)-WEEKNUM(E5-DAY(E5)-6))=0,1,IF(MONTH(E5)=1,WEEKNUM(E5),WEEKNUM(E5-1)-WEEKNUM(E5-DAY(E5)-6)))
late me know

Sir,

Its giving incorrect output.

Column D is the output that i require.. I know its difficult hence needed your assistance.

Regards,

 

[NARAYANK991]

Hi ,

You have not mentioned which of the two you want :

1. Given a table of dates , whether you want the result you have shown in column D

2. Given any date , you want the correct week number according to the result you have shown

I am attaching a file which gives 1 ; if you want 2 , what I suggest is just extend your existing table of dates , and the formula ; thereafter , given any date , you can just lookup the week number from the table.

Narayan

 

[Ravi Verma]

Hi ,

You have not mentioned which of the two you want :

1. Given a table of dates , whether you want the result you have shown in column D

2. Given any date , you want the correct week number according to the result you have shown

I am attaching a file which gives 1 ; if you want 2 , what I suggest is just extend your existing table of dates , and the formula ; thereafter , given any date , you can just lookup the week number from the table.

Narayan

Dear Narayan,

I tried your excel formula however it doesnt work at times.. I have tried checking it with dates from 31st Dec 2012 till 31st Dec 2013 .. it gave some correct numbers and some incorrect .. I have attached the working file for your reference.

Many thanks !!
Regards

 

[NARAYANK991]

Hi ,

I am sorry , but I am not able to understand the logic behind your week numbering. Please explain why January 28 is week number 5 , while April 29 is week number 1. July 29 is week number 1 , while October 28 is week number 5.

Narayan

 

[Ravi Verma]

Hi ,

I am sorry , but I am not able to understand the logic behind your week numbering. Please explain why January 28 is week number 5 , while April 29 is week number 1. July 29 is week number 1 , while October 28 is week number 5.

Narayan

Sir,

i need to allocate week 1-5 basis the start of the week in a month and wherever 2 months coincide the month should be added , the week with 4 days is assumed in that particular month and likewise i need to allocate week number 1- 5... if you check Column F , it tells me which month will the week fall into...

Regards,

 

[NARAYANK991]

Hi ,

See if this is OK.

Narayan

 

[barry houdini]

For a formula that only references the date try this in D5 copied down to get the week number of the month:

=INT((6+DAY(E5+4-WEEKDAY(E5-1)))/7)

That assigns week 1 as required, i.e. the first week with the majority of days in the relevant month.

This should work for any date in any year

 

[Ravi Verma]

Thanks a lot, Barry ..

You are Excel Champs !!