• 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

Hi Sam ,


I think you need to make only a minor change. It all depends on what your rules are :


Suppose a person works from 03:00 hrs. till 09:00 hrs. ; do you treat the time from 03:00 till 06:00 ( assuming Bonus 2 end is 06:00 ) as Bonus 2 time , and the time from 06:00 till 09:00 as normal time ? Or is it all Bonus 2 time ?


Narayan
 
Hi Narayan


The time from 03:00 to 06:00 is treated as bonus2 time and the time from 06:00 to 09:00 will be treated as normal time.


Sam


Jean I believe your formula is for the other post
 
Hi Sam ,


In that case , I think the problem is solved by changing only the formula for calculating Bonus 2 Time.


In the worksheet which you have already downloaded , change the formula to the following :


=IF(NOT(K5),0,MIN(Bonus_2_End,(F5+G5))-MAX((D5+E5),Bonus_2_Start))


Narayan
 
Hi Narayan

I have tried it with a number of different starts and finish times and it seems to work fine. I changed the Start Date to 20/10/11 and End Date to 21/10/11 and it did not work as I thought it might not. Is there any way not to keep changing the start and finish bonus dates every time I make an entry, can the formulas be modified to take that info from the dates as entered for Start Date and End Date.

Many Thanks for all your work to date

Sam
 
Hi Sam ,


Actually , all the constants ( Start of a new day , Midnight , Bonus 1 Start , Bonus 2 Start , Bonus 1 End and Bonus 2 End ) are derived from the first date that is entered ( D5 ) ; if the data entry is changed to 20/10/2011 and 21/10/2011 , all the constants will change automatically. You don't have to enter them.


The only problem is that you can enter only one set of dates at a time ; within the worksheet , if you have a range of dates ( say 20/10/2011 through 25/10/2011 ) , then the formulae will not work.


If your worksheet is going to have this kind of data , then I think it would be better to have a macro , which when run , will do all the calculations , and fill in the calculated values in the three columns.


Narayan
 
Hi Narayan

You're right, sorry I changed the dates on the last line so it did not work.

Anyway thanks for all your effort and work I will see if I can modify the way data is recorded and use your formulas for the spreadsheet. If it does not work out I may have to try the macro (not much experience on this I'm afraid), will let you know how I get on. Keep a watch for my post although it may take me a while!

Thanks Sam
 
Hi Sam ,


Thanks for the feedback. Hope you manage to crack it. Post your solution here as and when you do.


Thanks for the interesting problem.


Narayan
 
Narayan

Just looking at your spreadsheet, can you explain to me how cell E1 (=Bonus_2_Start)finds the data in cell C2. The reason I was asking was I was going to put a Bonus1 Start and End , Bonus 2 Start and End, Start of new Day and Midnight cells on each row and use them for each seperate entry, but when I copy the formulas down the =Bonus_2_Start will always reference back to the first line. I think I am just brain dead with this at the minute, its probabley straight forward but just can't see it.

I am using Excel 2003 SP3 and your spread is being converted so I can view if it makes any difference.

Thanks Sam
 
Hi Sam ,


The following ranges have all been named using the Name Manager :


1. Start_of_a_new_day - refers to : =Sheet1!$H$1


2. Midnight - refers to : =Sheet1!$H$2


3. Bonus_1_Start - refers to : =Sheet1!$C$1


4. Bonus_2_Start - refers to : =Sheet1!$C$2


5. Bonus_1_End - refers to : =Sheet1!$E$1


6. Bonus_2_End - refers to : =Sheet1!$E$2


1 is calculated using the date in D5 ; 2 depends on 1 ; 3 and 4 are calculated using the data entered in cells B1 and B2 , and the value of Midnight.


5 is put equal to 4 , and 6 is calculated as 24 hours after 1.


I think entering all of these for each entry may not be required ; you need to have a new entry of 1 , whenever data for a new date starts. Based on this , the others will be calculated.


You will find all the relationships by using the TRACE PRECEDENTS feature.


Can you upload an actual worksheet which contains data for several dates ?


Narayan
 
Hi Narayan

Security policy at work does not allow uploads it will be at least 9hrs before I can do this from home. I have never used Name Manager before so will have to check this out.

The worksheet I am using is the one you uploaded, with the bonus2 formula modified as per your post and then all I have done is move the items 1 to 6 listed above to the left hand side of the Start Date and copied them down. I looked at Trace Precendents and found that cells containing =Bonus_2_Start and Start_of_a_new_day+"24:00" all referred back to the fist line and not to the lines that I had copied down.

You must be fed up with this one by now!!

Thanks for all your help

Sam
 
Hi Narayan


Link for uploaded file below copy and paste into your browser and click on download file


https://rapidshare.com/files/4269291604/Overtime.xls


Sam
 
Hi Sam ,


Please download the modified worksheet , and verify the results.


https://skydrive.live.com/#!/view.aspx?cid=754467BA13646A3F&resid=754467BA13646A3F%21111


Narayan
 
Hi Narayan


As before cannot download this until I get home, will be a while before I see your worksheet and verify.


Thanks in the meantime


Sam
 
Hi Narayan


The worksheet seems to work fine and I can add new times as required.

Thanks for all your effort on this, it is very much appreciated.

I have a bit more to do to the sheet before it can calculate the salaries for the Overtime but that should be easy enough I am hoping

If I come across anything else on this that gives me a problem I will let you know.


Thanks again Sam
 
You must log in or register to reply here.
Back
Top