If would like a function that rounds a date to the nearest Monday. Is there
such a function?

Ellen

On Wed, 17 Oct 2007 03:22:03 -0700, ellebelle
wrote:

If would like a function that rounds a date to the nearest Monday. Is there
such a function?

Ellen

=A1-WEEKDAY(A1)+2+7*(WEEKDAY(A1)5)

If I understand you correctly, up through Thursday, the nearest Monday is the
Monday before; after Thursday, the nearest Monday is the Monday following.

If you want the split to be on a different day, then just change the 5 to
something else.
--ron

One thought ..

Assuming source dates (real dates) running in A1 down
Put in B1:
=IF(A1="","",IF(WEEKDAY(A1,2)=1,A1,IF(ISNA(MATCH(W EEKDAY(A1,2),{2;3;4},0)),A1+VLOOKUP(WEEKDAY(A1,2), {5,3;6,2;7,1},2,0),A1+VLOOKUP(WEEKDAY(A1,2),{2,-1;3,-2;4,-3},2,0))))
Copy down as far as required. The expression "rounds down" Tues-Thurs to Mon
& "rounds up" Fri-Sun to the next Mon.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"ellebelle" wrote:
If would like a function that rounds a date to the nearest Monday. Is there
such a function?

Ellen

Thanks - this works brilliantly! I don't understand how it is doing it
though. Can you please tell me to use the same function to round to the
nearest Friday?

"Ron Rosenfeld" wrote:

On Wed, 17 Oct 2007 03:22:03 -0700, ellebelle
wrote:

If would like a function that rounds a date to the nearest Monday. Is there
such a function?

Ellen

=A1-WEEKDAY(A1)+2+7*(WEEKDAY(A1)5)

If I understand you correctly, up through Thursday, the nearest Monday is the
Monday before; after Thursday, the nearest Monday is the Monday following.

If you want the split to be on a different day, then just change the 5 to
something else.
--ron

On Wed, 17 Oct 2007 05:51:03 -0700, ellebelle
wrote:

Thanks - this works brilliantly! I don't understand how it is doing it
though. Can you please tell me to use the same function to round to the
nearest Friday?


=A1-WEEKDAY(A1)+6-7*(WEEKDAY(A1)<3)


The way this works is as follows:

A1-weekday(a1) always gives the Saturday prior to the date in A1.

That value + 6 will give the Next Friday.

Then we check the weekday of the original date, to decide if we want the next
Friday or the preceding Friday.

=============================================


--ron