• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Calculating total "on hold" time


Hi All,

A while since I've been around these parts. Hope you're all well.

I've been tasked with a challenge that has me completely baffled. I've been working on formulas for it for days and I can't find anything that does what I need, and so I'm here as a last resort hoping there are some galaxy brains here that can help.

- I have a data extract ("Data") with assignment group metrics for incidents. Every time an incident is assigned to a new group, a new data row is created with the assignment start and end date/time. For rows where there is no Assignment End Date/Time, these are incidents that have been resolved, but not closed, so I would presume the Resolve Date/Time could be used in place.
- I have a second extract ("Metrics") with state changes for incidents, the one I'm interested in being "on hold", which also details the start and end date/time of the state.
- I have a named range called HolidayList which contains a list of public holidays
- I have two named cells DayStart & DayEnd which contains the working day start and finish times (e.g. DayStart 09:00 = 0.375 and DayEnd 17:00 = 0.70833...)

What I need to do is calculate the total "on hold" time in hours for each data row in ("Data") using the state details on ("Metrics"), excluding non-work time hours (hours outside of DayStart & DayEnd), public holidays (HolidayList) & weekends (Sat/Sun). Essentially allowing us to subtract that total hold time from the total assignment time for the purposes of SLA reporting. I have been able to calculate the total assignment hours using a very convoluted NETWORKDAYS.INTL formula I found, but I cannot for the life of me figure out the "on hold" part.

As you can see on ("Metrics") for INC0...2, there could be multiple "on hold" times which apply to a ("Data") row so it needs to be able to sum all the applicable "on hold" hours that affect the assignment period.

I'm not against using helper columns if necessary.

I have attached a example template. If any more info is required let me know. Hopefully someone will have some idea how I can achieve this. To be honest I'm not even sure it can be solved. My defeatist tone can be attributed the amount of time I've been working on this to no avail. :(

Many thanks in advance to anyone who can help solve this nightmare.


  • Book1.xlsx
    14.1 KB · Views: 5