Forum Discussion
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 open hours are between 9:00 and 17:30 Monday - Friday (using 24 hour format as you can see)
And I want to be able to add 35 hours to a specific time.
So let's say that II want a starting point at 11,30 on Tuesday i want to see when these 35 hours will end (rounded of with even 5 minutes.)
8 Replies
- 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)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 🙂 )
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.
