Time Calculations

[BronwynAnne]

I am trying to create a DB to emulate a time card in order to check my paycheck. I need it to track Call, 1st meal in, 1st meal out, 2nd meal in, 2nd meal out and wrap. I can calculate a total hours worked, but I need to break down this total to calculate regular time, 1.5x, 2x and 2.5x. For instance, if I worked 13 hours, the first 8 would be at regular time, the next four would be at 1.5x and the last hour would be at 2x. Any suggestions on how to bread down this total?

Thanks!!

Bronwyn

[BobWeaver]

Rate1Hours = Max(0,Min(8,HoursWorked))

Rate2Hours = Max(0,Min(12,HoursWorked-8))

Rate3Hours = Max(0,HoursWorked-12)

[BronwynAnne]

THANK YOU!!!

[Lee Smith]

Hi Bob,

What did I do wrong,

For thirteen hours worked, the calculations return this:

Rate1Hours equals = 8

Rate2Hours equals = 12

Rate3Hours equals = 46788

Instead of this?

8

4

1

Lee

[Ugo DI LUCA]

Hi Bob, Hi Lee,

I've got 8, 5, 1 with this calc.

In fact, I found two different ways for 8, 4, 1...but I'm clueless which should be used

Rate1Hours = Max(0,Min(8,HoursWorked))

Rate2Hours = Max(0,Min(12,HoursWorked-9))

Rate3Hours = Max(0,HoursWorked-12)

or

Rate1Hours = Max(0,Min(8,HoursWorked))

Rate2Hours = Max(0,Min(4,HoursWorked-4))

Rate3Hours = Max(0,Min(13,HoursWorked-12))

[BobWeaver]

Actually Lee's calculation is best. It will result in the most take home pay.

Lee:

Maybe you defined HoursWorked as a Time field. I used a number field.

Ugo:

The formula:

Rate3Hours = Max(0,Min(13,HoursWorked-12))

will cut off after 13 hours. I assume that all hours after 12 will be at the maximum rate, so you don't want to have the Min() function there. Try it with values of HoursWorked more than 13 and you'll see what I mean.

[Lee Smith]

Hi Bob and Ugo,

I have tried Bob's calculations and still not getting what I should.

The attached file shows Bob's calculations and Record on should be 5.40 and Record 2 should be 13 hours.

Lee

TimeWorkBreakdown.fp5.zip

[Ugo DI LUCA]

Hi Lee,

Well you must have been very tired yesterday after working 650 hours in a row

Or I won't hire your services if you keep billing me 650 hours for an effective 6 hours work.

Just look at your calc and set it with HOUR(endthour) - HOUR(starthour) to get an accurate number result of the rounded portion of time your really spent on the Project.

Bob,

I had tried already to add-up some hours and all these calcs lead to the same result, with Min or Max.

When you set the hours worked to 15 you'll get the same result for your calc (modified to -9) and the second I gave, that is

8

6

3

[Lee Smith]

Hi Ugo,

Thanks for point out my error. However, I had a problem with the rounding of the hours, as most employers pay by the minute..

This attachment corrects that problem.

Lee

TimeWorkBreakdown.fp5.zip

[BobWeaver]

Well I guess I was tired when I posted those calculations too. My Rate2 formula was definitely wrong. This is what they should be:

Rate1Hours = Max(0,Min(8,HoursWorked+0))

Rate2Hours = Max(0,Min(4,HoursWorked-8))

Rate3Hours = Max(0,HoursWorked-12)

Sorry, I should have tested this more thoroughly.

One other thing, you should uncheck the 'do not evaluate if all fields empty' option. Otherwise you will get some wrong numbers if HoursWorked is completely empty. That is the reason for the +0 in the rate 1 formula.

BTW this will work for fractional hours. If your HoursWorked field is a time field, just divide by 3600 and round as necessary before putting it into the formula.

Lee, I didn't look at your attachment, so maybe this is what you've already done.

[BronwynAnne]

