F Frank Kabel Feb 24, 2004 #2 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
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
B Bob Phillips Feb 24, 2004 #3 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)
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)
