getting the date from a week number

[kallie]

Hi
I have a timesheet which i use for every year which is based on Wee
Numbers.
The problem is at the end of every year I have to MANUALLY redate ver
day.
Since the Week Numbers & Week days never change & it is easy to chang
the year I want to code it in such away that it can automatically fin
the date by using a Week Number,Year & Week day.
Please can anyone help. I must have spent days trying to solve thi
problem but no luck.
I have attached the timesheet.
Thank

Attachment filename: 2004dttimesheettmp.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=40987

 

[Frank Kabel]

Hi

your problem is not so simple as it sounds for the following reasons:
1. Different definitions of weeknumbers exist (e.g., ISO, Excel
weeknumber, etc.) Have a look at
http://www.cpearson.com/excel/weeknum.htm for more information.
2. With this information you're able to determine on which day a week
starts and what is the first day of the first week.

For the ISO definition the followinf formula will return the monday for
your weeknumber
=7*A1+DATE(B1,1,3)-WEEKDAY(DATE(B1,1,3))-5

Where A1 stores the weeknumber and B1 the year. For all other days
simple add the approbiate number.
For more information I'd suggest to search Google for the topic
'weeknumber'.

HTH
Frank