Excel date

[Phil]

Trying to calculate the first day of the week for each
date in my sheet.

 

[Frank Kabel]

Hi
have a look at
http://www.cpearson.com/excel/DateTimeWS.htm#NthDoWYear

In your case if your date is stored in A1 try
=DATE(YEAR(A1),1,1+(1-(A1>=WEEKDAY(DATE(YEAR(A1),1,1))))*7)+A1-WEEKDAY(
DATE(YEAR(A1),1,1))
with Sunday = 1st day of a week

 

[Bob Phillips]

Phil,

This will return the Mon of the current week, so Sun 22nd returns Mon 23rd,
as does Mon 23rd, Tue 24th, etc.

=N1-WEEKDAY(N1,1)+2

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)