How can I calculate various rates of pay on a time card

**jpmanfredo** · 01-18-2013, 06:29 PM

Please refer to the attached file. I'm trying to concoct a formula that considers the fee table above, while considering the times associated with the fee schedule, and then considers the in/out times and applies the appropriate total to the charge column. Also, the formula would need to consider the accumulated hours over 40 at 1.5 times the appropriate rate based off of the in/out times.

It's as confusing to explain as it is to try and create. I just registered, but I refer to this site regularly....genius aplenty here. Looking forward to your thoughts. Thank you! Dazed & Confused

**BB1972** · 01-18-2013, 08:39 PM

jpmanfredo, welcome to the forum.

That's a lovely picture, but there's not very much we can do with it. You should upload a sample workbook, with any private or confidential data removed.

**jeffreybrown** · 01-18-2013, 08:41 PM

We would love to continue to help you with your query, but first, before we can proceed, please see Forum Rule #1 about proper thread titles and adjust accordingly...

To change a Title on your post, click EDIT then Go Advanced.
If 2 days have passed, ask a moderator to do it for you.

**jpmanfredo** · 01-24-2013, 09:47 AM

OK...I've updated the title to comply with the forum rules and uploaded the file. Any suggestions or direction on how to tackle the issue? Thanks in advance.

**kcarley** · 01-25-2013, 12:47 PM

Hi! I think I see 2 places where you may have blocked yourself from finding the next step. Try not to worry about the Daily and Accumulated values just yet. I think they may be in your way. Basically you'd want to know the hours used for each range first. This is where the second hang up would catch you, if you don't adjust your 'Hours' & 'Rates' table to follow the same order as your time card you are going to go nuts trying to keep up. Better organization could make it easier to copy a calculation down or across to make it work for more than one situation.

Worry about presentation later, after you've solved the calc.

Good luck with the 1.5 over 40 because you will need the logic on which rate to apply if it is worked through more than one range of hours. For instance are only the extra hours considered for the higher rate or is it a weighted average of all the rates worked. Based on this I'm not working overtime unless it's in the higher rate. LOL

Let us know how you make out!

Regards,
K

**kcarley** · 01-28-2013, 03:25 PM

@jpmanfredo

How are you making out with this? I took a long hard look at your rate schedule and I have to say I'm just a bit confused with finding which days to use the 1900 cutoff and the 2300 cutoff. Would you be able to provide an example of correct values for your output? Even if it is just done by hand for now it will help with how to apply the rates.

Either that or if you could restate the rate table with specific days... Here's an example that may or may not work for what you need:

Day	When	Rate
Friday	Before 0700	46
Friday	Before 1900	40.75
Saturday	Before 0700	46
Saturday	Before 1500	47
Saturday	Before 2300	47
Sunday	Before 0700	47
Sunday	Before 1900	46
Monday	Before 0700	48
Monday	Before 1500	40.75
Monday	Before 2300	40.75
Tuesday	Before 0700	46
Tuesday	Before 1500	40.75
Tuesday	Before 2300	40.75
Wednesday	Before 0700	46
Wednesday	Before 1500	40.75
Wednesday	Before 2300	40.75
Thursday	Before 0700	46
Thursday	Before 1500	40.75
Thursday	Before 2300	40.75

**jpmanfredo** · 01-30-2013, 02:37 PM

Good advice...For some reason I’m now heading down the path of converting the in/out times to minutes and then trying to group those aggregate minutes under the appropriate header based off of the rate table. We’ll see where this goes…thanks again for the direction and I’ll keep you posted.

**kcarley** · 01-30-2013, 03:03 PM

Glad to help (don't forget the stars at the bottom left of the posts that help).

If you want to validate the rate table in post #6, I have a solution all but ready to post. I'm not sure there's a need to convert to minutes because the math will work itself out based on your use of the "MOD" function. The only other thing that you'd need is a logic to contain the overtime rule so that the math that is applied follows the business intent of overtime.