Can anyone teach me how to use formula in Excel 2007 to find out the starting (beginning from Monday) and ending (ending on Saturday) dates based on week number? eg. week 32 of 2012 is from 06th August 2012 to 11th August 2012.
1 Answer
Yes:
=DATE(2012,1,5*7-2)-WEEKDAY(DATE(2012,1,3))
where the 5 in the above formula is the week number
So, in CELL A1, type
=DATE(2012,1,1*7-2)-WEEKDAY(DATE(2012,1,3))
and in CELL A2 type
=DATE(2012,1,1*7+3)-WEEKDAY(DATE(2012,1,3))
You can also use cell references in the DATE() function which may help.
Just for completeness, you can get the week number by:
=WEEKNUM(DATE(2012,8,1))
EDIT
This seems even quicker:
From date:
=DATE(2012,1,32*7-5)
To date:
=DATE(2012,1,32*7)
