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

Find if 2 sets of times overlap calculate time overlapped even through midnight

S

Sam Patterson

New Member
Hi and thankyou to all in advance.

The problem is as follows, I have been working on time sheet to calc pay.

Example 1 - If a person starts shift on 15/10/11 at 17:00 and works to 15/10/11 23:00 then the time he works falls in to a bonus period 20:00 to 23:00 i.e. he has worked 6hrs and 3 hrs of which are at a special bonus.

I have used cells for start date w23, start time x23, finish date y23, finish time z23, bonus1 start time ah2, bonus1 finish time ah3, using the following formula

=IF(OR(Z23+Y23<=$AH$2+W23,$AH$3+W23<=X23+W23),"00.00",MIN(Z23+Y23,$AH$3+W23)-MAX(X23+W23,$AH$2+W23)) which seems to work fine to calculate the hours worked in the bonus period.

The problem comes when I try to use the formula to work out the second bonus period which is from 23:00 to 06:00 the next day. The cells used for bonus2 start time aj2, finish time ak3 are used instead of ah2 and ah3 and just changed in the formula.

I cannot get it to work properly because it crosses midnight

Would appreciate some help I am not an expert so forgive me if I ask some questions (formula used from previous question answers on this site)

Thanks
 
I think this works:

=(1+$AJ$3-$AH$3)-MAX(0,MIN(1+$AJ$3-$AH$3,W24+1+$AJ$3-(Y24+Z24)))-MAX(X24-AH3,0)


Idea is to start with the max possible bonus time, and then subtract any time not used between the 2 end points of bonus time and working time.
 
Hi Sam ,


Can I understand your logic first ?


1. You have a START_DATE , START_TIME , END_DATE , END_TIME.


2. We need to find out the difference between the END_TIME and the START_TIME.


3. Normally , if the two times are on the same date , the END_TIME will be greater than the START_TIME ; if not , it means that there has been a midnight crossover. In this case , we can have two possibilities :


a) The difference between the two times is less than 24 hours


b) The difference between the two times is greater than 24 hours.


4. You also have a bonus time ; you mention a bonus1 start time and a bonus1 end time ; why do you need a bonus1 end time ? I would think that if the END_TIME is beyond the bonus1 start time , then the time difference between the END_TIME and the bonus1 start time will be at bonus payment.


5. In the example you have given START_DATE = 10/15/2011 , START_TIME = 17:00 , END_DATE = 10/15/2011 , END_TIME = 23:00 , bonus1 start time = 20:00 ; in this case the time difference between 23:00 and 20:00 will be at bonus payment.


6. If the same person continues to work till 06:00 on 10/16/2011 , the total time beyond the bonus1 start time is = 06:00 - 20:00 ; since 06:00 is less than 20:00 , we add 24 hours to 06:00 to get 30:00 ; thus 30:00 - 20:00 gives us 10 hours as the time difference.


Is all this correct ?


Narayan
 
Luke - I tried your formula but it does not seem to work. I will try to give you some clearer information as follows. A person can start and finish a shift at any time, they receive a bonus additional to their pay of 25% of the hourly rate for the number of hours worked if they work between the hours of 20:00 and 23:00, they also receive a bonus of 33% of the hourly rate for the number of hours worked if they work between 23:00 and 06:00 the next day. I have been able to work out the hours worked during the first bonus period (bonus1) with the formula =IF(OR(Z23+Y23<=$AH$2+W23,$AH$3+W23<=X23+W23),"00.00",MIN(Z23+Y23,$AH$3+W23)-MAX(X23+W23,$AH$2+W23)) which works fine and displays a total, I copied the formula to another cell and changed the bonus time periods but it would not work for me. The cells used for input and info for the above formula are as follows:-

Start Date w23, start time x23, finish date y23, finish time z23, bonus1 start time ah2 (20:00), bonus1 finish time ah3(23:00).

I replaced bonus1 figures with bonus2 figures as follows, bonus2 start time aj2(23:00) and bonus2 finish time ak3(06:00)

Hope this helps you


Narayan - I hope the above information clarifies what you had asked, the bonus2 calculation is the problem as the bonus period spans midnight please let me know if you need any more info


Many Thanks for your patience
 
Sam,

