Calculating Hours for time tracking and invoicing

[ryancooper]

Hi!

I'm using an airtable base to calculate my working hours as a freelancer who works in live entertainment and I'm trying to figure out the best way to have a flexible system for different employers' rules for minimum paid hours of a shift, after how many hours before OT and DT, and also how some allow for DT after midnight. There is also the possibility with some for Sundays to be considered "premium days" regular time=time and a half all day. Right now there is a tick box per "shift" record that allows me to say this shift is "premium".

Right now I have a table of "billing rules" with lookup values for each parameter.

Each shift is linked to a "billing rule" record, and these values are lookups. The lookup values are then used to determine how many hours fall under each.

The formulas to determine how many hours fall into each bucket involve a bunch of IF functions. If the billing rule is "flat rate" these hours are set to 0 and the total is overridden. Here is each:

The total billing for the shift is calculated with this formula:

If you made it this far thank you! What I'd like to accomplish is adding a start and end time(say 12am-6am) for what hours will be in double time to the "billing rules" table, and then change the formulas determining how many hours fall into regular/OT/DT to be smarter and take the times into account. Extra bonus if there is a way to have a multi select in the "billing rules" for which days of the week are premium days, then use the start date/time of a shift to determine if the shift is on one of these days and adjust the calculation accordingly.

Thank you!
Ryan

[TheTimeSavingCo]

Hmm, it all sounds doable, but it's hard to say without having a couple of concrete examples of what you'd like to happen

If you could provide a read-only invite link to a duplicated copy of your base with some example data that would be very helpful!  If you could also provide some example data in there with the expected output that would be useful too.  For example, you say that you want it to take times into account, but what does that mean in practice?

[ryancooper]

Hey Adam!

Here is the link to that base. I disabled the formulas that determine the hours calculated in regular/ot/dt and manually entered the expected results. There is a notes field where I wrote some notes elaborating on the expected result.

I also added some new billing rules and examples of the fields that would determine the start and end of double time, as well as which days are premium days.

https://airtable.com/invite/l?inviteId=invxucdAOgO8SJnk3&inviteToken=f6d8a3b81500f7e7950e9a6c574219c...