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
Riny_van_Eekelen's avatar
Riny_van_Eekelen
Platinum Contributor
Mar 19, 2020

Faust25 

Not sure how you want to factor in the shift time, but you probably had the correct formula in G2. Try formatting it (custom format) [h]:mm. Then Excel will keep counting when you exceed 24 hours.

 

  • 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.

Resources