Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Exclude weekends when counting days only on day 29 or 30
I have a formula that returns a date 30 calendar days from a given date.
Example, cell A1 has 02/14/08. I want to add 30 days to this date and find out what date that is but if that date is a Saturday or a Sunday, I want the previous business day which is a Friday. I want to count all Saturdays and Sundays during the 30 day cycle EXECPT if the 30th day is a Saturday or a Sunday, I need to have a date returned that will back it up to the last business day. In other words, if today were February 15th and I had 30 calendar days to complete my task, that would be March 16th, but since March 16th is a Sunday, I need the formula to return the last business day (March 14th) on or before day # 30. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Exclude weekends when counting days only on day 29 or 30
One way:
=A2+30-(WEEKDAY(A2+30,3)-4)*(WEEKDAY(A2+30,3)=5) It can be simplified, but at least that shows what's being done. -- David Biddulph "Loren A - Huntley, Illinois" <Loren A - Huntley, wrote in message ... I have a formula that returns a date 30 calendar days from a given date. Example, cell A1 has 02/14/08. I want to add 30 days to this date and find out what date that is but if that date is a Saturday or a Sunday, I want the previous business day which is a Friday. I want to count all Saturdays and Sundays during the 30 day cycle EXECPT if the 30th day is a Saturday or a Sunday, I need to have a date returned that will back it up to the last business day. In other words, if today were February 15th and I had 30 calendar days to complete my task, that would be March 16th, but since March 16th is a Sunday, I need the formula to return the last business day (March 14th) on or before day # 30. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Exclude weekends when counting days only on day 29 or 30
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formatting to Exclude Weekends | Excel Worksheet Functions | |||
How do I set up a calculation to exclude weekends? | Excel Worksheet Functions | |||
Date Calculation to exclude weekends | Excel Worksheet Functions | |||
calculation to exclude weekends | Excel Worksheet Functions | |||
Schedule to exclude weekends and holidays | Excel Discussion (Misc queries) |