+ Reply to Thread
Results 1 to 11 of 11

Nearest corresponding day

  1. #1
    Forum Contributor
    Join Date
    12-02-2008
    Location
    Brisbane
    MS-Off Ver
    2016
    Posts
    801

    Nearest corresponding day

    The 15th December 2019 was a Sunday. How do I find the 'nearest' corresponding Sunday in 2018 which, in this example, was 16th December?

  2. #2
    Forum Guru Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Nearest corresponding day

    Try with your date in A1

    =EDATE(A1,-12)+1


    If you want this to work for any year use
    =EDATE(A1,-12)+1+(MOD(YEAR(A1),4)=0)
    Last edited by Ace_XL; 01-01-2020 at 06:31 PM.
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  3. #3
    Forum Contributor
    Join Date
    12-02-2008
    Location
    Brisbane
    MS-Off Ver
    2016
    Posts
    801

    Re: Nearest corresponding day

    Ace indeed! Thanks, I never knew edate existed.

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,194

    Re: Nearest corresponding day

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  5. #5
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    3,009

    Re: Nearest corresponding day

    I am not quite sure the formula will work for the first 2 months of a leap year as the MOD(YEAR(A1),4) does not apply
    will think about tweaking, perhaps


    =EDATE(A1,-12)+1+AND(MOD(YEAR(A1),4)=0,MONTH(A1)>2)+AND(MOD(YEAR(A1),4)=1,MONTH(A1)<3)
    Last edited by davsth; 01-02-2020 at 04:37 AM.

  6. #6
    Registered User
    Join Date
    01-02-2020
    Location
    Munich, Germany
    MS-Off Ver
    Office 365
    Posts
    9

    Re: Nearest corresponding day

    Hi,

    =A1-364




    Greetings


    Christian

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,194

    Re: Nearest corresponding day

    Christian - welcome to the forum.

    Your solution won't work for a leap year.

  8. #8
    Registered User
    Join Date
    01-02-2020
    Location
    Munich, Germany
    MS-Off Ver
    Office 365
    Posts
    9

    Re: Nearest corresponding day

    Why not?
    If I subtract 364 (52*7) days from a date, i will always get the same Weekday, if there is a leap year or not.


    Greetings


    Christian

  9. #9
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    3,009

    Re: Nearest corresponding day

    I am inclined to agree and think I reached this conclusion when asked a similar question last year. I agree with the logic

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,194

    Re: Nearest corresponding day

    Yes, I suppose you will - guess I was over-thinking it!

  11. #11
    Forum Guru Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Nearest corresponding day

    @ BrisbaneBob
    A note of caution

    The only time Besserwiser's won't work is for the date Dec 31 (any year) and Dec 30 (in leap years with Besserwiser's solution only) as they would give a date in the same year

    Can you live with that? Else you can always build in an IF statement to handle that.
    Last edited by Ace_XL; 01-02-2020 at 11:16 AM.

+ 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. Round to nearest 15 then nearest hour if less than 1 hour
    By BLOUNTFIRE in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-08-2014, 12:22 AM
  2. [SOLVED] nearest value
    By wayneg in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 03-18-2013, 03:38 AM
  3. [SOLVED] Get the nearest value
    By Dushantha in forum Excel General
    Replies: 8
    Last Post: 06-05-2012, 08:15 AM
  4. [SOLVED] Round Cell to Nearest 0.25 up-to 1.0 then to nearest 0.5
    By haynesc87 in forum Excel General
    Replies: 4
    Last Post: 05-30-2012, 01:38 PM
  5. Rounding to the nearest 9
    By perrybarnett in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-22-2010, 05:48 PM
  6. Finding the nearest day
    By Salomey in forum Excel General
    Replies: 6
    Last Post: 12-02-2010, 01:24 PM
  7. Nearest value?
    By Rman3349 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-16-2008, 01:14 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