Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Calculate hours shared by two intervals

0

Hi All,

I am having trouble writting a formula to calculate hours shared by two intervals; the basic formula I have been working with are:

Duration: MOD(END-START;1)

(*) HrsShared: IF(start<end; MIN(end; upper)-MAX(start; lower); MAX(0;upper-start)+MAX(0;end-lower))

Case: Worker's Shift: Start=18:00; End=08:00 (next day); first 8 hours of the shift are "regular" (regular_start=18:00, regular_end=02:00)

--Specifically, I need to know how many hours of the "regular" portion  are within the Period-of-Interest: Lower: 19:00; Upper: 06:00 (next day)

(*) This formula works fine when Period-Of-Interest-Upper > Period-Of-Interest-Lower.

Your help will be greatly appreciated.

Kinds regards, DMurray3

exceldate-time
MS Office Version: xlsx
posted by: DMurray3 on Sat Aug 12, 2017 at 10:36 pm
Answer
Discuss

Answers

0

First off, you need to write all your times as Date/Time values, including the times for shift-start and shift-end. In your worksheet you probably want to show the regular time start as 18:00 and end as 02:00. Therefore the problem is how to suppress the date value which you have included in your question as "next day" but must be captured by Excel as Date/Time value. You can do this with cell formatting and a helper cell of column, perhaps, where you keep the current date. It's another question, if you need help with it.

For your immediate question, you need the employee's [Start-Work-Time] and the shift's [Shift-End-Time]. Obviousdly, the overlap is

MIN([Shift-End-Time], [Work-End-Time]) - [Start-Work-Time]
The result is a decimal fraction which you can convert to hours by multiplying with 24.
posted by: Variatus (rep: 4889) on Sat Aug 12, 2017 at 11:06 pm
Discuss

Discussion

Thnak you Variatus for your time and response; Unfortunately, I am forced to work with "times", although of course I do have the "date" that corresponds to each shift / case.

I understand how to have the date&time together (..I think it would something like INT(date)+mod(time;1)..), and then via "formatting" show the user the 'times' only.

However, I don't understand your recommended formula; As was included in my file, one of the cases:
Interval A: WorkStarts = 01/08/2017 18:00; WorkEnds = 02/08/2017 08:00
Regular Shift = 8hrs + 1hr Meal Break = 9hrs, so:

Interval 1: RegShiftStarts= 01/08/2017 18:00; RegShiftEnds= 02/08/017 03:00 (ie. 8+1 hours latter)
Interval 2: Period of Interest NGHT: POI_Lower = 01/08/2017 19:00; POI_Upper= 02/08/2017 06:00

The formula I requiere is to calculate the number of hours shared bewteen Interval 1= RegularShift and Interval 2= Period of Interest NGHT, ie: How many hours of the 'RegularShift' are shared with the 'Period of Interest NGHT'.

I apologize if I providing too much information, may have obscured the focal question I was making...

Could you please again evaluate my case and provide new feedback and -of possible- an xlsx file with your suggestions.?

Kind regards, DMurray3
DMurray3 Aug 14, '17 at 10:55 pm
The problem isn't in calculating the time difference or the overlap or any of those things that you explain. It's in the worksheet design, I tried to say. You must design the worksheet in such a way that you have data in in which can be processed. I said they must be Date/Time values. I can help, but I won't desing the sheet for you. Please post the sheet you have and I shall help you correct it. Once the sheet is designed efficiently doing the actual calculations will be easy for anyone. Most probably you won't need help with that. :-)
Variatus (rep: 4889) Aug 15, '17 at 2:49 am
I would also ask you to explain how you enter the times or date/times in your worksheet. Are you doing it yourself? Or does the sheet have to be "fool-proof"?
Variatus (rep: 4889) Aug 15, '17 at 2:51 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login