• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Formula Challenge 025 - Calculation Interval Hours

John Jairo V

John Jairo V

Well-Known Member
Hi to all the community!

I propose a challenge: Calculating the number of hours worked between an interval of hours, in all cases propose the attachment. The idea is to get a single formula that performs the task .

Assistant working file with the values calculated for review.

As has become tradition in these challenges , I will place the solution I have . Blessings to all!
 

Attachments

  • Calculation Interval Hours.xlsx
    10 KB · Views: 33
Hi John,

Can you explain your data page a bit more? If col A:B are the inputs, what is the purpose of D:K?
 
Hi @Luke M!

The hours are in the range D5 : K6 are the basis for calculating shift hours.

For example, if I worked from 20:00 until 5:15 (cells A7 and B7 respectively) belonging to shift hours from 0:00 to 8:30 hours they are 5:15 (5 hours and 15 minutes) .

The purpose of the challenge is to create a formula to calculate the times within each shift , the shift is independently on the same day ( range D5 : G6 ) or from one day to another ( range H5 : K6 ) .

I left the resulting values for test purposes in the range D7 : K20 .

Blessings !
 
Can we assume that everyone works less than 24 hrs straight? (e.g., not working from 0800 to 0800)
 
@John Jairo V

Seems strange, my replying to a challenge of yours! :)

Can you just clarify? Are the expected results those in D7:K20?

Are the shift times in D5:K6 fixed?

Regards
 
Hi @XOR LX! I'm glad to see you here.

Seems strange, my replying to a challenge of yours! :)
Click to expand...

Yeah! It's strange (you are the master)... but i'm sure you have a better answer than me in this challenge.

Can you just clarify? Are the expected results those in D7:K20?
Click to expand...

I'll try to explain. Sorry for my english (It's not my native language)

Yeah! Range D7:K20 is the expected result, it's time worked into a shuffle time. For example: If you start working at 0:00 and finish at 9:00,
time worked between 8:00 and 14:00 is 1:00, because both times match between 8:00 and 9:00, whereas time worked between 8:00 and 1:45 it is 2:45 , with 1:00 between 8:00 and 9:00 and 1:45 remaining between 0:00 and 1: 45.

The idea of this challenge is to find a formula that fits any schedule input and output , calculating the time worked on any shift, considering that always works less than 24 hours per shift.

In the file, I put several test times , so that the person performing the test has reference values for each schedule.

I hope I have been clear. Blessings!
 
Still working on this, but it's tricky. Realizing that sometimes the "working" part of the day is one the first day, and sometimes the 2nd, depending on how the shift schedule lies. It's a good one!
 
Still working on this, but it's tricky. Realizing that sometimes the "working" part of the day is one the first day, and sometimes the 2nd, depending on how the shift schedule lies. It's a good one!
Click to expand...

Yeah! making the resultant formula was tricky but fun. Good luck!
 
John Jairo V said:
If you start working at 0:00 and finish at 9:00,...whereas time worked between 8:00 and 1:45 it is 2:45 , with 1:00 between 8:00 and 9:00 and 1:45 remaining between 0:00 and 1: 45.
Click to expand...

@John Jairo V

Not sure I understand this example.

If I work between 00:00 and 09:00, how can I simultaneously "cover" the two periods between 08:00 and 09:00 and also that between 00:00 and 01:45 with respect to the shift 08:00-01:45?

Regards
 
Hi XOR LX ,

Suppose we consider the starting day as 22 July ; then the shifts covered by the following work time :

23 July - from midnight till 09:00

would be as follows :

the shift which goes from 22 July 08:00 till 23 July 01:45 ---- 1 hr. 45 minutes

the shift which goes from 23 July 08:00 till 23 July 09:00 ---- 1 hr.

Narayan
 
Thanks, Narayan.

Excellent explanation.

But also one which leads me to think that is a more complex challenge than I'd at first thought!

Regards
 
Hi to all!

I think this challenge is tricky but not impossible. Thanks Narayan for your explanation, maybe i'm not so clear in english for good explain. I will leave the challenge more time if someone post a solution. Blessings to all!
 
Hi @Lori!!! Amazing! You rock in Excel... great solution (length = 103 characters, v.s 206 of my solution), much better than mine. The solution I get was:

=MEDIAN($B7,F$5,IF(F$6<F$5,1,F$6))-MEDIAN($A7,F$5,IF(F$6<F$5,1,F$6))+($B7<$A7)*(MEDIAN(1,F$5,IF(F$6<F$5,1,F$6))-MEDIAN(0,F$5,IF(F$6<F$5,1,F$6)))+(F$6<F$5)*(MEDIAN($B7,0,F$6)-MEDIAN($A7,0,F$6)+($B7<$A7)*F$6)

Lori... can you explain the logic for your resolution? Really Impressive! Blessings!
 
@John Jairo V - Thanks, i thought this was already quite long and was actually expecting yours to be shorter! Two observations may help to simplify this problem:

1. We can consider a single 24 hour time period in which the two times either lie within the interval or outside the interval.

2. The problem is symmetrical i.e. the shift period and interval period are interchangeable.

