Remove week 53 from excel

[YounesB3]

Hello, I was wondering if it was possible to remove week 53 from excel.

WEEKNUM(TODAY()-(7*11)) = 2
WEEKNUM(TODAY()-(7*12)) = 53
WEEKNUM(TODAY()-(7*13)) = 52

The database I'm using doesn't have a week 53. I'd like the 53 to be 1.

I had found a solution by converting everything with system 2 (WEEKNUM(TODAY()-(7*?),21)). The thing is, I also have a year function i.e. YEAR(TODAY()-(7*?)).

So basically, I would have XYZ-1-2013 instead of XYZ-1-2014.

Any solutions to this?

Cross post : http://www.msofficeforums.com/excel/20314-remove-week-53-excel.html#post61180

 

[shg]

How do you define the first week of the year?

 

[YounesB3]

How do you define the first week of the year?

From sunday to saturday. So basically, week 1 would include sunday 29th 2013 to saturday 4th 2014.

Instead of having sunday 29th, monday 30th and tuesday 31st as week 53.

 

[shg]

So week 1 of 2016 starts on Sunday 12/27/2015?

 

[Jonmo1]

With this logic, The last day of Week 52 2016 would be 12/24/2016
What happens to 12/25/2016 through 12/31/2016 ?

Do they just not exist anymore?

 

[YounesB3]

With this logic, The last day of Week 52 2016 would be 12/24/2016
What happens to 12/25/2016 through 12/31/2016 ?

Do they just not exist anymore?

I must say that's a question I didn't ask myself. I'll ask IT tomorrow as to what they do each 6 years to adjust. I'll get back to you tomorrow, thanks!

 

[Jonmo1]

It's really a simple/unavoidable mathmatical problem. Think Neo from The Matrix

52*7 = 364
But there are 365 (and an occasional 366th) days in a year.

I'd consult the vendor of your database and ask how they made their database have only 52 weeks.

 

[Jonmo1]

If you simply want to make week 53's into 1's, you can try

IF(WEEKNUM(TODAY())=53,1,WEEKNUM(TODAY()))

But this will make week 1 have more than 7 days.

 

[YounesB3]

Thread solved (see cross-reference link).

As for what I'll do in 2016, I'll ask IT tomorrow, but I'll solve this problem separately. Thanks!

 

[barry houdini]

Note that if you use WEEKNUM function some dates are in week 54....although the next one isn't for 14 years.......

Try

=WEEKNUM("2028-12-31")