Calculate time between opening hours

[fresope545]

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

[#2]

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)

[#3]

@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 :) )

[#4]

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

[#5]

@Riny_van_Eekelen @Savia

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

Thank you so much both of you!! Very grateful :)

[#6]

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 

[#7]

@fresope545 

I'll get back to you!

[#8]

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

[#9]

@fresope545 

Probably some differences in presentation if nothing else.