Formula for closest monday to a particular date

Hi Gurus

I have a working solution for this, but was wondering if there's a more elegant way to achieve this. For any given date I want to be able to find the nearest Monday. So if the date given is a Monday already then it will return the date given. If the date given is a Tuesday it will return the day before the given date, etc.

I won't give my solution at this stage, so it doesn't influence your replies, but will happily share it later.

Thanks.

Formula:

Please Login or Register  to view this content.

Originally Posted by Phuocam

Try:

=A1-WEEKDAY(A1,2)+1

=A1-MOD(A1-2,7)

=WORKDAY.INTL(A1+1,-1,"0111111")

Thanks for the suggestions, however these do not work. If the date in question is a Friday these all return the previous Monday, not the next Monday, which is closer than the previous Monday. E.g. for 31 August 2018 they return 27 August 2018, which is 4 days away from 31 August, but I need it to return 3 September 2018, which is only 3 days away.

Originally Posted by mehmetcik

Formula:

Please Login or Register  to view this content.

Thank you, that works, but I'm interested to see what others come up with too.

Originally Posted by Phuocam

=WORKDAY.INTl(A1+4,-1,"0111111")

Thanks. I haven't used the WORKDAY.INTL function much, so have just read up on it. I like that solution.

The method I used was:

=A1+4-WEEKDAY(A1,15)