53 weeks in excel?

[adelkam]

Column A contains date .. Column B contain the week starting "Monday" (also date field) .. Column C contains the week number .. Cell A2 contains date "assume 11/23/2013" .. Cell A3 formula "=A2 +1" .. copy formula in A3 down for about 100 rows or so .. Cell B2 contains formula "=IF(WEEKDAY(A2)=1,A2-6,A2-(WEEKDAY(A2)-2))" .. copy formula down to the end of data range .. cell C2 contains the formula "=WEEKNUM(A2,2)" .. copy formula down .. why end of the year have 53 weeks and week 1 in 2014 have fewer days? Is this a standard? Should this be adjusted? and how?.. thanks

 

[ChrisR]

Hi,

If you think about what you are actually asking Excel to do, then there actually are 53 weeks in the year........

52 weeks * 7 days = 364 days

So therefore you're always going to have 1 or 2 days falling into the 53rd week

I think the issue lies around getting excel to recognise the correct start of week 1, as Excel will always recognise Sunday as being the first day of week 1 (i think!)

Regards,
Chris

 

[Vidar]

Read this useful article about week numbers in Excel. You might get a tip from there.
Week Numbers In Excel

 

[adelkam]

Thanks!! this is very informative ..!!