Trying to find nearest specific date from another date

Copper Contributor

I'm trying to create a formula where by I can find the nearest monday OR wednesday to a specific date i.e. 12/8/23 - any help?

6 Replies
Hi Sergei, this has helped; however, is there a way of adding two dates rather than one i.e. if the date is closer to a monday then it rounds to that date or if it's wednesday, then it will round to wednesday?


Sorry, I didn't catch/ "Nearest" you mean in past as well, not only in future? For example, if today is Tuesday, what shall be selected, Monday or Wednesday?

@Sergei Baklan 

Hi Sergei, so if today was Tuesday, I'd want it to round to the Monday. Similarly, if it was Thursday/Friday, I'd want it to be Wednesday. Hope this makes sense?

best response confirmed by Boughton1999 (Copper Contributor)


With a date in A2:






=LET(d, WEEKDAY(A2,13), A2+IF(d<=3,1,6)-d)

Hi Hans,

This has worked perfectly. Thanks ever so much!