Forum Discussion

Faust25's avatar
Faust25
Copper Contributor
Mar 19, 2020

Excel Formula Assistance

Hi to All,   As you can see below I have an example on one of my data, and by looking at the downtime duration the answer of it is wrong, what I want to achieve is that I need to count the hours an...
excel
Formulas and Functions
Faust25's avatar
Faust25
Copper Contributor
Mar 20, 2020

Riny_van_Eekelen 

Hi thank you for your input, actually sorry for not mentioning it earlier, that data on G2 was given and not calculated using a formula, if you can give me an idea on how to calculate them properly, then I'll try to think on a way to factor in the daily shifts in between

Riny_van_Eekelen's avatar
Riny_van_Eekelen
Platinum Contributor
Mar 20, 2020

Faust25 I had entered my formula is in G5. It's visible at the top in the first picture.

 

=E2+F2-C2-D2

 

It takes End date plus End time minus Start date minus Start time. That gives you a total of 50 hours and 23 minutes. But, I didn't save the file I worked in so now I made a new one, with some formulae and formatting examples. Hope it helps you in fixing your own schedule.

  • Faust25's avatar
    Faust25
    Copper Contributor
    Mar 20, 2020

    Riny_van_Eekelen 

    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.

    • Riny_van_Eekelen's avatar
      Riny_van_Eekelen
      Platinum Contributor
      Mar 20, 2020

      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.

      • Faust25's avatar
        Faust25
        Copper Contributor
        Mar 20, 2020

        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 

         

Resources