Apologies, I think I copied my formula down 1 row before posting here. Should have been:

=(1+$AJ$3-$AH$3)-MAX(0,MIN(1+$AJ$3-$AH$3,W23+1+$AJ$3-(Y23+Z23)))-MAX(X23-AH3,0)


Expanding on what I said before, formula is using the +1 notation to keep track of the "next day" problem.


If this formula doesn't work for you, please elaborate on what went wrong. Wrong number, error message, workbook crashed, etc.
 
Hi Luke

The formula does not work for every start/finish scenario that I have tried some examples as follows -

Start date 16/10/11 time 23:30, Finish date 17/10/11 time 07:00, this input displays 07:30 as bonus hours but should be only 6:30 as bonus time runs from 23:00 to 06:00.

Also if Start date 17/10/11 time 00:30, Finish date 17/10/11 time 07:00, this input displays 00:00 but should be 05:30.

This problem seems to be for start times at the beginning of the day in the bonus period and for finish times after the bonus period.

Thanks again Sam
 
Hi Luke

Just noticed that in your formula you are using AJ3 and AH3 which both have the same value i.e. 23:00, AH3 is the finish time for the bonus1 period (23:00) and AJ3 is the start time for the bonus2 period (23:00)the bonus period I am having problems with. The finish time for the bonus2 period is 06:00 cell AK3. I tried substituting AH3 with AK3 but it still did not work

Just to clarify cells - Start Date w23, start time x23, finish date y23, finish time z23, bonus2 start time aj2(23:00) and bonus2 finish time ak3(06:00)

Sorry for any confusion

Many Thanks Sam
 
Hi Sam ,


I tried out some formulae on the following data ; can you check out whether the results are OK ?


START DATE START TIME END DATE END TIME DURATION BONUS2 TIME BONUS1 TIME NORMAL TIME

10/15/2011 ....... 9:00 ..... 10/15/2011 ..... 17:00 ..... 0 ....... 8:00 ..... 0:00 ..... 0:00 ..... 0:00 ..... 8:00

10/15/2011 ....... 9:00 ..... 10/15/2011 ..... 20:00 ..... 0 ..... 11:00 ..... 0:00 ..... 0:00 ..... 0:00 ..... 11:00

10/15/2011 ....... 9:00 ..... 10/15/2011 ..... 21:00 ..... 0 ..... 12:00 ..... 0:00 ..... 1:00 ..... 1:00 ..... 10:00

10/15/2011 ....... 9:00 ..... 10/15/2011 ..... 23:00 ..... 0 ..... 14:00 ..... 0:00 ..... 3:00 ..... 3:00 ..... 8:00

10/15/2011 ....... 9:00 ..... 10/15/2011 ..... 23:30 ..... 0 ..... 14:30 ..... 0:30 ..... 3:30 ..... 3:00 ..... 8:00

10/15/2011 ....... 9:00 ..... 10/16/2011 ....... 0:00 ..... 1 ..... 15:00 ..... 1:00 ..... 4:00 ..... 3:00 ..... 8:00

10/15/2011 ....... 9:00 ..... 10/16/2011 ....... 3:00 ..... 1 ..... 18:00 ..... 4:00 ..... 7:00 ..... 3:00 ..... 8:00

10/15/2011 ....... 9:00 ..... 10/16/2011 ....... 6:00 ..... 1 ..... 21:00 ..... 7:00 .... 10:00 ..... 3:00 ..... 8:00

10/15/2011 ..... 20:00 ..... 10/15/2011 ..... 21:00 ..... 0 ....... 1:00 ..... 0:00 ..... 1:00 ..... 1:00 ..... 0:00

10/15/2011 ..... 20:00 ..... 10/15/2011 ..... 23:00 ..... 0 ....... 3:00 ..... 0:00 ..... 3:00 ..... 3:00 ..... 0:00

10/15/2011 ..... 20:00 ..... 10/15/2011 ..... 23:30 ..... 0 ....... 3:30 ..... 0:30 ..... 3:30 ..... 3:00 ..... 0:00

10/15/2011 ..... 20:00 ..... 10/16/2011 ....... 0:00 ..... 1 ....... 4:00 ..... 1:00 ..... 4:00 ..... 3:00 ..... 0:00

