Excel - rounding dates

Asked By ellebell on 17-Oct-07 06:22 AM
If would like a function that rounds a date to the nearest Monday. Is there
such a function?

Ellen




Ron Rosenfeld replied on 17-Oct-07 06:37 AM
On Wed, 17 Oct 2007 03:22:03 -0700, ellebelle


=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
demechani replied on 17-Oct-07 06:42 AM
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(WEEKDAY(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
---
ellebell replied on 17-Oct-07 08:51 AM
Thanks - this works brilliantly! I do not 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 replied on 17-Oct-07 09:33 AM
On Wed, 17 Oct 2007 05:51:03 -0700, ellebelle



=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