Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 03-25-2014, 06:50 AM
YounesB3 YounesB3 is offline Remove week 53 from excel Windows XP Remove week 53 from excel Office 2010 32bit
Advanced Beginner
Remove week 53 from excel
 
Join Date: Jul 2012
Posts: 37
YounesB3 is on a distinguished road
Default 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
Reply With Quote
  #2  
Old 03-25-2014, 07:24 AM
gebobs gebobs is offline Remove week 53 from excel Windows 7 64bit Remove week 53 from excel Office 2010 64bit
Expert
 
Join Date: Mar 2014
Location: Atlanta
Posts: 837
gebobs has a spectacular aura aboutgebobs has a spectacular aura about
Default

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?
Reply With Quote
  #3  
Old 03-25-2014, 07:44 AM
YounesB3 YounesB3 is offline Remove week 53 from excel Windows XP Remove week 53 from excel Office 2010 32bit
Advanced Beginner
Remove week 53 from excel
 
Join Date: Jul 2012
Posts: 37
YounesB3 is on a distinguished road
Default

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.
Reply With Quote
  #4  
Old 03-25-2014, 08:33 AM
gebobs gebobs is offline Remove week 53 from excel Windows 7 64bit Remove week 53 from excel Office 2010 64bit
Expert
 
Join Date: Mar 2014
Location: Atlanta
Posts: 837
gebobs has a spectacular aura aboutgebobs has a spectacular aura about
Default

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
Reply With Quote
  #5  
Old 03-25-2014, 08:54 AM
YounesB3 YounesB3 is offline Remove week 53 from excel Windows XP Remove week 53 from excel Office 2010 32bit
Advanced Beginner
Remove week 53 from excel
 
Join Date: Jul 2012
Posts: 37
YounesB3 is on a distinguished road
Default

Quote:
Originally Posted by gebobs View Post
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...
Reply With Quote
  #6  
Old 03-25-2014, 09:25 AM
gebobs gebobs is offline Remove week 53 from excel Windows 7 64bit Remove week 53 from excel Office 2010 64bit
Expert
 
Join Date: Mar 2014
Location: Atlanta
Posts: 837
gebobs has a spectacular aura aboutgebobs has a spectacular aura about
Default

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
Reply With Quote
  #7  
Old 03-25-2014, 09:37 AM
YounesB3 YounesB3 is offline Remove week 53 from excel Windows XP Remove week 53 from excel Office 2010 32bit
Advanced Beginner
Remove week 53 from excel
 
Join Date: Jul 2012
Posts: 37
YounesB3 is on a distinguished road
Default

Quote:
Originally Posted by gebobs View Post
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?
Reply With Quote
  #8  
Old 03-25-2014, 09:46 AM
gebobs gebobs is offline Remove week 53 from excel Windows 7 64bit Remove week 53 from excel Office 2010 64bit
Expert
 
Join Date: Mar 2014
Location: Atlanta
Posts: 837
gebobs has a spectacular aura aboutgebobs has a spectacular aura about
Default

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.
Reply With Quote
  #9  
Old 03-25-2014, 09:57 AM
YounesB3 YounesB3 is offline Remove week 53 from excel Windows XP Remove week 53 from excel Office 2010 32bit
Advanced Beginner
Remove week 53 from excel
 
Join Date: Jul 2012
Posts: 37
YounesB3 is on a distinguished road
Default

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.
Reply With Quote
  #10  
Old 03-25-2014, 10:04 AM
gebobs gebobs is offline Remove week 53 from excel Windows 7 64bit Remove week 53 from excel Office 2010 64bit
Expert
 
Join Date: Mar 2014
Location: Atlanta
Posts: 837
gebobs has a spectacular aura aboutgebobs has a spectacular aura about
Default

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
File Type: xlsx YounesB3.xlsx (12.4 KB, 16 views)
Reply With Quote
  #11  
Old 03-25-2014, 11:10 AM
YounesB3 YounesB3 is offline Remove week 53 from excel Windows XP Remove week 53 from excel Office 2010 32bit
Advanced Beginner
Remove week 53 from excel
 
Join Date: Jul 2012
Posts: 37
YounesB3 is on a distinguished road
Default

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

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Remove week 53 from excel Week by week report thedeadzeds Excel 3 11-16-2013 02:06 AM
Remove week 53 from excel Need an excel formula to remove dashes in a number sequence jyfuller Excel Programming 3 02-27-2013 10:32 PM
Remove week 53 from excel 5 days/week during planning & 7 days/week during implementation sanlen Project 1 06-25-2012 04:17 PM
Remove week 53 from excel 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

Other Forums: Access Forums

All times are GMT -7. The time now is 10:34 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft