Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding a specific date from a week no.
Is there a formula to find a specific date from a week no?
For example Week 42 day 2 (Monday) would result 16/10/06 Regards |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding a specific date from a week no.
"TheRook" schreef in bericht ... Is there a formula to find a specific date from a week no? For example Week 42 day 2 (Monday) would result 16/10/06 Regards Put weeknumber 42 in Cel A2 and daynumber 2 in Cel B2, then try this one. =DATE(2006;12;31)-(365-(((365/52)*A2)-(IF((7-B2)0;7-B2;B2-7)))) Not sure, I only tested 10/16/06, 01/01/01 and 12/31/06 with a positive result. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding a specific date from a week no.
correction, pasted the wrong one
=DATE(2006;12;31)-(365-(((365/52)*A2)-(IF((7-B2)0;7-B2;31-B2)))) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding a specific date from a week no.
A5: year
B5: week No C5: day of week =DATE(A5,1,1)+(B5-1)*7+C5 Regards, Stefi €˛moon€¯ ezt Ć*rta: correction, pasted the wrong one =DATE(2006;12;31)-(365-(((365/52)*A2)-(IF((7-B2)0;7-B2;31-B2)))) |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding a specific date from a week no.
Hi
With A1 holding the first Monday of the Year required e.g 02 Jan 2006 and A2 holding the week number then =A1+(A2-1)*7 -- Regards Roger Govier "TheRook" wrote in message ... Is there a formula to find a specific date from a week no? For example Week 42 day 2 (Monday) would result 16/10/06 Regards |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding a specific date from a week no.
<the first Monday of the Year required
Which is: =DATE(Yr,1,1+(Nth-(Dow=WEEKDAY(DATE(Yr,1,1))))*7)+ Dow-WEEKDAY(DATE(Yr,1,1)) Where Dow = 2. From Chip Pearson: http://www.cpearson.com/excel/DateTi...tm#LastWeekday -- Kind regards, Niek Otten Microsoft MVP - Excel "Roger Govier" wrote in message ... | Hi | | With A1 holding the first Monday of the Year required e.g 02 Jan 2006 | and A2 holding the week number then | =A1+(A2-1)*7 | | -- | Regards | | Roger Govier | | | "TheRook" wrote in message | ... | Is there a formula to find a specific date from a week no? | | For example Week 42 day 2 (Monday) would result 16/10/06 | | Regards | | |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding the week of date | Excel Discussion (Misc queries) | |||
Finding the next dayof the week after a certain date. | Excel Worksheet Functions | |||
Date Function formula that will return the date of a specific week | Excel Worksheet Functions | |||
Finding the Monday date based on a different date in same week | Excel Worksheet Functions | |||
Finding week from date | Excel Programming |