I'm racking my brain trying to figure out a formula I could use to display the Week Range (Ex. 01/05/16 - 07/05/16) given the (Week Number (1,2,3,4,5 etc..), the month and year. Can anyone help me with this?
I'm racking my brain trying to figure out a formula I could use to display the Week Range (Ex. 01/05/16 - 07/05/16) given the (Week Number (1,2,3,4,5 etc..), the month and year. Can anyone help me with this?
weeknum should give you want you want
https://exceljet.net/formula/get-week-number-from-date
but would also depend on what you classify as first week and also what day of the week you classify as the first day of the week
ie is the 1st of Jan this year considered week 1 even though it started on Friday or week 53 of last year?
making 1st week of this year start of 3rd
^this is also assuming your week starts on a sunday
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
Attached is a workbook with a formula in E2 that takes the week number (within a month), month and year and determines the date range (ending on Saturday) for the week number specified.
The formula doesn't recognize the number of days in a month, so the week range for the 4th or 5th week in a month may show a date that is past the number of days in a month. To get those specifically defined, I think you would need some VBA.
Hope this provides some direction,
Dan
Thank you so much for the replies!
But I dont think I was clear enough with my question.
So lets say in a cell I have the month, in another cell I have the year and in another cell I have the week number, how can I calculate the date range based on this information?
When I say weeknumber, I mean specific to the month, so week 1 is the first week of the month listed above, week 2 is the second week of the month listed above, etc... not the week number for the year.
Does this make sense? :x
pls attach the sample file
Samba
Say thanks to those who have helped you by clicking Add Reputation star.
I dont have a sample file. I dont even know how to start the code
Put week number in cell B1, Month in cell B2, and year in B3.
For result use this formula-
Check attached for examplePlease Login or Register to view this content.
excelforum-163.xlsx
Happy to Help
How to upload excel workbooks at this forum - http://www.excelforum.com/the-water-...his-forum.html
"I don't get things easily, so please be precise and elaborate"
If someone's post has helped you, thank by clicking on "Add Reputation" below the post.
If your query is resolved please mark the thread as "Solved" from the "Thread Tools" above.
Sourabh
Thats perfect!!!
How do I make it though so that the first week of the month is the week where the first of the month is in? (So the first of the month, that sun-sat week is the first of the week)
So for example, the first week of June 2016 would display May 29th to June 4th (sun-sat)?
try above formula in "sourabhg98" attached filePlease Login or Register to view this content.
try above formula in "sourabhg98" attached filePlease Login or Register to view this content.
See attached.
Based on 1st of month, column G gives the Sunday of week containing first of month (Week 1).
=F2-WEEKDAY(F2,1)+1
For Week 1
in H2
=TEXT($G2+(H$1-1)*7,"dd/mm/yy") &"-"& TEXT($G2+(H$1-1)*7+6,"dd/mm/yy")
Copy across and down
Last edited by JohnTopley; 05-06-2016 at 05:12 AM.
Thank you so much, but Im looking for the exact set up that "sourabhg98" did, and I put in 'nflsales' edit, but it didnt work fully. For example, week 1, month 5 and year 2016 give me the date range as April 24-April 30 which is not the first week of may, its the last week of april
See attached...
Yes!!! Exactly what Im looking for!! :D
Last question, how do I make it so that the month cell equals whatever number value is in another cell (J3)? It messes up when I put =J3 for example.
If you are referring to my table ..
For example, week 1, month 5 and year 2016 give me the date range as April 24-April 30 which is not the first week of may, its the last week of april
Week 1 of May is given as 01/05/2016-07/05/2016.
See attached which uses the table I created.
What do you want (as is the usual case) when Week 5 (or 6) for month n is Week 1 of month n+1?
Last edited by JohnTopley; 05-07-2016 at 06:08 AM.
Thank you so so much for all of the help!!!! Amazing experience on here!! :D Thanks Guys!, I got it figured out based on your work :D
Could you please mark the thread as solved ("Thread Tools" at top of first post).
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks