PC Review


Reply
Thread Tools Rate Thread

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

 
 
StargateFan
Guest
Posts: n/a
 
      10th Aug 2011
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
 
Reply With Quote
 
 
 
 
Claus Busch
Guest
Posts: n/a
 
      10th Aug 2011
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
 
Reply With Quote
 
 
 
 
Don Guillett
Guest
Posts: n/a
 
      10th Aug 2011
On Aug 10, 8:00*am, Claus Busch <(E-Mail Removed)> 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)
 
Reply With Quote
 
Claus Busch
Guest
Posts: n/a
 
      10th Aug 2011
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
 
Reply With Quote
 
StargateFan
Guest
Posts: n/a
 
      11th Aug 2011
On Wed, 10 Aug 2011 05:40:38 -0700 (PDT), StargateFan
<(E-Mail Removed)> 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.

 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Formula works on all worksheets except for 2 Jan Microsoft Excel Worksheet Functions 3 30th Mar 2009 05:36 PM
Friday to Friday - show dates =?Utf-8?B?RnJpZGF5IHRvIEZyaWRheSAtIHNob3cgZGF0ZXM= Microsoft Word Document Management 1 19th Apr 2006 06:06 PM
How can I get a row to display the date of the last friday, and every friday before that? Bryan.Cook@gmail.com Microsoft Excel Programming 1 10th Apr 2006 07:42 PM
How can I schedule a meeting for every day except Friday? =?Utf-8?B?ZGNvbXBsbw==?= Microsoft Outlook Discussion 1 4th May 2005 01:38 PM
Want to copy a cell down until it comes to the next nonblank cell, and then take this next nonblank cell until it comes to the next nonblank cell, and so on... GretOgrady Microsoft Excel Discussion 2 5th Jan 2005 09:29 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 10:06 AM.