Formula help - Find date nearest to Monday

Hello,

I'm a bit new to excel and not entirely sure how to ask this, but I need a formula to give me the date for the closest Monday to January 26th.

For example, based on the year these are the returning dates I need:
2015 - Jan 26
2016 - Jan 25
2017 - Jan 30
2018 - Jan 29
2019 - Jan 26
2020 - Jan 26
2021 - Jan 25
2022 - Jan 24
2023 - Jan 30

This is a little confusing but I need the formula to pull from a cell that contains the year. If Jan 26th of that year does not fall on a Monday, than I need the formula to give me the date that falls closest to that Monday. I hope this makes sense, any help would be appreciated!

Thank you,

In 2017, 1/26 falls on a Thursday. The closest Monday is 1/23 (3 days earlier) but you show 1/30 (4 days later). Can you please explain your logic for "closest"?

It should be split into 2 cells and real date formatted. One cell will have the year and another cell will have the date, i.e.

A1: Year
A2: =DATE(A1,1,26)

So cell A2 should always give me 1/26/"A1: Year"

From there, I need a formula that gives me the closest Monday to cell A2.

This formula gives the closest Monday (in whichever direction is closest) where the year is in column A.

=DATE(A1,1,26)-IF(WEEKDAY(DATE(A1,1,26),2)=1,0,IF(WEEKDAY(DATE(A1,1,26),2)<=4,WEEKDAY(DATE(A1,1,26),2)-1,8-WEEKDAY(DATE(A1,1,26),2)))

This formula can be made more compact by putting

WEEKDAY(DATE(A1,1,26),2)

in a helper column, since it is repeated three times

Originally Posted by 6StringJazzer

In 2017, 1/26 falls on a Thursday. The closest Monday is 1/23 (3 days earlier) but you show 1/30 (4 days later). Can you please explain your logic for "closest"?

I'm creating a calendar that needs to account for holidays and these are just the days this one (Duarte's Day) is observed on.

You have several errors in your sample data. Please revisit to make sure I have solved the right problem.

Please Login or Register  to view this content.

Originally Posted by meegs

I'm creating a calendar that needs to account for holidays and these are just the days this one (Duarte's Day) is observed on.

But you didn't address my point. In 2017 you did not show the closest Monday, you showed the following Monday. What do you actually need?

Originally Posted by 6StringJazzer

But you didn't address my point. In 2017 you did not show the closest Monday, you showed the following Monday. What do you actually need?

Apologies for the confusion, I'm using data from a public holidays website and this holiday follows a very weird pattern. It looks it should only fall on a Sat, Sun, or Mon.

So if the 26th falls on a Thurs (2017), it will be observed on that following Mon the 30th
If it falls on a Fri (2018), it is observed Mon the 29th
If it falls on a Sat (2019), it is observed Sat the 26th
If it falls on a Sun (2020), it is observed Sun the 26th
If it falls on a Mon (2015), it is observed Mon the 26th
If it falls on a Tues (2021), it is observed Mon the 25th

And so on...