I have a time card database which employees clock in and clock out of.

 

Currently the fields TimeStart and TimeEnd hold the exact time that someone arrives and 'clocks in or out'.  The problem with this is that each employee has a standard starting and finishing time and overtime is really only meant to be paid when overtime has been requested or required on a job. Coming in 10 minutes early to work does not constitute overtime.

 

So I would like to simplify the calculation of total hours for the day by rounding the TimeStart and TimeEnd fields to 15 minute intervals - rather than just rounding the total time spent for the day.

 

In this way - if someone clocks in 10 minutes early (eg 7:50am), the start time will still round to 8am, but if they clock in a full 15 minutes early they will clock in at 7:45am. So i guess the rule would be that if the normal starting time is 8am, if they clock in any time between 7:46 and 8:00am, the start time rounds to 8am. 

 

If the employee is late and clocks in at 8:05 instead of 8am, then the same rule will apply - and the start time will round to 8:15am.

 

Different employees have different set starting times so I think i will also need to have a table attached to employees which lists standard start and finish times and total hours for week.

 

Can anyone help me with a formula for rounding in this way please?

In this way - if someone clocks in 10 minutes early (eg 7:50am), the start time will still round to 8am, but if they clock in a full 15 minutes early they will clock in at 7:45am. So i guess the rule would be that if the normal starting time is 8am, if they clock in any time between 7:46 and 8:00am, the start time rounds to 8am.

 

What if they clock in at 7:35? Will you still round this up to 7:45? If yes, then the "standard" start time is irrelevant - you are simply rounding the actual start time up to the nearest quarter hour =

Ceiling ( StartTime / 900 ) * 900

The problem with this is that each employee has a standard starting and finishing time and overtime is really only meant to be paid when overtime has been requested or required on a job.
As per your question, you need to define the EntryTime and ExitTime on the user table, it should be predefined.
Also you need to define global fields, which will store UserId, EntryTime and ExitTime.
Then in script,
Now, you should use the step SetField in your script, using Case statement in solution,
here you need to compare the EntryTime stored in Global field, with the exact EntryTime which is associated with the user. And round it however you want.

Here is the logic, if you need full calculation, then you need to send a demo file to me.

Comment - yes you are right - the standard time is irrelevant -  all times will be rounded to 15 min, so if an employee logs in at 7:35am (when their normal start time is 8am), the start time will be rounded up to 7:45am.

 

Can i ask what Ceiling is?

One more question - for the TimeEndRounded, how do I alter the calculation so that it rounds down to the nearest 15 mins?

 

Many thanks

Ceiling() is a Filemaker function:

http://www.filemaker.com/12help/html/func_ref3.33.25.html#1028722

 

And so is Floor(), to answer your other question:

http://www.filemaker.com/12help/html/func_ref3.33.30.html#1028941

Ah! There is obviously an opportunity for a Wall() function in FileMaker.

 

Any idea what it would do?

I believe you may have opened a door to something...