Rouding Dates to beginning of a week
 

I have previously been using weeknum function to produce reports in week
number order. Users now wish to see the actual start date of each week. Is
there a standard function to do this or do I need to create an algorithm?

Regards

Assuming the date is in A1, and the week starts on a Monday, then

=A1+CHOOSE(WEEKDAY(A1),1,0,-1,-2,-3,-4,-5)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Peter W" wrote in message
...
I have previously been using weeknum function to produce reports in week
number order. Users now wish to see the actual start date of each week. Is
there a standard function to do this or do I need to create an algorithm?

Regards

Peter,

Assuming the actual start date of each week is Monday and you are not using
the 1904 date system then:

With any date within the the target week in A1

=A1-WEEKDAY(A1,3) will give the Monday start date. If the starting date is
a Sunday then it will give the previous Monday. If you want a Sunday to
show the next Monday, (ie the next day) then use:

=A1-WEEKDAY(A1,1)+2


HTH

Sandy

--
to e-mail direct replace @mailinator.com with @tiscali.co.uk


"Peter W" wrote in message
...
I have previously been using weeknum function to produce reports in week
number order. Users now wish to see the actual start date of each week. Is
there a standard function to do this or do I need to create an algorithm?

Regards

Bob

Many thanks this works really, really well!!!!!

"Bob Phillips" wrote:

Assuming the date is in A1, and the week starts on a Monday, then

=A1+CHOOSE(WEEKDAY(A1),1,0,-1,-2,-3,-4,-5)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Peter W" wrote in message
...
I have previously been using weeknum function to produce reports in week
number order. Users now wish to see the actual start date of each week. Is
there a standard function to do this or do I need to create an algorithm?

Regards