My fiscal year starts on October 1st and I have a total of 53 weeks in a year. Saturday is considered the first day of the week. How do I calculate the week number of a specific date? For instance, if the date is 4/10/2010, the week number should be 29.
Last edited by katie_10042; 04-26-2010 at 05:05 PM. Reason: Solved
Try this formula
=53-WEEKNUM(DATE(YEAR(B1)-1,10,1))+WEEKNUM(B1)
where your current date is in B1. It does however give me 28 with your example. Double check your count. If it really is 29, then modify the formula with +1. Does that work for you?
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
EDIT: deleted formula
Last edited by Palmetto; 04-26-2010 at 03:42 PM. Reason: formula does not return correct result
Palmetto
Do you know . . . ?
You can leave feedback and add to the reputation of all who contributed a helpful response to your solution by clicking the star icon located at the left in one of their post in this thread.
Oh, My formula will only work up until October 1st. Oppps, it expands to
=IF(MONTH(B1)>=10,WEEKNUM(B1)-WEEKNUM(DATE(YEAR(B1),10,1))+1,53-WEEKNUM(DATE(YEAR(B1)-1,10,1))+WEEKNUM(B1))
I'm thinking someone here can simplify.
Try this,
A1=04/10/2010
=INT((A1-WEEKDAY(A1+1)-DATE(YEAR(A1+7-WEEKDAY(A1+1))-(MONTH(A1)<10),10,1))/7)+2
Reagards
Thanks sailepaty! The formula works perfectly!
If A1 is 31st December 2011 sailepaty's formula returns -38, and you get similar results for the last few days of some other years.
Assuming your week numbers are defined as follows:
week 1 starts on 1st October then week 2 starts on the next Saturday after that, then you can use this formula to get the week numbers
=INT((A1-DATE(YEAR(A1+92)-1,10,1)-WEEKDAY(A1+1))/7)+2
Like WEEKNUM function that formula can give you 54 in certain circumstances (only on 30th Sept if that day is a Saturday and the year is a leap year - although the next one isn't until 2028!)
I think this one works better.Originally Posted by daddylonglegs
=INT((A1-WEEKDAY(A1+1)-DATE(YEAR(A1)-(MONTH(A1)<10),10,1))/7)+2
Reagrds
Thanks! Can you show me how to put this formula in Access? Need to replace A1 with [sample1]![Date]. I keep getting this error "The expression you entered has a function containing the wrong number of arguments”. Not sure if it's due to not having the correct parenthesis around correct field or not enough parentheses or if I'm missing an IIF statement near the month part of the formula.
Maybe this works,
WN:Int(([Sample1]![Date]-Weekday([Sample1]![Date]+1)-DateSerial(Year([Sample1]![Date])-IIf(Month([Sample1]![Date])<10,1,0),10,1))/7)+2
Regards
Works perfectly! Thanks!
This didn't help me much as a UK user. So, taking into account the European standard that defines the first week as the one with the majority of days (four or more) falling in the new year, I cooked up this:
=ROUNDDOWN(IF(A1<IF(IF(WEEKDAY(DATE(YEAR(A1),4,1),2)>4,1,2)=1,DATE(YEAR(A1),4,1)+(8-WEEKDAY(DATE(YEAR(A1),4,1),2)),IF(IF(WEEKDAY(DATE(YEAR(A1),4,1),2)>4,1,2)=2,DATE(YEAR(A1),4,1)+(1-WEEKDAY(DATE(YEAR(A1),4,1),2)),"")),((A1-IF(IF(WEEKDAY(DATE(YEAR(A1)-1,4,1),2)>4,1,2)=1,DATE(YEAR(A1)-1,4,1)+(8-WEEKDAY(DATE(YEAR(A1)-1,4,1),2)),IF(IF(WEEKDAY(DATE(YEAR(A1)-1,4,1),2)>4,1,2)=2,DATE(YEAR(A1)-1,4,1)+(1-WEEKDAY(DATE(YEAR(A1)-1,4,1),2)),"")))/7)+1,((A1-IF(IF(WEEKDAY(DATE(YEAR(A1),4,1),2)>4,1,2)=1,DATE(YEAR(A1),4,1)+(8-WEEKDAY(DATE(YEAR(A1),4,1),2)),IF(IF(WEEKDAY(DATE(YEAR(A1),4,1),2)>4,1,2)=2,DATE(YEAR(A1),4,1)+(1-WEEKDAY(DATE(YEAR(A1),4,1),2)),"")))/7)+1),0)
Thanks for this. Works perfectly!
Dear owstie. Thanks for this. Works perfectly!
I used form formulas above so i can calculate fiscal weeks for a fiscal year that starts July 10th 2016 and ends July 8th 2017, but as soon as the date year changes to 2017 i am getting negative numbers. or in some of the other formulas, I am getting the wrong fiscal weeks. can anyone help me out.
ILP welcome to the forum
Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.
If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.
Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
hi,
i need to calculate the fiscal/financial weeks
my month start from april but my first week is from the 28/02/2016 -03/04/2016.
please help
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks