Trying to find nearest specific date from another date

[Boughton1999]

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?

[#2]

@Boughton1999 

Please check Get next day of week - Excel formula | Exceljet

[#3]

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?

[#4]

@Boughton1999 

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?

[#5]

@SergeiBaklan 

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?

[#6 · Accepted solution]

@Boughton1999 

With a date in A2:

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

or

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

[#7]

Hi Hans,

This has worked perfectly. Thanks ever so much!