LinkBack URL
About LinkBacks
I have to extract the monday's date, when choosing year and week in the two dropdowns, could anyone help?
https://www.excelforum.com/attachmen...1&d=1501135262
I have to extract the monday's date, when choosing year and week in the two dropdowns, could anyone help?
https://www.excelforum.com/attachmen...1&d=1501135262
How are you defining your week numbers?
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. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
I'm simply choosing? 1 to 53 from the dropdown
I choose a new week every week - im using it to index/match in a bigger table and extracting data corresponding to the date I'm trying to extract.
No, sorry, that's not what I meant! Are you using week numbers as Excel defines them, are you simply starting with 01/01 each year as the first day of the week, or what?
I might not understand
My year and week, are simply written numbers 2017 -> 2020 and 1 -> 53
From the week I choose, I wanna extract the date of monday. I've tried this, but something is of
=MAX(DATE(C2,1,1),DATE(C2,1,1)-WEEKDAY(DATE(C2,1,1),2)+(C3-1)*7+1)
I think my problem is that Week 1 doesn't always start on 01-01 every year
Last edited by HereComesTheBoom; 07-27-2017 at 02:37 AM.
I don't think you do understand my question, but maybe I haven't phrased it well! Have a read of this and then let me know how YOU are defining your week numbers: https://exceljet.net/excel-functions...eknum-function
Just seen your edit - yes, that's precisely what I mean!
Last edited by AliGW; 07-27-2017 at 02:40 AM.
I think I get what you mean, but I don't have dates to define the weeks. I'm trying to do it the other way around.
I choose year and week, and want the date of the monday in that week. It doesn't solve it even if the week is written as =WEEKNUM(1) in the validationslist.
It still shows that monday 2017 would be 01-01, but monday in that week was 02-01
OK - so what do you WANT it to show? The thing is that you will get overlapping with the week numbers, which means that from Monday to Friday most years there will be a mixture of, for example, week 1 and week 2.
I want to choose YEAR, and WEEK, and then just extract the date of MONDAY in that given week - I'll sort the overlapping weeks by just +1 in the cells showing tue->fri
you may try this
Sorry for off-topic interjection:
Your attachment is not working, and in any case, although there is no official rule regarding this behaviour, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.
I'm sure you understand, and we look forward to seeing you post your formulas/macros in your posts for the searching benefit of all.
Thanks again for all your hard work here!
The link is invalidOriginally Posted by teireii
OK, so we can get rid of the week numbers in the column on the left for now - yes?
This works, but only in 2017 - when choosing 2018, week 1 gets monday as 08-01
We sure can - I'm only using the numbers in C2 and C3 (they are just showing the C3)
I was able to open the file without a problem?The link is invalid
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
still thinking,,,,,
week 1 of every year may not be on Jan.
He updated it
It's opening now - must have been a glitch. The formulae in it don't work at all, though - it's producing very odd results.
First week (week 1) is always placed in jan, there's occasions where 53 is partly in jan though - I know it might not be the FIRST week though, as there can be days from week 53 in jan. But that doesn't explain 2018, where week 1, day 1 is exactly Monday 01-01
C6=DATE(C$2,1,1)+(A6-1)*7-WEEKDAY(DATE(C$2,1,1))+MATCH(C6,{"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday"},0)
Try this and copy towards down
Samba
Say thanks to those who have helped you by clicking Add Reputation star.
can you try this? i tested again, it works.
Formula:
Please Login or Register to view this content.
Thanks Siva, but that doesn't give the desired, sadly.
Yeah it does, when in 2017 - try picking 2018 week 1 - that would be 01-01 - but yours is giving me 08-01 (and it's text, so I cant use it for looking up the date in the table, which is date-formatted.)
What would be your expected result in your example (attached file)
I need mondays DATE, when choosing year and week in C2 and C3
Try this:
=IF(VALUE(TEXT(C$2,"d"))>9,DATE(C$2,1,1)-7,DATE(C$2,1,1))+CHOOSE((WEEKDAY(DATE(C$2,1,1),2)),1,0,-1,-2,-3,-6,-7)+(ROWS(B$6:B6)-1)+(7*C$3)-1
Last edited by AliGW; 07-27-2017 at 04:33 AM.
I updated my last post - hope you saw it. The formula does what you want, I think, and the output is in date format.
but attached is fine...
Formula:Please Login or Register to view this content.
the logis is,,
1) search which day of the week is for the 1 Jan of selected year
2) the day difference from Monday to previous week of that day
i.e: 1/1/2020 is Wednesday (3)
previous week Wednesday is 12/25/2019
3) find out total day difference
wednesday (3) - Monday (1) = 2 days difference + 7 days (1 week)
3) dependent on the weeknum selected to multiply 7 days
4) reformat it to mm-dd-yyyy
Last edited by teireii; 07-27-2017 at 05:30 AM.
No, it isn't! Change the week number selector to 1 and you are getting a value error.
so weird.....but it works just fine in my computer...Capture.JPG
BTW, is my logis below correct?
=
1) search which day of the week is for the 1 Jan of selected year
2) the day difference from Monday to previous week of that day
i.e: 1/1/2020 is Wednesday (3)
previous week Wednesday is 12/25/2019
3) find out total day difference
wednesday (3) - Monday (1) = 2 days difference + 7 days (1 week)
3) dependent on the weeknum selected to multiply 7 days
4) reformat it to mm-dd-yyyy
The logic, as I have interpreted it, is simply that when week 1 is selected, it must include the 1st of the month, which may or may not be a Monday. Once that has been established, you can work out the rest. Try my formula in B6 copied down, and then practise changing the year and week number.
yeah, that's what i understand as well... but what to do with the first if VALUE(TEXT(C$2,"d"))>9??
just a suggestion, isn't the week number change to a dependent drop down box is better?
That checks the day that the first of January falls on in the chosen year. I played with this a bit to get the right number, but if it's bigger than 9, it means that the week will need to start seven days earlier: this stops the problem of week one not containing the first. So we don't get 08/01 as the first item on the list - instead, we get 01/01. Hope that makes sense!
Dependent drop-down - not my call!!! Ask the OP.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks