# Roundingd ate to nearest Monday

• 05-17-2017, 12:31 AM
barneyrubble1965
Roundingd ate to nearest Monday
I'm trying to round a date that an animal calves to the nearest Monday. Therefore in say cell A2 I will have the calving date and I want cell B2 to show the nearest Monday to that date. It may be the previous Monday if animal calves on a Tuesday, Wednesday of Thursday. Or the following Monday if animal calves on a Friday, Saturday or Sunday. If animal calves on a Monday then that date will be the same in B2. Hope that makes sense and any help would be appreciated.
• 05-17-2017, 12:54 AM
avk
Re: Roundingd ate to nearest Monday
formula in "B2"
Formula:
`=A2+7-WEEKDAY(A2+7-2)`

Both format as : [\$-F800]dddd, mmmm dd, yyyy
• 05-17-2017, 01:02 AM
AliGW
Re: Roundingd ate to nearest Monday
There may be more elegant ways, but this will work with the calving date in A1:

=IF(WEEKDAY(A1)=2,A1,IF(AND(WEEKDAY(A1)>1,WEEKDAY(A1)<6),A1-WEEKDAY(A1)+2,A1+(7-WEEKDAY(A1))))
• 05-17-2017, 02:12 AM
Phuocam
Re: Roundingd ate to nearest Monday
Or:

=WORKDAY.INTL(A2-4,1,"0111111")