Wow. That was fast. I'm about to clock out for the day, so I won't have a good chance to look at it until Monday (or later if they assign something else). The first thing I'd like to say is that lunchtime is not excluded. The clock ticks as long as the task is still unfinished, regardless of lunch.

For the Starting adjustment

where B1 = 5:00:00 PM --- there is no cell for that 5:00 pm. This 5:00pm must be part of the formula that calculates how many seconds were part of the task on that day.

The calculations don't appear to account for the excluded times.

Examples:

If the start time is 7:00am and runs to 6:00pm, that would be 28800 seconds for a full day. The same would be if the start time was 7:00am and it ran into the next day.

If the start time is 7:00am and runs until 1:00pm of the same day, we need to calculate the seconds from 9:00am to 1:00pm. This is difficult because the end time is from a different cell. I think we need a different calculation if the start and end are on the same day.

If the start time is 3:00pm and runs until the next day, we need to calculate the seconds from 3:00pm to 5:00pm for the start day.

(B1-B2)*86400 doesn't appear to calculate a fraction of the 86400 seconds in a day (or I'm not understanding how the B1-B2 is going to give me a fraction of 86400. It also doesn't consider that 5:00pm minus 7:00am would be too many seconds since time doesn't start until 9:00am.

I understand how NETWORKDAYS-2 counts only the days in between and I know that I can use NETWORKDAYS(A1,A1,Holidays)*[the seconds calculation] for both the first and last days to exclude those days if they fall on an excluded day. I'm stuck trying to figure out the valid seconds actually used on the first and last day. I'm considering the several IF type functions (countif, sumif, etc.), but I don't use them enough to know them.

If the start is before 9:00am, the valid seconds would be 28800 if it takes all day. If the start is after 5:00pm, the valid seconds would be 0. If the task is completed the same day, I'd have to somehow calculate the seconds while including the end date/time from the ending time cell.

Likewise, it is just as difficult, if not more so, to calculate the difference between two date/time serial numbers, converting that to seconds, subtract the standard 5:00pm to 9:00am seconds between network days, and still have to calculate the actual seconds for the start and end days.

Thanks for helping. Anything that moves me closer to the goal is very much appreciated. Have a nice weekend, and thank you.