Thanks Thanks:  0
Likes Likes:  0
Page 1 of 5 123 ... LastLast
Results 1 to 10 of 47

Thread: Finding nearest Friday formula

  1. #1
    New Member
    Join Date
    Feb 2014
    Posts
    35
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Finding nearest Friday formula

    Hi,

    I have an IF formula that looks to a particular date and brings back the nextFriday however I need to change it so that if the date is already a Friday that is keeps this date rather than taking the following friday.

    The current formula I have is:

    =IFERROR(IF(WEEKDAY([@[EXPECTED PAYMENT DATE]]) < 6, [@[EXPECTED PAYMENT DATE]] + (6- WEEKDAY([@[EXPECTED PAYMENT DATE]])), [@[EXPECTED PAYMENT DATE]] + 13 - WEEKDAY([@[EXPECTED PAYMENT DATE]])),0)

    Thanks!

  2. #2
    Board Regular skorpionkz's Avatar
    Join Date
    Oct 2013
    Location
    Dublin
    Posts
    1,152
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Finding nearest Friday formula

    try this:
    Excel 2012
    AB
    128/03/201428/03/2014

    Sheet1



    Worksheet Formulas
    CellFormula
    B1=IFERROR(IF(WEEKDAY(A1,15)=1,A1,A1+(8-WEEKDAY(A1,15))),0)

    Last edited by skorpionkz; Feb 25th, 2014 at 07:46 AM.
    Regards,
    Andrzej (Andrew) Bejmart

    ------------------------------------------------------------------------
    Any fool can know. The point is to understand. - Albert Einstein

    Windows 10, Office 2013, Home PC
    Windows Server Datacenter, Office 2010, Work - Citrix Server

  3. #3
    Board Regular skorpionkz's Avatar
    Join Date
    Oct 2013
    Location
    Dublin
    Posts
    1,152
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Finding nearest Friday formula

    actually there is even shorter formula:

    Excel 2012
    ABC
    101/03/201407/03/201407/03/2014

    Sheet1



    Worksheet Formulas
    CellFormula
    B1=IFERROR(IF(WEEKDAY(A1,15)=1,A1,A1+(8-WEEKDAY(A1,15))),0)
    C1=IFERROR(A1+(7-WEEKDAY(A1,16)),0)

    Regards,
    Andrzej (Andrew) Bejmart

    ------------------------------------------------------------------------
    Any fool can know. The point is to understand. - Albert Einstein

    Windows 10, Office 2013, Home PC
    Windows Server Datacenter, Office 2010, Work - Citrix Server

  4. #4
    New Member
    Join Date
    Feb 2014
    Posts
    35
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Finding nearest Friday formula

    Do you need two extra cells for the second one though? I need to keep it to one cell

    I think the first one works..
    Last edited by Nicole87; Feb 25th, 2014 at 08:03 AM.

  5. #5
    Board Regular skorpionkz's Avatar
    Join Date
    Oct 2013
    Location
    Dublin
    Posts
    1,152
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Finding nearest Friday formula

    no you dont need second cell

    this are 2 different formulas which gives the same result, but the second formula is shorter and dont need IF statement.
    Regards,
    Andrzej (Andrew) Bejmart

    ------------------------------------------------------------------------
    Any fool can know. The point is to understand. - Albert Einstein

    Windows 10, Office 2013, Home PC
    Windows Server Datacenter, Office 2010, Work - Citrix Server

  6. #6
    New Member
    Join Date
    Feb 2014
    Posts
    35
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Finding nearest Friday formula

    Ah ok I c.

    Can you combine these two formulas into a shorter one by any chance?

    =IF([@[PAYMENT STATUS]]="PAID","PAID",IF(LEN([@[DATE OVERRIDE]])>0,[@[DATE OVERRIDE]],SUM(AT97,AS97,AU97))) - looks at 3 different cells and picks up the first three unless there is a date in the fourth (date override)

    &

    The formulas you provided above to bring it to the nearest Friday?

    Many Thanks,
    Nicole

  7. #7
    Board Regular skorpionkz's Avatar
    Join Date
    Oct 2013
    Location
    Dublin
    Posts
    1,152
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Finding nearest Friday formula

    1* I don't think that your formula can be changed to be shorter

    2* Yes it brings nearest Friday

    Code:
    =WEEKDAY(A1,16)
    =IFERROR(A1+(7-WEEKDAY(A1,16)),0)
    option 16 in WEEKDAY makes week Saturday (1) - Friday (7) so if you have currently Fryday it will take todays day then + (7 - weekday of friday (7)) = 0 so it stays in same day. if today is saturday then it is Today + 6 days (7-1) and so on
    Regards,
    Andrzej (Andrew) Bejmart

    ------------------------------------------------------------------------
    Any fool can know. The point is to understand. - Albert Einstein

    Windows 10, Office 2013, Home PC
    Windows Server Datacenter, Office 2010, Work - Citrix Server

  8. #8
    New Member
    Join Date
    Feb 2014
    Posts
    35
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Finding nearest Friday formula

    Can you add on the friday one to the longer formula above then?

  9. #9
    Board Regular skorpionkz's Avatar
    Join Date
    Oct 2013
    Location
    Dublin
    Posts
    1,152
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Finding nearest Friday formula

    instead of what?

    something like this?
    Code:
    IF([@[PAYMENT STATUS]]="PAID","PAID",IF(LEN(IFERROR(A1+(7-WEEKDAY(A1,16)),0))>0,IFERROR(A1+(7-WEEKDAY(A1,16)),0),SUM(AT97,AS97,AU97)))
    Regards,
    Andrzej (Andrew) Bejmart

    ------------------------------------------------------------------------
    Any fool can know. The point is to understand. - Albert Einstein

    Windows 10, Office 2013, Home PC
    Windows Server Datacenter, Office 2010, Work - Citrix Server

  10. #10
    New Member
    Join Date
    Feb 2014
    Posts
    35
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Finding nearest Friday formula

    That doesnt seem to work. So I want to use the formula below and add in the rule to make the result the nearest Friday:

    =IF([@[PAYMENT STATUS]]="PAID","PAID",IF(LEN([@[DATE OVERRIDE]])>0,[@[DATE OVERRIDE]],SUM(AT97,AS97,AU97))




Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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