Calculating Dates

[Chris]

I have approximately 200 rows of tasks in column C with the estimated
number of days each task should take to complete (0, .25, 3, etc) in
column K. I am trying to use a formula so I enter a start date for
the first task, Excel adds the number of days for that task and gives
me the finish date for the first task ( but excludes holidays and
weekends). The formula will then be repeated for each row to give me
the end date for the task in that row. Any ideas on how to accomplish
this? I am basically trying to create a Project schedule in Excel.

 

[T. Valko]

Try this...

A1 = some date
B1 = number of days to add
J1:J10 = list of holiday dates

C1 formula:

=WORKDAY(A1,B1,J$1:J$10)

Format as Date

Note that the WORKDAY function requires the Analysis ToolPak add-in be
installed if you're using a version of Excel prior to Excel 2007. If you
enter the formula and get a #NAME? error look in Excel help for the WORKDAY
function. It'll tell you how to fix the problem.

 

[Chris]

Try this...

A1 = some date
B1 = number of days to add
J1:J10 = list of holiday dates

C1 formula:

=WORKDAY(A1,B1,J$1:J$10)

Format as Date

Note that the WORKDAY function requires the Analysis ToolPak add-in be
installed if you're using a version of Excel prior to Excel 2007. If you
enter the formula and get a #NAME? error look in Excel help for the WORKDAY
function. It'll tell you how to fix the problem.

--
Biff
Microsoft Excel MVP






- Show quoted text -

Thanks! That works (I am using 2007) but it creates a new problem.
Many of the task lengths are less than a day. Is it possible to have
it add the partial days together? For example, task 1 is .25 days,
task 2 is .125 days, task 3 is .125 days, and task 4 is .25 days. My
original thought was to use the formula on each line, but doing so is
going to give me the same end date as start date, even though the 4
tasks add up to one day and should give the next day as the end date.
My only thought is to sum all the days and only give a start date and
end date for the whole project and not the individual tasks, but I
would prefer to give dates on each task if possible.

 

[T. Valko]

You could sum all the cells where the task takes less than a day but how you
try to account for that as far as the weekends and holidays, I have no idea.

=SUMIF(B:B,"<1")

--
Biff
Microsoft Excel MVP

Try this...

A1 = some date
B1 = number of days to add
J1:J10 = list of holiday dates

C1 formula:

=WORKDAY(A1,B1,J$1:J$10)

Format as Date

Note that the WORKDAY function requires the Analysis ToolPak add-in be
installed if you're using a version of Excel prior to Excel 2007. If you
enter the formula and get a #NAME? error look in Excel help for the
WORKDAY
function. It'll tell you how to fix the problem.

--
Biff
Microsoft Excel MVP






- Show quoted text -

Thanks! That works (I am using 2007) but it creates a new problem.
Many of the task lengths are less than a day. Is it possible to have
it add the partial days together? For example, task 1 is .25 days,
task 2 is .125 days, task 3 is .125 days, and task 4 is .25 days. My
original thought was to use the formula on each line, but doing so is
going to give me the same end date as start date, even though the 4
tasks add up to one day and should give the next day as the end date.
My only thought is to sum all the days and only give a start date and
end date for the whole project and not the individual tasks, but I
would prefer to give dates on each task if possible.