Avoid Weekends

[mycon73]

Hi,

I have this function for a Excel Gantt Chart which has Estimated Days, Planned Start Date & Target Date.

=IF(K65="","",IF(L65="","",IF(AND(K65,L65),L65+K65,"")))

Right now, some of the days is falling during a weekend. How can I modify this function or utilize another to avoid weekend dates or to round up to the following Monday & have that date show as its 4 Target / Due Date instead? I suppose I could modify my estimate duration but looking for something to be more automated.

Out of curiosity, I may want an option to select a Friday or Monday & perhaps want a pop-up option to have me select.

Thanks

[Pauleyb]

You should look at the WORKDAY function.
=IF(K65="","",IF(L65="","",IF(AND(K65,L65),WORKDAY(K65,L65),"")))

(assuming K65 is a date and L65 is the number of workdays.)

Pauley

[mycon73]

Hi Pauleyb,

Thanks for this! K65 is my estimated durations or days needed & L65 is my planned start date. I modified the Workday portion & this function appears to work great.

Question though,

If I have a start date of 07/30/12 & say it's going to take 5 days to complete, should the result date be 08/30, which happens to be on a Friday. Right now, it's rounding it to Monday.

How would this be modified?

Thanks again...

[Pauleyb]

The calculation is basically adding 5 days to the start day. So, it, in a way, is a matter of perspective. For example, if you start a 1 day project on a Monday, do you want to assume it ends on Monday afternoon or Tuesday morning? Usually, you want to assume Tuesday morning so that it can 'feed' a dependent task as starting on Tuesday. In other words, 2 one day serial tasks would be:
Task 1: Start on Monday, end on Tuesday
Task 2: Starts at the end of task 1, so Tuesday, end on Wednesday

If you want to assume one day tasks start and end on the same day then you have to be careful and subtract 1 day on the WEEKDAY formula for the duration, and then add 1 day to the date whenever you reference that end day as a start for another task. Otherwise, your 2 one day serial tasks would both start and end on Monday. For example, this is what you want to avoid:
Task 1: Start on Monday, end on Monday
Task 2: Starts at the end of task 1, so Monday, end on Monday

Typically, scheduling programs assume a 1 day task takes a full 24 hours of real time and so completes the following day. BTW, if you are doing a lot of schedules, you should be using scheduling software. Excel can work at a basic level, but by the time you get everything working, the time spent would have paid for the other software (e.g. MS Project).

Pauley

[mycon73]

Hi Pauleyb,

Thanks for the feedback. Yes - I'm also using MS Project. As good as MS Project is, it also can't do everything or has difficulties doing some things.

I'll keep this function as is....