# 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. ### StargateFanGuest

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! D

StargateFan, Aug 10, 2011

2. ### Claus BuschGuest

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

3. ### Don GuillettGuest

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
4. ### Claus BuschGuest

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
5. ### StargateFanGuest

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! 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