Hi,

I wonder if anyone can help. Using the workday formula and conditional
formatting Ive 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,C1:D1: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 doesnt 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 thats one day shorter than it
really is, but I want everyone on our project to be able to use this. I dont
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

Hi Karl,

<Why doesnt 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,C1:D1:E1:F1:G1:H1:I1:J1), which, I think, is equivalent to
=WORKDAY(D5,F5-1,C1:J1)

instead

--
Kind regards,

Niek Otten
Microsoft MVP - Excel



"Karl" wrote in message
...
Hi,

I wonder if anyone can help. Using the workday formula and conditional
formatting Ive 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,C1:D1: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 doesnt
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 thats one day shorter than it
really is, but I want everyone on our project to be able to use this. I
dont
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

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

"Niek Otten" wrote:

Hi Karl,

<Why doesnt 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,C1:D1:E1:F1:G1:H1:I1:J1), which, I think, is equivalent to
=WORKDAY(D5,F5-1,C1:J1)

instead

--
Kind regards,

Niek Otten
Microsoft MVP - Excel



"Karl" wrote in message
...
Hi,

I wonder if anyone can help. Using the workday formula and conditional
formatting Ive 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,C1:D1: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 doesnt
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 thats one day shorter than it
really is, but I want everyone on our project to be able to use this. I
dont
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