Using workday function to populate schedule

[Karl]

Hi,

I wonder if anyone can help. Using the workday formula and conditional
formatting I’ve created a Gantt chart in Excel that behaves more or less like
a Project file.

I have one small problem. The WORKDAY function looks like this:

=WORKDAY(D5,F5,C11:E1:F1:G1:H1:I1:J1)-1

The -1 at the end of the formula is there to solve a problem with the Gantt
chart. The WORKDAY function adds the number of days the task lasts (cell F5
in this case) to the start date (D5). So if you enter a duration of 5 days
into F5, you end up with six shaded cells on the Gantt chart; hence the -1.

However, for some reason with the -1 in the formula, a job that the formula
thinks ends on a Monday is shown as ending on a Sunday. So for instance:

Start date (D5) – 02/12/2008 (a Tuesday)
Duration in days (F5) – 4
End date (using above formula) – 07/12/2008 (a Sunday)

So, my question: am I using the WORKDAY function incorrectly? Why doesn’t it
show the job ending the formula minus one working day (Friday 5 December,
which is when the task really does end)?

Is there a better way of getting the WORKDAY function to include the start
date as part of the duration of the task?

And I know I could just enter a duration that’s one day shorter than it
really is, but I want everyone on our project to be able to use this. I don’t
always want to be left wondering if a ten-step project is actually ten days
too long or too short.

Many thanks for any help and advice you can give.

Best regards

Karl

 

[Niek Otten]

Hi Karl,

<Why doesn’t it show the job ending the formula minus one working day >

Because the -1 is ourside of the WORFDAY() function....
Use

=WORKDAY(D5,F5-1,C11:E1:F1:G1:H1:I1:J1), which, I think, is equivalent to
=WORKDAY(D5,F5-1,C1:J1)

instead

 

[Karl]

Hi Niek,

Thanks for that; it works perfectly. One more question, is there a way of
adding only workdays to the value of a cell. so for instance:

D6 = E5 + 1 working day

I'm now trying to avoid having tasks starting on the weekend.

Thanks

Karl