Timesheet and Overtime Calcs

[andymarkbrown]

Hope someone can help on this (basic) query.

I have a timesheet function for 10 employees. Each employee creates multiple entries throughout the day as single records These entries have start_time and finish_time based on a 7.5 hour working day. I then have a trailing grand summary showing total time.

I also have a calculation field in the grand summary which shows overtime hours based on the user doing more than 7.5 hours per day (total_time_overall - Time ( 7 ; 30 ; 0 )) . This however only works for a single user on a single day.

I would like to be able to view time entry records for a given employee over a range of dates (eg 1/2/05...1/3/05) and see how much overtime has been worked. I realise I can maybe do this using sub summaries, but these will only show up when I preview or print and I would like to see them in browse mode as a grand summary. Is there a better way for me to configure the overtime calculation or do I need to be looking at a different solution?

Thanks in advance.

[Ender]

I'd recommend adding another table where each record is one employee's work day. In this table, you can use a sum() function in a calc to add up the related hours for that employee for that date. If the sum is greater that 7.5, you'll have 7.5 hours at the regular rate, and whatever remainder at the overtime rate.

You may still wish to print the timesheets from the hours table, as this will probably print the best. If you do, you can reference the overtime data from the Work Day table. This overtime info could also be viewed in Browse Mode.

For totaling this up for a date range, you could use sub-summaries like you have now, or you could add a Payperiod table, where each record is one employee's payperiod. This can be useful if your payperiods are regular weekly, bi-weekly, or semi-monthly intervals, and you wish to show the totals in real time while the users are entering hours. The attached screenshot show how this might look. You create the payperiod records the first day of the payperiod, then use that as the interface for editing the hours.

What I ended up doing for our timesheet system, is just use a Payperiod table and an Hours table. I skipped the Work Day table, and instead subtotaled hours by day in fields in Payperiod. Because my interface only allows entry in one day at a time, I could set the total field for the current date with the total related hours. Also, our overtime is calculated by hours in a week instead of by day: anything over 40 hours in a week is overtime (although not for Exempt employees like me.)

Picture1.pdf

[comment]

Perhaps this can be viewed as an issue of de-duping a portal - see attached.

timeSheets.fp7.zip

[andymarkbrown]

Thanks for this one. The screen shot and info helps clarify it for me. I'll get cracking and see how it goes.