10/15/2011 ..... 20:00 ..... 10/16/2011 ....... 3:00 ..... 1 ....... 7:00 ..... 4:00 ..... 7:00 ..... 3:00 ..... 0:00

10/15/2011 ..... 20:00 ..... 10/16/2011 ....... 6:00 ..... 1 ..... 10:00 ..... 7:00 .... 10:00 ..... 3:00 ..... 0:00

10/15/2011 ..... 21:00 ..... 10/15/2011 ..... 23:00 ..... 0 ....... 2:00 ..... 0:00 ..... 3:00 ..... 3:00 ..... 0:00

10/15/2011 ..... 21:00 ..... 10/15/2011 ..... 23:30 ..... 0 ....... 2:30 ..... 0:30 ..... 3:30 ..... 3:00 ..... 0:00

10/15/2011 ..... 21:00 ..... 10/16/2011 ....... 0:00 ..... 1 ....... 3:00 ..... 1:00 ..... 3:00 ..... 2:00 ..... 0:00

10/15/2011 ..... 21:00 ..... 10/16/2011 ....... 3:00 ..... 1 ....... 6:00 ..... 4:00 ..... 6:00 ..... 2:00 ..... 0:00

10/15/2011 ..... 21:00 ..... 10/16/2011 ....... 6:00 ..... 1 ....... 9:00 ..... 7:00 ..... 9:00 ..... 2:00 ..... 0:00

10/15/2011 ..... 23:00 ..... 10/15/2011 ..... 23:30 ..... 0 ....... 0:30 ..... 0:30 ..... 3:30 ..... 3:00 ..... 0:00

10/15/2011 ..... 23:00 ..... 10/16/2011 ....... 0:00 ..... 1 ...... 1:00 ..... 1:00 ..... 1:00 ..... 0:00 ..... 0:00

10/15/2011 ..... 23:00 ..... 10/16/2011 ....... 3:00 ..... 1 ...... 4:00 ..... 4:00 ..... 4:00 ..... 0:00 ..... 0:00

10/15/2011 ..... 23:00 ..... 10/16/2011 ....... 6:00 ..... 1 ...... 7:00 ..... 7:00 ..... 7:00 ..... 0:00 ..... 0:00

10/15/2011 ..... 23:30 ..... 10/16/2011 ....... 0:00 ..... 1 ...... 0:30 ..... 0:30 ..... 0:30 ..... 0:00 ..... 0:00

10/15/2011 ..... 23:30 ..... 10/16/2011 ....... 3:00 ..... 1 ...... 3:30 ..... 3:30 ..... 3:30 ..... 0:00 ..... 0:00

10/15/2011 ..... 23:30 ..... 10/16/2011 ....... 6:00 ..... 1 ...... 6:30 ..... 6:30 ..... 6:30 ..... 0:00 ..... 0:00


Data is as follows :


START DATE : in column D starting from D5

START TIME : in column E starting from E5

END DATE : in column F

END TIME : in column G


Formulae are as follows :


Column H , starting from H5 : =F5-D5

Column I , starting from I5 : =G5-E5+H5*24

Column J , starting from J5 : =IF(H5=0,IF(G5>BONUS2_START,G5-BONUS2_START,0),IF(E5<BONUS2_START,G5+24-BONUS2_START,I5))

Column K , starting from K5 : =IF(H5=0,IF(G5>BONUS1_START,G5-BONUS1_START,0),IF(E5<BONUS1_START,G5+24-BONUS1_START,I5))

Column L , starting from L5 : =K5-J5

Column M , starting from M5 : =IF(E5<BONUS1_START,I5-L5-K5,0)


Column J - Bonus 2 Time

Column L - Bonus 1 Time

Column M - Normal Time


Narayan


P.S. This will not work if the start time is at midnight or thereafter , during Bonus 2 time.
 
Okay, I corrected for using the correct bonus times (sorry about that) and put in some more checks to determine what day the times are in. =P

=(1+$AJ$3-$AJ$2)-MAX(0,MIN(1+$AJ$3-$AJ$2,W23+Y23-W23+$AJ$3-(Y23+Z23)))-MAX(IF(Y23=W23,1,0)+X23-AJ2,0)
 
Hi Narayan

