Is there a way to have a field calculate and display time and half pay from an hourly base pay field?
OK, np.
It's similar to Excel. Just make a new field of type formula numeric and the formula would be like
[base hourly pay rate] * 1.5
You probably have to adjust to your own field name unless I guessed correctly. There is a field picker at the bottom of the formula box where you can choose from your existing fields
Apologies, let me try to clarify - I have a formula field that will calculate the Total OT Pay based on the Total Hours worked * OT Pay rate...but I also need to calculate based off of an exempt type employee who receives a per-diem rate. I'm not sure how to have the Total OT Pay reflect how much a per-diem person will receive.
is there a way to calculate the total pay based off the Total OT hours field and a field that calculates the OT pay rate vs using a standard work week info?
Qrew LegendSorry but I'm not really understanding your question. What does this mean?
vs using a standard work week info?
Thank you, this is very helpful! My current fields for entering in the date and time is the Date/Time field - should I separate these out and have 2 different fields for the date and time?
Qrew LegendI think it's a better user experience to have separate fields as the syntax for entering the date and the time into a daytime field it's a little tricky.
In a date field you can enter a T and today's date will populate the field. Once you do the T, you can walk that date backwards and forwards using the [ and ] keys.
Also if your employees are going to clock in and out on this form then if they type an N into a time field (like N for now), it will populate with the current time.
It's a duration field, but now I've also realized I need to calculate hours between date/time fields.
I have 2 date and time fields for each day of the week for the user to enter in the date work and time started and another field to enter the date and time work stopped - for each day of the week...how do I total those fields in my TOTAL Hours of OT Worked Duration field?
Qrew LegendMy suggestion is to avoid using Duration field type where possible. Your life will be easier if you coax all the calculations to be numeric hours.
Sounds like you have a single record where the employee enters their hours for the days they worked that week. That in fact is a very clean way to do it from a Quickbase point of you because all the data you need us in one record.
I would suggest making five formula in numeric fields like this:
Field Name: [Day 1 Hours]
Nz(ToHours(
ToTimeStamp([Day 1 Date End ],[Day 1 Time End])
-
ToTimeStamp([Day 1 Date Start],[Day 1 Time Start])))
// the Nz says to treat a null value as zero
Then the formula for [Total Hours Worked] is just
[Day 1 Hours]+ [Day 2 Hours] + ... etc [Day 7 Hours] .
He would show that field on the form right beside where the users entering their start and end dates and times
Then you have a field for [Standard Weekly Hours] make that field be a numeric field type, and your setup might be that it's just a formula field with formula of say 40. Maybe it varies by the State that the worker is in or maybe it's a value which is locked up from the employee record. But best if its a numeric field type.
Then [OT hours] would be
Max(0, [Total Hours Worked] - [Standard Weekly Hours])
The above formula takes the hours worked minus the standard hours but if the result is smaller than zero then it's going to take the maximum (ie the higher) of zero and some negative number meaning that this can never be negative.
Then the [OT Pay] will be
[OT hours] * [OT Pay Rate]
The zero at the end redlines and highlights yellow with a message saying expecting duration but found number? the field is a formula numeric field
Qrew LegendTry this
If([TOTAL Hours Worked] > [Standard Work Week],
ToHours([TOTAL Hours OT Worked] )* [OT Pay Rate],
0)
If that does not work, please post your formula and let me know the types of the fields that are involved. My problem is that I'm guessing whether your fields are Duration field types or Numeric field types
I've tried this formula, but it doesn't seem to be working...
If([TOTAL Hours OT Worked] > [Standard Work Week],
([TOTAL Hours OT Worked] - [Standard Work Week]) * [OT Pay Rate],
0)
Qrew LegendTry this
If([TOTAL Hours Worked] > [Standard Work Week],
[TOTAL Hours OT Worked] * [OT Pay Rate],
0)
Thank you! So, another issue I've ran into is trying to find the best way to calculate the total pay the employee was paid based off the Total hours worked...
I have a formula duration field that shows the total hours of OT worked based off work start and end times - not sure if I can use the Total Hours of OT worked to calculate the pay based off my OT Pay Rate field that is capturing my time and half pay?
I currently have a field where the user enters in their base hourly pay and the overtime rate is at least 1.5 time the amount of the base hourly pay rate.
Qrew LegendOK, np.
It's similar to Excel. Just make a new field of type formula numeric and the formula would be like
[base hourly pay rate] * 1.5
You probably have to adjust to your own field name unless I guessed correctly. There is a field picker at the bottom of the formula box where you can choose from your existing fields
Qrew Legend... you might want to round the rate to the nearest penny.
Round([base hourly pay rate] * 1.5,0.01)
Qrew LegendYou can say it, we can do it. Can you give an example of what the rules are for paying overtime and what the overtime rate is.
