+ Reply to Thread
Results 1 to 10 of 10

nearest friday to date and if date is a friday return that friday not the following

  1. #1
    Forum Contributor
    Join Date
    01-21-2010
    Location
    Glasgow
    MS-Off Ver
    Excel 2003 and 2007
    Posts
    105

    Smile nearest friday to date and if date is a friday return that friday not the following

    Hi there,

    I have found several similar formula's on here but not quite what I need.

    I need a date in column a to have 60 days added and then to find the nearest Friday to that date. If the date 60 days on is a friday I want to return that friday and not the next.

    i.e.
    Date in column A is 25/01/2010 date 60 days is 26/03/2010 which is a frdiay so formula should return this date. It should return this date for anything between 19/01/2010 and 25/01/2010.
    The formula I was using is:
    Please Login or Register  to view this content.
    However, this returns the following friday if the date + 60 days is a friday.

    Please help

    Thanks
    Last edited by Libster78; 03-01-2010 at 11:25 AM.

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: nearest friday to date and if date is a friday return that friday not the followi

    Assuming by nearest your mean next - and taking into account that if result is a Friday that date should persist then perhaps:

    B1: =A1+60+CHOOSE(WEEKDAY(A1+60,2),4,3,2,1,0,7,6)

  3. #3
    Forum Expert darkyam's Avatar
    Join Date
    03-05-2008
    Location
    Houston, TX
    MS-Off Ver
    2013
    Posts
    2,191

    Re: nearest friday to date and if date is a friday return that friday not the followi

    Or perhaps =A1+60+MOD(9-WEEKDAY(A1),7)
    Last edited by darkyam; 03-01-2010 at 09:10 AM.

  4. #4
    Forum Contributor
    Join Date
    01-21-2010
    Location
    Glasgow
    MS-Off Ver
    Excel 2003 and 2007
    Posts
    105

    Re: nearest friday to date and if date is a friday return that friday not the followi

    Hi there, that formula did not work.

    I mean if the date = 60 days is a sat-fri, say 27/02/2010 to 05/03/2010 it should return 05/03/2010 for all of these.

    Does that make sense?

    The formula I had returns the friday 5th march when A1 + 60 = to anywhere 27/02/2010 to 04/2010 but for the Friday it returns 12/03/2010

    Thanks
    Libby

  5. #5
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: nearest friday to date and if date is a friday return that friday not the followi

    Your last post makes no sense to me I'm afraid -- hopefully someone else can follow it.

    Why would 27/2 + 60 -> 5/3 ?

    The result should be 30-Apr - given 27/2 + 60 -> 28/4 which is a Wed - hence +2 is 30-Apr.

  6. #6
    Forum Contributor
    Join Date
    01-21-2010
    Location
    Glasgow
    MS-Off Ver
    Excel 2003 and 2007
    Posts
    105

    Re: nearest friday to date and if date is a friday return that friday not the followi

    No I meant 27/02 would be the date returned after 60 days already added. so original date would be 29/12/2009. Sorry for confusion.

    So 29/12/2009 + 60 days is 27/02/2010 which is a Sat, this then needs to return the following friday which will be 05/03/2010. All dates therefore from 29/12/2009 up to and including 04/01/2010 should return 05/03/2010 as the friday I want.

    Thank you

  7. #7
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: nearest friday to date and if date is a friday return that friday not the followi

    Quote Originally Posted by DonkeyOte View Post
    =A1+60+CHOOSE(WEEKDAY(A1+60,2),4,3,2,1,0,7,6)
    I imagine that should be

    =A1+60+CHOOSE(WEEKDAY(A1+60,2),4,3,2,1,0,6,5)

    or Darkyam's suggestion would work for you

    This is mine:

    =A1+60+7-WEEKDAY(A1+60+1)

  8. #8
    Forum Contributor
    Join Date
    01-21-2010
    Location
    Glasgow
    MS-Off Ver
    Excel 2003 and 2007
    Posts
    105

    Re: nearest friday to date and if date is a friday return that friday not the followi

    Thats it. I think it was me not thinking it was correct.

    Thanks to everyone.

  9. #9
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: nearest friday to date and if date is a friday return that friday not the followi

    Quote Originally Posted by daddylonglegs View Post
    I imagine that should be

    =A1+60+CHOOSE(WEEKDAY(A1+60,2),4,3,2,1,0,6,5)
    Yes, thanks dll... typo on my part - best always to test

  10. #10
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: nearest friday to date and if date is a friday return that friday not the following

    Try this formula. It should return the nearest (not next) Friday to 60 days from the date in A1.
    =(A1+60+4)-WEEKDAY(A1+60+4-6)

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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