Forum Discussion

fresope545's avatar
fresope545
Copper Contributor
Feb 27, 2020

Calculate time between opening hours

Hi.   I would like to calculate time between opening hours and I would like to round it of to even 5 minutes. this also needs to consider dates, so the staring point is both date and time.   The...
excel
Formulas and Functions
Savia's avatar
Savia
Iron Contributor
Feb 27, 2020
Excel time values are stored as decimal parts - 1 is a full 24 hour day, 0.5 is 12 hours, etc. Dates are serial numbers started at 01/01/1900 = 1. Date/time values are both - so as I write it's 43888.41 or so.

To calculate the number of hours use =(close time - open time)*24

To add 35 hours use =start time + 35/24

To do rounding to 5 minutes use =MROUND(formula or number to round, 5/60/24)
fresope545's avatar
fresope545
Copper Contributor
Feb 27, 2020

Savia

 

Thank you for this, but I'm sorry, I still can't get it to work. I understand the different parts but not how to put them together.

 

But what I wan't is the end time based on 35 hours from the starting time.

 

So lets say I write 2020-02-27 11:00 and want to add 35 hours and get a time and a date when the 35 hours will end.

 

So, not counting the closed hours and not counting the weekend.

 

I want the answer to be 2020-03-04 12:00 (if I'm not mistaken 🙂 )

 

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Feb 28, 2020

    fresope545 The attached workbook has a working example of the required formula in it. I tested it thoroughly and sincerely hope that I didn't miss anything. In order to keep the formula reasonably clear, I chose to use some helper formulae. The model is dynamic and allows you to set the opening and closing time and the number of hours you want to jump ahead.

    Thank you, Savia , for pointing out how to round time to 5 minutes.

    • fresope545's avatar
      fresope545
      Copper Contributor
      Mar 01, 2020

      Hello Riny.


      So mit turns out I made a big mistake 🙂

       

      The date & time I was going to calculate from was not the starting date/time. It was the end date/time and I need to deduct from that end date to end up with the starting date.

       

      So therefore i wonder if you could help me once again. I have tried but unfortunatelly this is a bit over my head.

       

      Also, the time you decuct might not be full hours, it could be for example 17:40.

       

      Any chance you can help me?

       

      Best regards Fredrik 

       

      Riny_van_Eekelen 

    • fresope545's avatar
      fresope545
      Copper Contributor
      Feb 28, 2020

      Riny_van_Eekelen @Savia

       

      Seeing these formulas I know I would have never solved this.

       

      Thank you so much both of you!! Very grateful 🙂

Resources