how do I have QuickBase automatically add [accrued] set of hours to a [total hours] field on the first of every month?

  • 0
  • 1
  • Question
  • Updated 2 years ago
  • Answered
I have a table where employees track their hours during each month. They are awarded PTO hours at the beginning of every month, which is added to their total hours available field. How can I add their accrued time to their total hours automatically on the first of every month?
Photo of Gregg

Gregg

  • 90 Points 75 badge 2x thumb

Posted 2 years ago

  • 0
  • 1
I suggest using a formula to calculate their accrued PTO based on their month of service.
Photo of Gregg

Gregg

  • 90 Points 75 badge 2x thumb
Thank you for the quick answer. I did create a formula that displays their monthly accrual rate based on their date of hire (since 5 yr employee accrues less than a 10 yr employee). I want to add that accrual automatically each month to their running total.
For example: An employee has 200 hours of PTO in March. Each month they accrue 20 hours of PTO. So on April 1st the 20 hours will be added to their 200 hours...and on May 1st...and so on. I tried a formula that said if the FirstDayOfMonth(Today()) field is equal to (Today()) field then add [Accrual] to [Total Remaining PTO Hours]. The problem is the formula only works on that specific day. I need the employee to see [Total Remaining PTO Hours] all month long with the additions...
I am only smart enough with this to be a bid dangerous. You guys are so more advanced than me on this. I hope I explained my issue clear enough. Thank you in advance for any assistance you can provide.
What I am saying is to make a formula which will ways have the value of the Employees PTO based on their hire date.  So maybe the formula calculates to 1 day per month worked for the first 5 years of service and then goes to 1.5 days per month  for years 5 to 10.

So, for an EE who has worked say 70 months it would calculate their employee PTO today, which would work out to about 85 days or so.  So that formula would always have the correct value.

If you agree and need help in writing the formula, then you will have to explain what the PTO eligibility rules are.
Photo of Gregg

Gregg

  • 90 Points 75 badge 2x thumb
oh I see! Perfect. Yes help with the formula.
1.  So when they enter in their date of hire and save, [Service Years] field calculates:
Floor ( ToDays(Today() - [Member - DOH]) / 365)

2.  They start 2017 year off with a specific amount of PTO Hours: [Starting PTO]
If([Service Years]>=18, 200,
[Service Years]>=10, 160,
[Service Years]>=5, 120,
[Service Years]>=1, 80, null)

3.  Here is the formula I used for [Accruing PTO] field: (The second number is the number of hours accrued each month for each level of service).
If([Service Years] >=18, 20,
[Service Years] >=10, 16.64,
[Service Years] >=5, 13.36,
[Service Years] >=4, 12.64,
[Service Years] >=3, 12,
[Service Years] >=2, 10.64,
[Service Years] >=1, 10, null)

4.  In the form if they use PTO it is added into the field [PTO Hours Used]. This would be subtracted from the formula you are suggesting to give them their available bank.

If you would like to have access to the application to get a better understanding just let me know.

Thank you Thank you Thank you!!!
It seems to me that you have already done most of the work.

There would be a new field created for say [YTD PTO Accrued]

The formula would be something like this

 var number MonthsElapsed = Month(today());  // I make the assumption here that you allow the EE to take January's new PTO off anytime in January, otherwise they would never have a chance to take off their December PTO.


[Starting PTO] + $MonthsElapsed * [Accruing PTO]
Photo of Gregg

Gregg

  • 90 Points 75 badge 2x thumb
It worked!!! THANK YOU!  The formula I used was:

([Starting PTO] + Month(Today()) * [Accruing PTO]) - [PTO Hours Used].
This gave me the amount each month minus sick/vaca taken.

One more question...when January rolls around, will it automatically add [Accruing PTO] to the [Starting PTO]? If so their PTO will be one month too much. I need it to start on February 1st. So do I change my [Starting PTO] to be one month short hours, or is there a way to manipulate our formula at the top?

I soooo appreciate you help!!!
Yes on January 1st the EE will get their full allotment of their January PTO hours all at once,. say the whole 10 hours for a new EE with less than 1 year service (or 20 hours for a long time service EE).  If you want to not give them the January hours until Feb 1st, then you will needs to subtract 1 form the month.

([Starting PTO] + (Month(Today())-1) * [Accruing PTO]) - [PTO Hours Used].
Photo of Gregg

Gregg

  • 90 Points 75 badge 2x thumb
Perfect!!! Thanks.
Now, if you do that, they will never get to use up their December PTO though, without going "in the hole"
Photo of Gregg

Gregg

  • 90 Points 75 badge 2x thumb
I have time to figure that one out...I really appreciate your help. Happy New Year!
:) yes you have about 11 months or so.  It comes down to the fine print in your HR policy, or writing the fine print. In my experience with PTO, often the rules are a bit loose until you go to program them and start asking questions. QuickBase does not do "loose".  Only people do "loose".