If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
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? |
#3
|
|||
|
|||
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? |
Thread Tools | |
Display Modes | |
|
|