With this in mind we can draw two timelines as below:

|-------A|—————————————————|B-----------------|
|-------------C|———————————————————————|D-----|

The solid lines (—) represent the case B > A and D > C
The dashed lines (--) represent the case B < A and D < C

We can now divide this unit interval into four sections according to solid and dashed line combinations. The lengths of intersections of these sections are as follows:

(—)(—) I
(—)(--) B - A - I
(--)(—) D - C + I
(--)(--) 1 - (D - C) - (B - A) + I

where I is the (signed) length of intersection of the solid lines:

I = MEDIAN(B,C,D) - MEDIAN(A,C,D)

Putting these cases together with the conditions they represent we can write:

MOD( (B < A) * (D - C) + (D < C) * (B - A) + (D > C) * I - (D < C) * I , 1)

Finally by substituting for cell references and combining the last two conditions using the SIGN() function we get the proposed formula.
 
Last edited:
@Maxim, i know how you feel. The above description skips over a few details and it took me a good deal of trial and error to arrive at the final formula.

In moving from the separate conditions to the single formula with conditions, careful attention needs to be paid over the signs. There are a couple of minor sign changes needed to be fully consistent for anyone trying to fill in the gaps.

In practice, i figured out the form of the solution with the above method and adjusted the signs to get the right results.
 
Here is mine solution, which, I think, could be improved, but my brain already exploded here (189 symbols with "=" ):
=IF(D$5+(D$5<$A7)>D$6+(D$6<$A7),IF($B7+($B7<$A7)>D$6+(D$6<$A7),D$6-$A7+(D$6<$A7)+MAX($B7+($B7<$A7)-D$5-(D$5<$A7),0),$B7+($B7<$A7)-$A7),MAX(MIN($B7+($B7<$A7),D$6+(D$6<$A7))-D$5-(D$5<$A7),0))

Here is the logic:
Let A, B, C, D - points on the circle, also as angles measures or day parts. There are only six possible crossings of arcs, formed by AB and CD arcs:
ABCD = no crossing
ABDC = B-A
ACBD = B-C
ACDB = D-C
ADBC = D-A
ADCB = (D-A) + (C-B)
Lets add 1-A to each of A,B,C,D, then we got a, b, c, d points, where:
a = 0,
b = MOD(B+1-A,1) = B-A+(B<A),
c = MOD(C+1-A,1) = C-A+(C<A) etc. Red part is 0 or 1.

So we can use six notes from above to make calculations:
IF(c>d,IF(b>d,d+max(b-c,0),b),max(min(b,d)-c,0))
And, after replacing b,c,d with sources (A,B,C,D) and a little simplification of the formula, we've got this monsterrrr from above.

Hope it could be simplified...
 
So I still don't understand aspects of this challenge. Can someone explain how the figure in H7 relates to the cells in H5:H6 and A7:B7?

I understand we're dealing with overlaps, but I don't understand how the figure of 8:15 is derived in this case.
 
Hi Jeff ,

If you see the times of 2:00 and 1:00 , it is clear that the start time of 2:00 is on one day , and the end time of 1:00 is on another day , though according to the terms of the challenge they are less than 24 hours apart.

Suppose we assume that the start time of 2:00 is on 4-August , then the time of 1:00 will be on 5-August.

If we take the times in A7 and B7 , we can similarly take the start time of 20:00 as on 4-August , and the end time of 05:15 as on 5-August.

Thus , the time overlap between the two time ranges will be from 20:00 on 4-August till 01:00 on 5-August , which is 5:00 hours.

Now comes the twist !

When the shift ends at 01:00 on 5-August , the next shift starts at 02:00 on 5-August , and will end at 01:00 on 6-August ; there will now be an overlap between 02:00 and 05:15 of 3:15 hours.

Thus , the total overlap is 8:15 hours.

Narayan
 
You mixed up yesterday, today and tommorow in your times

In D7 the shift starts today on 0:00 and ends today on 8:30, the work starts yesterday on 20:00 and ends today on 5:15.
So the overlap today is 5:15.

In G7 the shifts starts today on 20:00 and ends today on 22:00, the work starts yesterday on 20:00 and ends today on 5:15.
So the overlap today is 0:00 instead of the wrong 2:00 which assumes that the work starts today on 20:00 and ends tommorow on 5:15.
 
alphamax said:
You mixed up yesterday, today and tommorow in your times

In D7 the shift starts today on 0:00 and ends today on 8:30, the work starts yesterday on 20:00 and ends today on 5:15.
So the overlap today is 5:15.

In G7 the shifts starts today on 20:00 and ends today on 22:00, the work starts yesterday on 20:00 and ends today on 5:15.
So the overlap today is 0:00 instead of the wrong 2:00 which assumes that the work starts today on 20:00 and ends tommorow on 5:15.
Click to expand...

Hi ,

The dates were added for illustrative purposes ; the results were given by the OP. An attempt was made to understand how those results were arrived at , and the only way possible is what has been described.

If you have any other way of explaining OPs results , without the use of yesterday , today and tomorrow , that would be welcomed.

Narayan
 
You must log in or register to reply here.
Back
Top