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.mrexcel.com/forum/excel-q...ml#post3758264

Egad...what is this supposed to be doing?

If I am looking at this right, what this does is assign a Week number based on today's date. So based on today's date (3/25/14)

1/14/14 - 3
1/7/14 - 2
12/31/13 - 53
12/24/13 - 52
12/17/13 - 51

Since the Today() function is dynamic, the values returned will change. Next week...

1/14/14 - 4
1/7/14 - 3
12/31/13 - 2
12/24/13 - 53
12/17/13 - 52

What is it you are trying to do?

Yep! I don't think it's relevant to go into details of what it's supposed to do unless there is no way to remove the week 53. Just to give a quick story short, I'm taking into considering last week's data, the 4 last week's data as well as the last 13 week's data based on a database being updated weekly.

Also, it would be possible to resolve this with an IF formula, but it would take a long time to do and apply on every single formula I already have...

If (weeknum(XYZ)=53,1,weeknum(XYZ))

Also, even if I do that, it might be problematic when I'll be in 2015...

Additional details :
Thee weeks are 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.

So wouldn't you just want the previous weeks numbered 1-13? Thus for this week:

Week 1: 3/16 - 3/22
Week 2: 3/9 - 3/15
Week 3: 3/2 - 3/8
Week 4: 2/23 - 3/1
Week 5: 2/16 - 2/22
Week 6: 2/9 - 2/15
Week 7: 2/2 - 2/8
Week 8: 1/26 - 2/1
Week 9: 1/19 - 1/25
Week 10: 1/12 - 1/18
Week 11: 1/5 - 1/11
Week 12: 12/29 - 1/4
Week 13: 12/22 - 12/28

Yep, that would work, what do you suggest?

Edit: Actually, I just realized that it wouldn't work. The database use the week number 1 to 52 which needs to be associated to my formulas...

So this?

Week 12: 3/16 - 3/22
Week 11: 3/9 - 3/15
Week 10: 3/2 - 3/8
Week 9: 2/23 - 3/1
Week 8: 2/16 - 2/22
Week 7: 2/9 - 2/15
Week 6: 2/2 - 2/8
Week 5: 1/26 - 2/1
Week 4: 1/19 - 1/25
Week 3: 1/12 - 1/18
Week 2: 1/5 - 1/11
Week 1: 12/29 - 1/4
Week 52: 12/22 - 12/28

I guess I could do that... you mean doing a week table for the whole data I'm analyzing right?

The reason you first formulas give 53 is because there are 52 weeks and change in every year. Not sure if this is going to throw off your calculations.

Yes because on the IT side (the database), there is no week 53 for some reason. I'll check with IT tomorrow and we'll see.

Take a look at what I have attached.

To test, enter the date you want (e.g. today's date) into the highlighted cell.

The table beneath give the number of weeks back from the test date (1 -65...so goes back over a year), the start date, the end date and the number I think you're looking for, the week number.

There are probably more elegant solutions.

Attached Files

YounesB3.xlsx (12.4 KB, 18 views)

Thanks! I can play with that and modify it to my needs!