Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 12
Default Formula to display next Friday from B1 works except on the Friday itself.

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! :oD
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,872
Default Formula to display next Friday from B1 works except on the Friday itself.

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)+15)*7+4-MOD(B1-2,7)


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,522
Default Formula to display next Friday from B1 works except on the Friday itself.

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)+15)*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)
  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,872
Default Formula to display next Friday from B1 works except on the Friday itself.

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
  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 61
Default Formula to display next Friday from B1 works except on the Friday itself.

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


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
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

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


Similar Threads
Thread Thread Starter Forum Replies Last Post
Formula to return date of following Friday Daniel Bonallack Excel Worksheet Functions 6 April 5th 23 01:09 PM
formula for counting only Friday between to dates EricBB Excel Discussion (Misc queries) 4 May 26th 09 12:08 PM
If weekend date display previous Friday date jimar Excel Discussion (Misc queries) 4 September 17th 08 03:01 PM
Subtract 2 days and if it falls on Sat or Sun display Friday. Teethless mama Excel Discussion (Misc queries) 3 April 1st 08 02:41 PM
formula for if result is friday, make it thursday. joe schmo Excel Worksheet Functions 1 February 6th 08 08:23 PM


All times are GMT +1. The time now is 07:00 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"