Hi,
I need to create a dynamic table that helps with backplanning from a specified date and time. It's meant to be used for project management, so I essentially something like this:
If someone enters data into the blue boxes the full table should populate with dates and times in the format h:mm AM/PM dd/mm/yyyy (see to the right of 'END' for an example).
The only additional complication is that I need to exclude holidays and non-working hours from the times that are returned in the table.
I think the formula would look something like this:
Using a date and time range that excludes group A (specified holiday date range) and B (specified non-working hour time range), calculate X (time and date specified in blue boxes) - Y (time and date duration listed on the left hand column of the table).
I've tried WORKDAY, NETWORKDAYS, and varioius time and date combinations but the biggest issue is nothing allows for combinated date and time addition and subtraction while also excluding specified date and time ranges.
Any ideas?
I need to create a dynamic table that helps with backplanning from a specified date and time. It's meant to be used for project management, so I essentially something like this:
If someone enters data into the blue boxes the full table should populate with dates and times in the format h:mm AM/PM dd/mm/yyyy (see to the right of 'END' for an example).
The only additional complication is that I need to exclude holidays and non-working hours from the times that are returned in the table.
I think the formula would look something like this:
Using a date and time range that excludes group A (specified holiday date range) and B (specified non-working hour time range), calculate X (time and date specified in blue boxes) - Y (time and date duration listed on the left hand column of the table).
I've tried WORKDAY, NETWORKDAYS, and varioius time and date combinations but the biggest issue is nothing allows for combinated date and time addition and subtraction while also excluding specified date and time ranges.
Any ideas?