Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula to return date of following Friday | Excel Worksheet Functions | |||
formula for counting only Friday between to dates | Excel Discussion (Misc queries) | |||
If weekend date display previous Friday date | Excel Discussion (Misc queries) | |||
Subtract 2 days and if it falls on Sat or Sun display Friday. | Excel Discussion (Misc queries) | |||
formula for if result is friday, make it thursday. | Excel Worksheet Functions |