Calculating Payroll Hours

[Jarvis]

I am having some problems figuring out how to automate payroll calculations.

The workers record their hours each day on a timeclock.

They punch start & stop times before lunch and start & stop times after lunch. There are two starts & two stops each day.

The data from these time cards is presently entered into an MS Excel spreadsheet.

A typical card would look like:

08:15

12:10

01:08

05:14

The problem is that excel needs to know whether the time is AM or PM to make the calculations work. I enter the times as digits only but then have to look at each entry to surmise whether or not it is AM or PM.

Is there a way to make filemaker evaluate the different entries to conclude whether or not they should be listed as AM or PM?

Or does anybody know of a plug in or template that could do this for me?

Thanks,

Jarvis

Is there a way for filemaker to evaluate the entered digit and know whether

[comment]

does anybody know of a plug in or template that could do this for me?

A crystal ball would be very handy for this. Or, you could just make the two 'after lunch' fields automatically add 12 hours to whatever is entered.

excel needs to know whether the time is AM or PM to make the calculations work.

Does it really matter? From 01:08 to 05:14, that's 4 hours and 6 minutes - same as from 13:08 to 17:14.

[LaRetta]

Does it really matter? From 01:08 to 05:14, that's 4 hours and 6 minutes - same as from 13:08 to 17:14.

But what if they took an early lunch and their afternoon work schedule started at 11:30 - 5:00?

I would use timestamps in FM, letting the date/times be set by script. Then, in case someone had to pull an over-nighter as well, there wouldn't be an issue. :wink2:

[Lee Smith]

Hi Jarvis,

By chance, does this information start out as a timestamp. i.e. all in one field? something like, 2008-12-13 08:00:00.

Lee

[comment]

You could make the calculation of elapsed time account for that. But eventually there's no fool-proof way to surmise what an ambiguous entry was meant to be. Why not let users indicate PM expressly?

[LaRetta]

The data from these time cards is presently entered into an MS Excel spreadsheet.

Sorry, I mistakenly thought the process currently was Excel but that you were switching to FM for timecard entry instead (where you could have users click a start/stop button).

As said, there are many ways a User can mess up this type of manual entry. Do they always use a colon or do they sometimes just write 8 for 8:00 AM? Any FM calc will have to account for a lot of potential errors including illegal characters and such.

[Jarvis]

LaRetta, (and all)

You have this right. I am currently using excel.

The process starts out with a conventional timeclock in the shop & cardboard timecards. The workers punch the timeclock and the value is stamped. I read the value and manually type it into excel.

I would rather take a beating than do anything related to bookkeeping. I've been meaning to migrate this to filemaker for several years but each time I've been confronted with the opportunity I found something more pleasant to do.

The excel process takes between 20 & 30 minutes twice a month and the filemaker programming looked more formidable than that. I was just hoping somebody could point me to an already done solution for this.

Timestamps would be a very simple way to do this but the reason we rely on the mechanical device is periodically the computer system is down for one reason or another and payroll is something that cannot be fallible.

You are also right that the weakspot is in migration from AM to PM hours (or vice versa).