# Formula help - Find date nearest to Monday

1. ## 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,

2. ## Re: Formula help - Find date nearest to Monday

2015 - Jan 26
is it in single cell or splitted into 2 cells : "2015" and "Jan 26"?
Is it date-text or real date formatted?

3. ## Re: Formula help - Find date nearest to Monday

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"?

4. ## Re: Formula help - Find date nearest to Monday

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.

5. ## Re: Formula help - Find date nearest to Monday

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

6. ## Re: Formula help - Find date nearest to Monday

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.

7. ## Re: Formula help - Find date nearest to Monday

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.``

8. ## Re: Formula help - Find date nearest to Monday

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?

9. ## Re: Formula help - Find date nearest to Monday

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...

10. ## Re: Formula help - Find date nearest to Monday

With B1 is real date
If B1 is Sun, add 1 day
...Mon.......0 day
...Tue ..... -1 day
...Wed...... -2 days
...Thu....-3 days
...Fri..... +3 days
...Sat..... + 2 days
...Sun.....+ 1 day

Formula in C1

``Please Login or Register  to view this content.``

11. ## Re: Formula help - Find date nearest to Monday

#10 is based on Mon
With #9, based on Sat, Sun and Mon:

``Please Login or Register  to view this content.``

12. ## Re: Formula help - Find date nearest to Monday

Originally Posted by meegs

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.
B2
=A2+4-weekday(A2,15)

13. ## Re: Formula help - Find date nearest to Monday

Originally Posted by Bo_Ry
B2
=A2+4-weekday(A2,15)
Very neat to nearest Mon.
Any way, could you adapt it to "Mon, Sat, Sun" does not change, but others to nearest Mon?

14. ## Re: Formula help - Find date nearest to Monday

Thanks.

"Mon, Sat, Sun" does not change, but others to the nearest Mon.

=A2+(4-WEEKDAY(A2,15))*(WEEKDAY(A2,2)<6)

15. ## Re: Formula help - Find date nearest to Monday

In C2 then copy down. Format cell for date.

=AGGREGATE(15,6,ROW(INDIRECT(B2-3&":"&B2+3))/(WEEKDAY(ROW(INDIRECT(B2-3&":"&B2+3)),2)=1),1)
.
Jan 30 has 4 days difference

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1