Hi Jules

I completely misread your mail, I thought you wanted the weeknumber, not the
starting day of the week.

Ron has posted you the correct solution.

Regards

Roger Govier


Roger Govier wrote:
Hi Jules

Just format the cell with the formula as General.
It is picking up the date format and turning the result of 39 into the
date 39 days after the 31/12/1899 which is the base date from which
Excel stores its dates as a number.

Regards

Roger Govier


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