Just checked over your data there are a couple of errors as follows -

Lines with first two test times of 21:00 column L Bonus 2 Time should be 2:00 on both lines rather than 3:00,first line with test time 23:00 column L Bonus Time 2 should be 0:00 rather than 3:00

Thanks for taking the time to do the tests.

The start time after midnight and during bonus time is the problem and I believe if the finish time is after the end of Bonus Time 2 (i.e. after 06:00) it will not work either.

Unfortunately after midnight starts and after bonus2 time finishes are very common so I still have to solve this problem somehow

Thankyou for your input so far, any ideas on how to solve these problems Sam
 
Hi Luke

Sorry for the previous confusion on cells

I tried out the formula and it works for almost every start/finish except for examples as follows

Start date 17/10/11 time 16:00, Finish date 17/10/11 time 22:00, this input displays ###### but should be 00:00 i.e. start /finish times not in the bonus 2 period.

Start date 17/10/11 time 20:00, Finish date 17/10/11 time 23:30, this input displays ###### but should be 00:30 i.e. start/finish times in the bonus 2 period but finish time not after midnight

Thanks Sam
 
Sam,

Do we need to worry about extra long shifts? E.g, start work at 5:00, end at 23:30?
 
Luke

Funny you should mention this I had thought of including this in the last set of examples, the problem being you enter the bonus period twice?

These shifts would be a rare occurrence but not impossible although they would be restricted to weekend days Saturday, Sunday and Holidays.

If we ignored these I think it should be alright, I will content with this if happens in the future.

Sam
 
Hi Sam ,


Just got an idea , but since it's bedtime here , I'll just mention it here and you can make the calculations. This will work for all cases.


The date and the time can be added together to give you an absolute time e.g. 10/15/2011 + "09:00" becomes 40831.375.


All that we need to do is start off with two values :


1. Start_of_a_new_day : take any date , say 10/15/2011 and add "06:00" to it ; this is the beginning of a new day.


2. Midnight : add 18 hours ( "18:00" ) to Start_of_a_new_day


3. Convert all your start dates , start times , end dates , end times , bonus1 start time , bonus1 end time , bonus2 start time and bonus2 end time to these absolute values ; all the arithmetic becomes straightforward now.


Narayan
 
Hi Narayan

Will attemp to try this out over the weekend, but am not sure if my skills are up to it.

If you get a chance would you also give it a go.

Thanks again enjoy your rest Sam
 
As NARAYANK991 explained, the data and time is a decimal number made of 2 parts:

- The date itself is the integer part of this number

- The time is the decimal part of this number


So the data number 40831.375 is "15/10/2011 09:00:00" where

- The integer part (40831) is "15/10/2011" =
Code: 
DATE(2011,10,15)

- The decimal part (0.375) is "09:00:00" = [code]TIME(9,0,0)

So the full date and time = [code]DATE(2011,10,15)+TIME(9,0,0)


You can understand now why time= 0.375 = 9/24. It represents a fraction of a day in seconds.


Let's take a specific shift : [b]2230-0630 which can be understood as [b][i]06:30:00 of the next day[/i].


As the day is immaterial here, you can choose any: the [b]TODAY() function gives you automatically the day of to-day:

- The shift start day and time = [code]TODAY()+TIME(22,30,0)

- The shift end day and time = [code]TODAY()+TIME(06,0,0)+N(TIME(06,0,0)<TIME(22,30,0))


The last part of the formula explained:

- Shift_End_Time < Shift_Start_Time would be FALSE when they fall the same day

- Shift_End_Time < Shift_Start_Time would be TRUE if the shift crosses two days

- N()[/b] is a logical function transforming FALSE/TRUE into 0/1.

So when the shift crosses a day the condition is TRUE so N(TRUE) adds 1 day to to-day.


I gave the name "T_SHIFT"[/b] to your shift table with:

- Col 1: Smith 600-1430 ON....

- Col 2: Name (Smith)

- Col 3: Shift Start Date and Time = TODAY()+TIME(6,0,0)

- Col 4: Shift End Date and Time = TODAY()+TIME(14,30,0)

- Col 5: Availability Status (ON)

starting at B2.


The second table starts in B14. I named it "T_STAFFING"[/b]:

