Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Finding 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 |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Finding Friday in week number
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 "DavidS" wrote: 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 |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Finding Friday in week number
=TODAY()+6-MOD(TODAY(),7)+(your_weeknum-WEEKNUM(TODAY()))*7
-- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "DavidS" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Finding Friday in week number
Much better solution! I'm saving that one.
"Bob Phillips" wrote: =TODAY()+6-MOD(TODAY(),7)+(your_weeknum-WEEKNUM(TODAY()))*7 -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "DavidS" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Finding Friday in week number
Thank-you.
Bob "JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message ... Much better solution! I'm saving that one. "Bob Phillips" wrote: =TODAY()+6-MOD(TODAY(),7)+(your_weeknum-WEEKNUM(TODAY()))*7 -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "DavidS" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Finding Friday in week number
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. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Bob Phillips" wrote in message ... =TODAY()+6-MOD(TODAY(),7)+(your_weeknum-WEEKNUM(TODAY()))*7 -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "DavidS" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Retrieve multiple dates, from a week number? | New Users to Excel | |||
Pulling Data off Web - Need Function Help | Excel Worksheet Functions | |||
Show week number in current month | Excel Discussion (Misc queries) | |||
Return SEARCHED Column Number of Numeric Label and Value | Excel Worksheet Functions | |||
Sigma Function? | Excel Worksheet Functions |