Dynamic payroll system which calculate total hoursof regular, night shift, overtime and overtime night shift.

[papaje]

Hello Masters,
Can somebody advice me how to do this payroll system which will look like this

[TABLE="class: grid, width: 680"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Date[/TD]
[TD]Time In[/TD]
[TD]Time Out[/TD]
[TD]Regular[/TD]
[TD]Night Shift[/TD]
[TD]Regular Overtime[/TD]
[TD]Night shift Overtime[/TD]
[/TR]
[TR]
[TD]j doe[/TD]
[TD]04/04/2013[/TD]
[TD]14:00[/TD]
[TD]01:00[/TD]
[TD]7[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]

Regular rates starts from 6am to 10pm. Night Shift with differential rate from 10pm to 6am. Overtime starts at the tenth hour of duty to be counted as 1 hour OT. 1 hour break time every after four hours. The purpose of this is to calculate properly employee salaries from different rates.

 

[Akashwani]

Hi and welcome to MrExcel.

You may want to take a look here....

Excel Magic Trick 286: MOD function & Time Calculations (Time For Night Shift, or Negative Time) - YouTube

And here.....

Working With Times Example Page


The following is sample data and example answers, I am NOT suggesting that this is the solution you require.
All I am doing is showing you how you may want to set your data up and the complexity of getting the formulas constructed based on the different criteria you have.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Name	Date	Time In	Time Out	Regular	Night Shift	Regular OT	Night Shift OT	Total Hrs	Total Hrs - Break		Start of Day	Night Rate	OT Hrs	Break
2	Bill	01/04/2013	2:00 PM	1:00 AM	7	1		2	11	10		6:00 AM	22:00	10	01:00
3	Ben	01/04/2013	10:00 AM	10:00 PM	10		1		12	11
4	Bob	01/04/2013	12:00 PM	1:00 AM	9	1		3	13	12
5	Tim	01/04/2013	6:00 AM	6:00 PM	10		1		12	11
6
Sheet11

The formulas need to be copied down.
I have NOT covered all the different hours that may be worked, the above is just a brief pointer for you.
I strongly suggest that you watch some of the videos that Mike has on Time and Payroll, just type Night Shift, Payroll or Time into the search box and all relevant videos will appear.

I hope this helps, I'm sorry that I couldn't have been of more help, but the above is the extent of my knowledge on this.

Good luck.

Ak

 

[papaje]

Thanks Sir AK! This is a great help. A good point of view to start on with. Thanks you for all the resources. These are cool!

 

[Akashwani]

Hi,

I'm pleased that you found this helpful and thanks for the feedback.

Ak