rounding dates

[Guest]

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

Ellen

 

[Ron Rosenfeld]

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

 

[Guest]

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.

 

[Guest]

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]

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