- Col 1: HOURS MIN Staffing Count (0600-1030 7 ??)

- Col 2: Shift Start Date and Time = TODAY()+TIME(6,0,0)

- Col 3: Shift End Date and Time = TODAY()+TIME(10,30,0)[/code]

- Col 4: Minimum Staff

- Col 5: Available Staff


The Available Staff is an ARRAY formula (R1C1 reference style) for each line of your second table:


{=SUM((RC3>=INDEX(T_SHIFT;;3))*(RC3<=INDEX(T_SHIFT;;4))*(RC4>=INDEX(T_SHIFT;;3))*(RC4<=INDEX(T_SHIFT;;4))*(INDEX(T_SHIFT;;5)="ON"))}[/code]


where:

- RC3 : Shift Start Date and Time = Col 2 of T_STAFFING

- RC4 : Shift End Date and Time = Col 2 of T_STAFFING


You enter the array formula in row 14 column 6 and you drag it to the bottom.


Please note that when you apply mathematical operator like "+" or "*" to logical conditions, it transforms the TRUEs and FALSEs as arithmetical 0/1.

So

(condition 1)*(condition 2)*(condition 3)*(condition 4)[/code]

is the same as

N(AND(condition 1,condition 2,condition 3,condition 4))[/code]


Hope it helps,


Jeanbar
 
Hi Jean ,


I may be wrong , but I get a feeling your post answers another question , whose link is :


http://chandoo.org/forums/topic/identify-minimum-staffing-based-on-shift-hours


Narayan
 
Narajan,


You are right! Thanks for pointing this out.


I tried to remove the extra text but failed to edit my post. (Sorry for that).


However, the beginning of the post do answer Sam's question (before the tables' description)
 
Hi Sam ,


I have posted a sample worksheet with the calculations. Please verify if it is OK.


https://skydrive.live.com/?cid=05ea03be521b84f8&id=5EA03BE521B84F8%21121#!/view.aspx?cid=754467BA13646A3F&resid=754467BA13646A3F%21109


Since the entire address does not appear as a hyperlink , instead of clicking on the hyperlink , you will have to copy the complete address and paste it in your browser.


In case you download it , I've set the zoom to 75 % ! Please increase it.


Narayan
 
Hi and thanks for all the hard work


Jeanbar allow me a little time to try to understand your comments and I will let you know how I get on


Narayan I downloaded your worksheet and checked through it and it seems to be fine except for the following exceptions - if you take the last line and change the finish time to any time after 06:00 it still adds on the extra time past 06:00 for bonus2 time, i.e. date 16/10/11 start 03:00 finish 09:00 bonus2 = 6 but should be 3.

I also changed line 1 to date 15/10/11 start 05:00 finish 09:00 bonus2 = 0 but should be 1


Is there somthing more thats needs to be added to the formula to allow this to be calculated for these times which extend past 06:00?


Thanks Sam
 
Hi Sam ,


I think the logic will work for only a 24-hour calculating period.


If a shift can start during Bonus2 time , and extend beyond 06:00 hrs. , then the calculation has to stop at 06:00 hrs. , and do a fresh calculation for the period beyond 06:00.


At present , the day goes from 06:00 hrs. on a date to 06:00 hrs. the next day. So if someone works from , say 23:30 on day 1 to 09:00 hrs. on day 2 , then the time of work from 23:30 hrs. on day 1 till 06:00 hrs. on day 2 will be calculated under day 1 ; the working from 06:00 hrs. on day 2 till 09:00 hrs. on day 2 will have to be calculated under day 2.


Narayan
 
Hi Narayan

I believe I understand the logic behind what you are saying, is there any way forward without having to split the days?


Sam


Jeanbar thanks for the information I do understand the logic after reading it a few times Thanks Sam
 
Sam,


Thanks for your comment.

The formula for available staff (based on the above information) is:


{=SUM((RC3>=INDEX(T_SHIFT;;3))*(RC3<=INDEX(T_SHIFT;;4))*(RC4>=INDEX(T_SHIFT;;3))*(RC4<=INDEX(T_SHIFT;;4))*(INDEX(T_SHIFT;;5)="ON"))}


CSE formula, of course!
 
You must log in or register to reply here.
Back
Top