On Wed, 28 Sep 2005 05:13:11 -0500, JulesM
wrote:


Hi All

Can anyone help me with a formula to calculate the Week Commencing date
based on a date supplied?

I found this:
http://www.bygsoftware.com/Excel/fun.../iso_dates.htm

and tried:

=1+INT((A1-DATE(YEAR(A1+4-WEEKDAY(A1,2)),1,5)+WEEKDAY(DATE(YEAR(A1+4-WEEKDAY(A1,2)),1,4),2))/7)

Using a date in A1 of "28-Sept-05" (formatted) but this returns a
completely wrong date "08/02/1900"

Am I doing something wrong?

Excel Version 2000

Any help appreciated. Thanks
Jules

And a more general formula would be:

=A1+1-WEEKDAY(A1+8-DOW)

Where DOW = Day of Week (Sun=1; Mon=2; etc)


--ron