Using workday function to populate schedule
 

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

Using workday function to populate schedule
 

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

Using workday function to populate schedule
 

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