 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / October 2006Finding Friday in week number
Hello, I have a week number and am trying to find the date of the Friday in that week. The week number is selected in a drop down and used elsewhere so I can't change it but need to derive the date of the Friday in the selected week number. Can't find a way of doing this with my limited knowledge. Thanks for looking. David I found that the easiest way for me to do this quickly was to determine the date of the Friday in the first week. Assumes week starts on Sunday. First friday can potentially be in previous year if you are starting with Jan 1 as 1st day entered. But, works great for 2006 where 1/1/2006 was a Sunday. Determine the date for the first Friday, i.e. 1/6/2006 for 2006 Assume that date is in G2 and you enter the weeknumber you want in G1 Then this formula will calculate date of Friday in a weeknumber: =G$2+((G1-1)*7) If G1 = 1, date is 1/6/2006 G1 = 8, 2/24/2006 G1 -50, 12/15/2006 > Hello, I have a week number and am trying to find the date of the Friday in > that week. The week number is selected in a drop down and used elsewhere so > I can't change it but need to derive the date of the Friday in the selected > week number. Can't find a way of doing this with my limited knowledge. > Thanks for looking. > David =TODAY()+6-MOD(TODAY(),7)+(your_weeknum-WEEKNUM(TODAY()))*7 -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) > Hello, I have a week number and am trying to find the date of the Friday in > that week. The week number is selected in a drop down and used elsewhere so > I can't change it but need to derive the date of the Friday in the selected > week number. Can't find a way of doing this with my limited knowledge. > Thanks for looking. > David Much better solution! I'm saving that one. > =TODAY()+6-MOD(TODAY(),7)+(your_weeknum-WEEKNUM(TODAY()))*7 > [quoted text clipped - 15 lines] > > Thanks for looking. > > David Thank-you. Bob > Much better solution! I'm saving that one. > [quoted text clipped - 17 lines] > > > Thanks for looking. > > > David Forgot to mention that this solution does assume that you are using the MS definition of a week number. Post back if you want an ISO defined week number solution.  SignatureHTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) > =TODAY()+6-MOD(TODAY(),7)+(your_weeknum-WEEKNUM(TODAY()))*7 > [quoted text clipped - 15 lines] > > Thanks for looking. > > David |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.