Hi Forum Leader
My fiscal year starts on April to March and I have a total of 52 weeks in a year. Monday 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/2017, the week number should be 27.
Date: Week No
01/04/2017 53
25/04/2017 04
01/05/2017 05
18/05/2017 07
27/05/2017 08
16/10/2017 29
31/03/2018 52
Do we take it that your fiscal year starts on the first Monday in April?
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Hi Forum Leader
My financial year starts on April to March and I have a total of 52 weeks in a year. Monday 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/2017, the week number should be 27.
Date: Week No
01/04/2017----------- 53
25/04/2017----------- 04
01/05/2017----------- 05
18/05/2017----------- 07
27/05/2017----------- 08
16/10/2017----------- 29
31/03/2018----------- 52
thank for your prompt response Mr,AliGW. wrongly mentioned.
financial year started April to march.
You did not answer my question. Does your fiscal year start on the first Monday in April? Yes or no? If 'no', when does it start?
You will see from my avatar that I am female.
I am big a pardon, Yes please I would like the financial year start on 1st Monday in April.
Try with
Formula:
Please Login or Register to view this content.
If Date in "A2" 1/4/2017
atul
If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.
Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".
Try this ...
=IF(NETWORKDAYS.INTL(DATE(YEAR(A1)-(TEXT(A1,"mdd")-401<0),4,0),A1,"0111111")=0,53,
NETWORKDAYS.INTL(DATE(YEAR(A1)-(TEXT(A1,"mdd")-401<0),4,0),A1,"0111111"))
thanks for your reply. formula return false expert first cel
01/04/2017 53
25/04/2017 FALSE
01/05/2017 FALSE
18/05/2017 FALSE
27/05/2017 FALSE
16/10/2017 FALSE
31/03/2018 FALSE
Change "," to ";", see this ...
thanks a lot. formula work perfectly.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
That's an interesting formula Phuocam, but I don't think it works for all dates. Sometimes there's an odd day at the end of the year which is week 53 where I would only expect 52.....and some years it starts week 1 on the wrong date, e.g. the first Monday in April 2014 is April 7th but your formula returns 1 for Tuesday 1st April 2014.Originally Posted by Phuocam
You can use this formula to get every week with 7 days and week 1 starting on 1st Monday in April
=INT((A1-WEEKDAY(A1,2)-DATE(YEAR(A1+276 -WEEKDAY(A1,2))-1,4,7))/7)+2
This is an adaptation of my formula to give you ISO week numbers, as explained in this previous post
Audere est facere
Oops!Edit formula:
=NETWORKDAYS.INTL(DATE(YEAR(A1)-(NETWORKDAYS.INTL(DATE(YEAR(A1),4,1),A1,"0111111")<1),4,1),A1,"0111111")
Would someone help with another variation of this? Our fiscal year started on Oct 1, 2017, which would be week 1. However, each weeks runs Monday to Sunday and week 2 would start on October 9th. Thanks.
Hello cindyoh,
That sounds a bit odd to have week 1 as 8 days long, is there a general rule for how it works for any year? Does week 1 always start on 1st October, what would be the earliest/latest start dates for week 2?.......or do you only care about the next year?
It's odd because technically every FY runs from Oct 1 to Sept 30. Since we wouldn't be getting any gifts in on Sunday, Oct 1, we can say Oct 2 - 8 is week one.
Phuocam's formula in #14 seems to be working very well.
cindyoh, just replace the "4"in the formula with MONTH($A$1).
If you just count Mondays since start date that will give you the year number, so in this case that will be:
=NETWORKDAYS.INTL(DATE(2017,10,2),A1,"0111111")
Next financial year just change DATE(2017,10,2) to the week 1 start date for 2018
October 1 2017 is week 1 for a Fiscal year that always starts on Oct 1 regardless of the day of week. The normal work week starts on Monday and goes thru Sunday. So week 1 is on a Sunday for the date given above which happens to be the last day of the week. So for 2017 week 1 is 1 day long and week 2 starts on Oct 2nd Monday. The this would make Oct 9th week 3.
I am using this formula to find the work week in cell K2 of my spreadsheet.
=IF(AND(MONTH(K2)=10,DAY(K2)=1),1,ROUNDUP((K2-DATE(YEAR(K2)-IF(MONTH(K2)<10,1,0),10,1)+WEEKDAY(DATE(YEAR(K2)-IF(MONTH(K2)<10,1,0),10,1),2))/7,0))
the only issue I am having with this formula is the year 2024 then every 28th year after that which for some reason is giving me 54 weeks.
If someone has a solution to that problem that would make my day
You can get the same results with this formula
=NETWORKDAYS.INTL(DATE(YEAR(K2+92)-1,9,25),K2,"0111111")
.....and yes, this will sometimes gives the result 54, which is logical. When week 1 has only 1 day and the year has 366 days then there is also a 54th week - WEEKNUM function does exactly the same, e.g. =WEEKNUM("2028-12-31") = 54
If you don't want 54 what would you have instead? You can add a simple MIN function to limit to 53, e.g.
=MIN(NETWORKDAYS.INTL(DATE(YEAR(K2+92)-1,9,25),K2,"0111111"),53)
another way:
=WEEKNUM(K2+92,12)
I worked out that yes there should be a week 54 so I added a week 54 to my data set.
Your formulas above worked and are a very elegant daddylonglegs and deceptively simple! Phoucam. I would not have thought to use return type 12 Start on Tuesday when my week starts on Monday return type 2
So I found a way using a long formula again Maybe there is a Simpler way given the eye opening formulas shown above?
I show the Week Ending Date for a date provided in K2 using E3=K2+(7-WEEKDAY(K2,2))
Now I realize that September 30th will have most years Not Ending on a Sunday (my weeks are Monday thru Sunday)
I am using these :
E3=K2+(7-WEEKDAY(K2,2))
E4=E3-7
E6=DATE(YEAR(K2),9,30)
E7=IF(AND(Data!Q8<>0,IF(YEAR(K2)=2024,Data!BR8=0,Data!BQ8=0),E6>E4,E6<E3),EOMONTH(E3,-1),K2+(7-WEEKDAY(K2,2)))
Data!Q8 = Week1, BQ8 = week 53, BR8=Week 54 in my data set.Q8=0 means no data entered for that Fiscal week yet
Any ideas for a simplified solution?
2017/10/1 + 92 = 2018/1/1
92 mod 7 = 1
Start yyyy/10/1 =Mon like -> start (yyyy+1)/1/1 = Tue
Hi all,
after checking the post, i tried to start the week on 01/07/2018. However, it has 8 days as week 1.....is there any way to solve this?
Below are the formular I tried
=IF(NETWORKDAYS.INTL(DATE(YEAR(A13)-(TEXT(A13,"mdd")-1<0),7,1),A13,"0111111")=0,1,
NETWORKDAYS.INTL(DATE(YEAR(A13)-(TEXT(A13,"mdd")-1<0),7,1),A13,"0111111"))
=IF(NETWORKDAYS.INTL(DATE(2018,7,1),A9,"0111111")=0,1,
NETWORKDAYS.INTL(DATE(2018,7,1),A9,"0111111"))
thanks heaps
love this solution but I can't even follow it but thank you it works
"=WEEKNUM(K2+92,12)"
I am struggling to understand the logic behind the 92 value, please help?
Administrative Note:
Welcome to the forum.
We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.
Please see Forum Rule #4 about hijacking and start a new thread for your query.
If you are not familiar with how to start a new thread see the FAQ: How to start a new thread
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks