Create Date to be Week 1

[Dave350z]

Hi
Is there a formula that I can use to set Week 1 to be 04/05/2014? I know how to change it from a certain day in the week to another, but not to set a specific date to be Week 1 and so on.

Thanks in advance.

Dave

[Glenn Kennedy]

Hi, assuming that your dates start in A1, use this:

=WEEKNUM(DATE(YEAR(A1),MONTH(A1),DAY(A1)))-18

[Special-K]

@Glenn Kennedy

That was my first thought but surely that wont work.

01/01/2015 produces a WEEKNUM of -17.

You need to cater for when the week numbers exceed 33 which will be end of 2014.

[Glenn Kennedy]

Indeed, you're quite right. try this, instead:

Please Login or Register  to view this content.

[daddylonglegs]

Is there a formula that I can use to set Week 1 to be 04/05/2014?

Hello Dave,

I assume that you need to reset to week 1 each year. Is there a more general rule that would apply to any year, e.g. week 1 starts on the first Sunday in May?

[Dave350z]

General weekly rule is that it's Sunday to Saturday is the week.

The week 1 rule is going to be determined most likely by the first Sunday to Saturday full week 1 year later. So in 2015 - 03/05/2015 will be week 1.


By the way - the if formula above doesn't work, I get #NUM error the week before the 04/05/2014 and FALSE on the 04/05/2014 and thereafter.

Ideally I need to be able to drag the formula back as well, so week 52 will be the week before and so on.

[daddylonglegs]

OK, with a any date in A2 this formula will give you the week number assuming that week 1 starts every year on the Sunday in the range 1st - 7th May

=INT((A2-WEEKDAY(A2)-DATE(YEAR(A2+246-WEEKDAY(A2))-1,5,7))/7)+2

As per ISO week numbers that means some years (roughly 1 in six) will have 53 weeks, others will have 52

[Dave350z]

Great, that'll work!

I assume there's no way around the 53 week issue?

[daddylonglegs]

Most week numbering systems will give you a week 53 because, obviously 365/366 days is 1/2 days more then 52 weeks, so 2016 will start on Sunday 1st May but the next year starts on Sunday 7th May so the last week of 2016/2017 will be 53.

If you never go higher than 52 then you can only do that by having the year start getting earlier each year......or some weeks having more than 7 days. This version won't go higher than 52 but it means that some years week 52 is 14 days long!

=MOD(INT((A2-WEEKDAY(A2)-DATE(YEAR(A2+246-WEEKDAY(A2))-1,5,7))/7)+1,52)+1

Note that WEEKNUM can even return 54!