#1
|
|||
|
|||
Remove week 53 from excel
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 |
#2
|
|||
|
|||
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? |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
Quote:
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... |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
Quote:
|
#8
|
|||
|
|||
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.
|
#9
|
|||
|
|||
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.
|
#10
|
|||
|
|||
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. |
#11
|
|||
|
|||
Thanks! I can play with that and modify it to my needs!
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Week by week report | thedeadzeds | Excel | 3 | 11-16-2013 02:06 AM |
Need an excel formula to remove dashes in a number sequence | jyfuller | Excel Programming | 3 | 02-27-2013 10:32 PM |
5 days/week during planning & 7 days/week during implementation | sanlen | Project | 1 | 06-25-2012 04:17 PM |
Conditional Formatting per Week | mrgy05 | Excel | 2 | 06-07-2012 10:38 AM |
MS Excel Remove password encryption | Rio Pauline | Excel | 1 | 05-29-2012 03:34 AM |