+ Reply to Thread
Results 1 to 15 of 15

Formula help - Find date nearest to Monday

  1. #1
    Registered User
    Join Date
    11-11-2020
    Location
    Philadelphia
    MS-Off Ver
    Office 360
    Posts
    4

    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. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    7,654

    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. #3
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    17,739

    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. #4
    Registered User
    Join Date
    11-11-2020
    Location
    Philadelphia
    MS-Off Ver
    Office 360
    Posts
    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. #5
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    17,739

    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. #6
    Registered User
    Join Date
    11-11-2020
    Location
    Philadelphia
    MS-Off Ver
    Office 360
    Posts
    4

    Re: Formula help - Find date nearest to Monday

    Quote Originally Posted by 6StringJazzer View Post
    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. #7
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    17,739

    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. #8
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    17,739

    Re: Formula help - Find date nearest to Monday

    Quote Originally Posted by meegs View Post
    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. #9
    Registered User
    Join Date
    11-11-2020
    Location
    Philadelphia
    MS-Off Ver
    Office 360
    Posts
    4

    Re: Formula help - Find date nearest to Monday

    Quote Originally Posted by 6StringJazzer View Post
    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. #10
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    7,654

    Re: Formula help - Find date nearest to Monday

    With B1 is real date
    Follow this logic:
    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. #11
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    7,654

    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. #12
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    5,670

    Re: Formula help - Find date nearest to Monday

    Quote Originally Posted by meegs View Post

    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. #13
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    7,654

    Re: Formula help - Find date nearest to Monday

    Quote Originally Posted by Bo_Ry View Post
    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. #14
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    5,670

    Re: Formula help - Find date nearest to Monday

    Thanks.

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

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

  15. #15
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2010
    Posts
    5,838

    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
    Attached Files Attached Files
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Find the nearest date in a range of dates formula (Google sheets)
    By kaytoc in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 1
    Last Post: 10-20-2017, 05:20 PM
  2. Find the nearest date in a range of dates formula
    By kaytoc in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 10
    Last Post: 10-20-2017, 04:51 PM
  3. Find the nearest date in a range of dates formula
    By kaytoc in forum Excel General
    Replies: 9
    Last Post: 10-20-2017, 03:24 PM
  4. Roundingd ate to nearest Monday
    By barneyrubble1965 in forum Excel General
    Replies: 3
    Last Post: 05-17-2017, 02:12 AM
  5. [SOLVED] Calculate future date to nearest Monday
    By mswauger in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-09-2013, 04:13 PM
  6. [SOLVED] Find monday preceeding given-date unless date is a monday
    By welchs101 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-10-2012, 07:42 AM
  7. Nearest Monday
    By luvthavodka in forum Excel General
    Replies: 5
    Last Post: 07-21-2010, 06:13 PM

Bookmarks

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