Shift Loading formula

[map26]

Hi All,

Just wondering if you could help me out. Im trying to create a table that will work out the hours worked and then rounded to the nearest 15 minutes when the employee will log in then log out, a lunch break, total hours, hours -lunch and then the loading hours (15% and 30% between a specific time).

This is what i have so far but i can get the Loading to work correctly. Any help would be appreciated, i think i the 15% loading formula is incorrect but i cant work out where its gone wrong as it works fine for the 30% mark. i also have issues in the formula when it goes onto the next day.

Formula as follows:
Login: login time (h:mm)
Round Login: =ROUND(B3*96,0)/96
Logout: logout time (h:mm)
Round Logout: =ROUND(D3*96,0)/96
Lunch: lunch 0:30 (h:mm)
Total Hours: =(E3+(C3>E3)-C3)
Hours: =IF(B3<1,((E3+(C3>E3)-C3)-F3),0)
Loading 15%: =IF($C3>0,IF($C3>=$M$2,$E3-$C3,IF($E3>$M$2,$E3-$M$2,0)),IF($C3<$M$2,$N$2-$M$2,$N$2-$C3))
Loading 30%: =IF($C3>0,IF($C3>=$M$3,$E3-$C3,IF($E3>$M$3,$E3-$M$3,0)),IF($C3<$M$3,$N$3-$M$3,$N$3-$C3))

Loading
Loading 15% and 30%: just values
Start and end are the times for loading



Thanks again.

 

[Sanjeev1976]

Can you please be precise as to what is Loading time 15% and 30%

 

[map26]

Can you please be precise as to what is Loading time 15% and 30%

Sorry, it has to do with shift loading at the when billing the client and paying for staff. During shift loading 15% between 13:00 and 20:59 and additional 15% is charged and 30% for shift loading 30%.

I just need to work out how many hours they work in shift loading 15% and 30% shift times.

i should have named them something else as to not get confused.

 

[Sanjeev1976]

Sorry, it has to do with shift loading at the when billing the client and paying for staff. During shift loading 15% between 13:00 and 20:59 and additional 15% is charged and 30% for shift loading 30%.

I just need to work out how many hours they work in shift loading 15% and 30% shift times.

i should have named them something else as to not get confused.

Use

Excel Workbook
	I	J
2	Loading 15%	Loading 30%
3	8:00	1:15
Sheet1

 

[map26]

Use

Excel Workbook
	I	J
2	Loading 15%	Loading 30%
3	8:00	1:15
Sheet1

Thanks so much, its just about perfect how ever if im not sure how to calculate it if the shift goes past midnight onto the next day, it comes up with a zero answer.

 

[Sanjeev1976]

Try :

Excel Workbook
	I	J
2	Loading 15%	Loading 30%
5	7:59	7:59
Sheet1