• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Challenge on finding out a date base on Weeknum and Weekday

F

fred

Member
My boss popped a question and ask me for the dates of Friday on the 33rd week and the 49th week in 2012.


I browse around the blog but all subjects were on dates converting into something else. You know, we start with a date then fidn out the week number or weekday using weeknum() and weekday(). But this is going forward. But there doesn't seem to be an ariticle to go the other way around. I'm also antipating that my boss may ask the Friday of Xth week in 2013 in the future. I can't use goal seek because it'll turn everything back to year 1900. And I'd not want to do it by trial and error. Can Excel formula even do this?


Please kindly advise. thanks!
 
Find the first Friday of 2012, which is Jan 6th.

Type that in excel. Below that enter =cellabove + 7. Then drag down how ever many times u need. I guess 32 and 48 in your case.
 
Thanks Montrey. I know I can do that which I'd consider as a "long form" type of solution. I was just thinking using the year, the weekday and the week number to deduce the exact date.


don't worry about it. i guess there is no short form solution. I was just trying to test my mind and others to see if we can come up with something new. :)
 
Hi, fred!


Here we come again to the issue of how to count the weeks of a year. There are many methods to count them (starting on Monday, Sunday, first is that with the first Thursday... and depending on Excel version, you can have a bunch of more options).


So I'd face the problem firstly defining which method for numbering a week you'll use. Then the rest of the stuff is easy.


Give a look at this topic where it's been discussed:

http://chandoo.org/forums/topic/adding-week-numbers-in-different-years


Regards!
 
Havn't tried Hui's solution because ima about to go to bed. But if it works then /bow


:). You the man!
 
Hi Hui ,


Please check your formula to see whether it takes care of leap years.


I have come up with the following ; please check if it works for all dates :

[pre]
Code: 
=DATE(Year,1,MATCH(Week_number,WEEKNUM(DATE(Year,1,1)+ROW(INDIRECT("1:366"))-1),0))+MATCH(Week_day,{"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday"},0)-1
[/pre]
Year is a number ; Week_number is also a number , Week_day is text.


Narayan
 
As SirJB7 said, I think we first need to know how you want to define the first week? If week 1 starts on Jan 1, things are very easy. If it's the week that contains Jan 1, a little tricker. If it's the first full week of the year, a different problem.


Once we know which of the 3 is the correct definition, we'll be able to come up with a better solution.
 
Thanks, Luke. you have brought out a great point. Yeah, I do realize that how we treat 1/1/2012 would make a big difference. I found out on Excel that January 1, 2011 (not 2012) is week 1 but Jan 2, 2011 is week 2. That said, some manufacturing plants would start their fiscal years on a monday. They call it the "manufacturing calendar". So for that reason Jan 3 to Jan 9 would be week 1.


Thank you very much for all the input and I can go from here. I'll check with finance dept and see how they see things.
 
Hi, fred!


Give a look at this:


-----

[pre]
Code: 
2011	vie 07/01/2011
2012	vie 06/01/2012
2013	vie 04/01/2013
2014	vie 03/01/2014
2015	vie 02/01/2015
2016	vie 01/01/2016
2017	vie 06/01/2017
[/pre]
-----


The formula atr B column for first Friday of year is:

=FECHA(A1;1;1)+SI(DIASEM(FECHA(A1;1;1);2)>5;7;0)+5-DIASEM(FECHA(A1;1;1);2) -----> in english: =DATE(A1,1,1)+IF(WEEKDAY(DATE(A1,1,1),2)>5,7,0)+5-WEEKDAY(DATE(A1,1,1),2)


That's for weeks starting on Monday (1-7 thru Sunday). Now you can add the number of weeks needed.


Regards!
 
You must log in or register to reply here.
Back
Top