Formula to display next Friday from B1 works except on the Friday itself.

Discussion in 'Microsoft Excel Misc' started by StargateFan, Aug 10, 2011.

  1. StargateFan

    StargateFan Guest

    I found a formula for displaying the next Friday from the user input
    date in B1. It works great except if the input date is a Friday. I
    need a spreadsheet that shows invoicing dates, which occur every
    Friday. But if the start date happens to be a Friday, need to have
    the list include that Friday.

    =B1-WEEKDAY(B1-4,2)+8

    If I input this week's Friday in B1 (August 12, 2011), the list starts
    on August 19th in B2 when it should actually say August 12th. For all
    other days of the week it seems to work fine.

    Thanks in advance for any help.

    Cheers! :blush:D
     
    StargateFan, Aug 10, 2011
    #1
    1. Advertisements

  2. StargateFan

    Claus Busch Guest

    Hi,

    Am Wed, 10 Aug 2011 05:40:38 -0700 (PDT) schrieb StargateFan:

    > =B1-WEEKDAY(B1-4,2)+8
    >
    > If I input this week's Friday in B1 (August 12, 2011), the list starts
    > on August 19th in B2 when it should actually say August 12th. For all
    > other days of the week it seems to work fine.


    try:
    =B1+(MOD(B1-2,7)+1>5)*7+4-MOD(B1-2,7)


    Regards
    Claus Busch
    --
    Win XP PRof SP2 / Vista Ultimate SP2
    Office 2003 SP2 /2007 Ultimate SP2
     
    Claus Busch, Aug 10, 2011
    #2
    1. Advertisements

  3. StargateFan

    Don Guillett Guest

    On Aug 10, 8:00 am, Claus Busch <> wrote:
    > Hi,
    >
    > Am Wed, 10 Aug 2011 05:40:38 -0700 (PDT) schrieb StargateFan:
    >
    > > =B1-WEEKDAY(B1-4,2)+8

    >
    > > If I input this week's Friday in B1 (August 12, 2011), the list starts
    > > on August 19th in B2 when it should actually say August 12th.  For all
    > > other days of the week it seems to work fine.

    >
    > try:
    > =B1+(MOD(B1-2,7)+1>5)*7+4-MOD(B1-2,7)
    >
    > Regards
    > Claus Busch
    > --
    > Win XP PRof SP2 / Vista Ultimate SP2
    > Office 2003 SP2 /2007 Ultimate SP2


    Or
    =B1-WEEKDAY(B1,3)+IF(WEEKDAY(B1,3)>4,11,4)
     
    Don Guillett, Aug 10, 2011
    #3
  4. StargateFan

    Claus Busch Guest

    Am Wed, 10 Aug 2011 06:36:10 -0700 (PDT) schrieb Don Guillett:

    > =B1-WEEKDAY(B1,3)+IF(WEEKDAY(B1,3)>4,11,4)


    or
    =B1+(WEEKDAY(B1,2)>5)*7+(5-WEEKDAY(B1,2))


    Regards
    Claus Busch
    --
    Win XP PRof SP2 / Vista Ultimate SP2
    Office 2003 SP2 /2007 Ultimate SP2
     
    Claus Busch, Aug 10, 2011
    #4
  5. StargateFan

    StargateFan Guest

    On Wed, 10 Aug 2011 05:40:38 -0700 (PDT), StargateFan
    <> wrote:

    >I found a formula for displaying the next Friday from the user input
    >date in B1. It works great except if the input date is a Friday. I
    >need a spreadsheet that shows invoicing dates, which occur every
    >Friday. But if the start date happens to be a Friday, need to have
    >the list include that Friday.
    >
    >=B1-WEEKDAY(B1-4,2)+8
    >
    >If I input this week's Friday in B1 (August 12, 2011), the list starts
    >on August 19th in B2 when it should actually say August 12th. For all
    >other days of the week it seems to work fine.
    >
    >Thanks in advance for any help.
    >
    >Cheers! :blush:D


    Thanks much for everyone's replies. They all seemed to work just
    great so I just picked one to put into my spreadsheet. The new
    printout covers next few months and gives me one less thing to have to
    stop and do manually. Cheers.
     
    StargateFan, Aug 11, 2011
    #5
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. MathewPBennett

    Date = Next Friday

    MathewPBennett, Dec 21, 2003, in forum: Microsoft Excel Misc
    Replies:
    12
    Views:
    312
    Bob Phillips
    Dec 22, 2003
  2. Guest
    Replies:
    14
    Views:
    305
    Guest
    Aug 3, 2005
  3. Guest
    Replies:
    3
    Views:
    335
    Guest
    Mar 28, 2007
  4. deepika :excel help

    Friday date of next week..

    deepika :excel help, Feb 13, 2008, in forum: Microsoft Excel Misc
    Replies:
    3
    Views:
    139
    Ron Rosenfeld
    Feb 13, 2008
  5. jlclyde

    Change Event Works on All computers except 1

    jlclyde, Jul 14, 2010, in forum: Microsoft Excel Misc
    Replies:
    1
    Views:
    251
    jlclyde
    Jul 14, 2010
Loading...

Share This Page