Forum Discussion
Excel Formula Assistance
Thanks, now the easy part is done, I just need to find a way to incorporate the working hours that is sandwiched between the downtime start date and end date.... don't even know how to begin on that part.
Faust25 Have a look again at the attached file. Two solutions, neither of them very elegant. One uses several helper formulae to make it easier to follow. Another has all these helper formulae combined into one formula. It's the best I can offer just now.
By the way, the table at the bottom is just to visualise the problem.
Hi Riny_van_Eekelen,
Sorry for extending your hospitality... as you can see on my datasheet (from my attached file), we have multiple hosts, and each host falls from the category "working hours" and "non-working hours" if it falls under non-working hours the total of that host' total downtime would be "0" but if that host has multiple downtimes during "working hours" it should be calculated and with the output of the total downtime by minutes, and also before my previous query if that downtime extends a day or more, it should be calculated minus non-woking hours shift. (I tried incorporating your formula earlier and it baffled me on how to properly use it)
Note: don't mind the far right side, was trying to compute the before and after downtime that is not under working hours and subtracting it off the total downtime but it didn't work
Faust25 My hospitality is almost endless! But my understanding for your business is limited. What I did, is incorporated my "all-in-one" formula in the data that you have in your sheet.
See attached. I hope you find it helpful.
I'm getting confused on the output of your formula, I tried tweaking it, but to no avail, I attached the file that shout be the output (the formula is not right, I just wrote the output).
Thank you
Riny_van_Eekelen thank you for this soo much, let me analyze what you did, you really helped out a lot.