Good thing I checked back in. I was plugging in the original formula and was coming to the same conclusion about the formula. Thank you very much for your help, I really appreciate it.

Bronwyn

[Lee Smith]

Hi Bob,

Thank you, I was beginning to think I was going nuts.

Think???

Anyway, this works great.

Lee

[BronwynAnne]

New conundrum: Difference in Work Week and Pay week.

Ok, let's say WEEK 1, I work Monday - Sunday. That's 7 days with Saturday as 1.5x and Sunday as 2x pay. My timecard for WEEK 1 lists all 7 days. I am paid the following week ( WEEK 2) for Monday - Saturday (Saturday @ 1.5x) and the following week's paycheck (WORK WEEK 3) has Sunday @ 2x pay.

Now... WEEK 2: I work Monday - Friday at Reg Pay, off Saturday, but work Sunday as a 6th Day @ 1.5x pay. I am paid during WEEK 3 for WEEK 2 Monday - Friday @ reg pay and WEEK 4 will have WEEK 2's Sunday.

What I am trying to figure out is how to best approach building my database. Currently I have a single database for a record for each week and fields for each day. As each record represents a week as Monday - Sunday, but my checks reflect a Sunday - Saturday week, I can't seem to figure out how to track it. Also with a pay calc that is dependant changing variables, I'm not too sure how to proceed.

I know I'm asking a lot, but if anyone has any suggestions I would really appreciate it.

Thank you!

Bronwyn

[BobWeaver]

The short answer is, either change the way you do your payroll so that both the work week and pay week start and end on the same days, or else have one record per day.

If your pay week and work week start and end days coincided, then there would be no problem, and having a record represent one week would be fine. But, since there is an offset between pay week and work week, the simplest solution from the database structure aspect is to have one record per day. Then it's simply a matter of doing the correct find/sort/report script to generate the payroll. In database-speak it's an issue of granularity; i.e., having your data broken down into small enough pieces that you can work with them conveniently, but no smaller than necessary.

Trying to generate a report by combining data from part of one record with a different part of another record will be a nightmare to set up. And as your database evolves over the years, every change that you have to make to it will become an even bigger nightmare. So, take the time now to structure things properly, and you will make your life much easier in the long term.

[BronwynAnne]

Added Question:

Having applied all of your wonderful suggestions above, let's try on this scenario:

Let's say that between the hours of 8pm and 1am a 10% Night Premium fee is added to the CURRENT pay rate and between the hours of 1am and 6am the fee is 20% of the current rate.

For example:

I clock in at 9am and clock out at 2am.

From 9am - 5pm my rate is regular time. (1x)

From 5pm - 9pm my rate is time and a half (1.5x)

From 9pm - 2am my rate is double time (2x)

However from 8pm - 9pm night premium earnings are tacked on at 10% of the current 1.5x rate

From 9pm - 1am the night premium earnings are adjusted to reflect the 2x rate.

From 1am - 2am the fee changes to 20% of the current rate of 2x.

Any suggestions on how to tackle this problem? My database has changed very little since my initial posting aside from incorporating your wonderful solutions.

Thanks!!

Bronwyn

[QuinTech]

Just jumping in here... my apologies to Brownyn, Bob, Lee, and Ugo if i muddy the waters...

PayForDay = PayRate * HoursPerDay

HoursForDay =

Case ( QuitTime - StartTime <= 8 , 8 ,

QuitTime - StartTime <= 12 , ((QuitTime - StartTime) - 8)*1.5 + 8 ,

((QuitTime - StartTime) - 12)*2 + 4*1.5 + 8 )

That's the basic OT calc, before figuring in 10% and 20%. If you're going to get that complicated, the resulting calc _might_ be long enough that you'd be better off making each day a record unto itself, in a separate file.

Jerry

[BronwynAnne]

I already have two databases working together. On has a record for each day and the other is a portal broken down by weeks. The relationship between the two is a field called "Week Ending". I've already figured out the overtime problem (thanks to the wonderful suggestions posted above).

Thanks!

Bronwyn