Feb 26 2020 11:59 PM
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.)
Feb 27 2020 01:48 AM
Feb 27 2020 10:00 AM
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 :) )
Feb 27 2020 11:02 PM - edited Feb 28 2020 06:47 AM
@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.
Feb 28 2020 02:05 PM
Seeing these formulas I know I would have never solved this.
Thank you so much both of you!! Very grateful :)
Mar 01 2020 05:20 AM
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
Mar 01 2020 01:22 PM
@fresope545 Perhaps the attached file does what you need.
Enter the number of hours you want to deduct in a time format. For instance, 35 hours would be 35:00. 17 hours and 40 minutes would be 17:40. No need for a minus in front of it.
Mar 01 2020 02:21 PM
Probably some differences in presentation if nothing else.