SOLVED

# Trying to find nearest specific date from another date

Copper Contributor

# Trying to find nearest specific date from another date

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

# Re: Trying to find nearest specific date from another date

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?

# Re: Trying to find nearest specific date from another date

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?

# Re: Trying to find nearest specific date from another date

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)
Solution

# Re: Trying to find nearest specific date from another date

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)

# Re: Trying to find nearest specific date from another date

Hi Hans,

This has worked perfectly. Thanks ever so much!
1 best response

Accepted Solutions
best response confirmed by Boughton1999 (Copper Contributor)
Solution

# Re: Trying to find nearest specific date from another date

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)