Dates Workdays
 

Hi,
I have a long list of tasks each with a different start date and end date
spanning several months. I have used networkdays function to calclaute the
total number of work days for the task. I now want a formula to try and
calculate the number of networkdays in each month for each task.

The task start and end dates do not coincide with the start and end of the
months and the months are not calender months.

I assume I need calculate the networkdays based on a nested if statements to
check if the task start end dates are or < the month start and end dates
and then calculate . Or is there a simpler way to do this?

Dates Workdays
 

The approach I'd take:
For each month, find that month's start and end dates. Then the task's
start date for that month is MAX(task start date, month start date). The
task's end date for the month is MIN(task end date, month end date). The
task's networkdays for the month are =max(0,networkdays(task's start date for
the month, task's end date for the month)). The max(0, ... ) ensures that we
don't count negative workdays for tasks that are completed before or started
after that month.

"Marcus" wrote:

Hi,
I have a long list of tasks each with a different start date and end date
spanning several months. I have used networkdays function to calclaute the
total number of work days for the task. I now want a formula to try and
calculate the number of networkdays in each month for each task.

The task start and end dates do not coincide with the start and end of the
months and the months are not calender months.

I assume I need calculate the networkdays based on a nested if statements to
check if the task start end dates are or < the month start and end dates
and then calculate . Or is there a simpler way to do this?

Dates Workdays
 

Thanks. That worked a treat.

"bapeltzer" wrote:

The approach I'd take:
For each month, find that month's start and end dates. Then the task's
start date for that month is MAX(task start date, month start date). The
task's end date for the month is MIN(task end date, month end date). The
task's networkdays for the month are =max(0,networkdays(task's start date for
the month, task's end date for the month)). The max(0, ... ) ensures that we
don't count negative workdays for tasks that are completed before or started
after that month.

"Marcus" wrote:

Hi,
I have a long list of tasks each with a different start date and end date
spanning several months. I have used networkdays function to calclaute the
total number of work days for the task. I now want a formula to try and
calculate the number of networkdays in each month for each task.

The task start and end dates do not coincide with the start and end of the
months and the months are not calender months.

I assume I need calculate the networkdays based on a nested if statements to
check if the task start end dates are or < the month start and end dates
and then calculate . Or is there a simpler way to do this?