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?
 90 Points
Posted 2 years ago
QuickBaseCoach App Dev./Training (Mark Shnier), Champion
 70,354 Points
I suggest using a formula to calculate their accrued PTO based on their month of service.
 90 Points
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.
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.
QuickBaseCoach App Dev./Training (Mark Shnier), Champion
 70,354 Points
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.
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.
 90 Points
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!!!
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!!!
QuickBaseCoach App Dev./Training (Mark Shnier), Champion
 70,354 Points
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]
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]
 90 Points
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!!!
([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!!!
QuickBaseCoach App Dev./Training (Mark Shnier), Champion
 70,354 Points
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].
([Starting PTO] + (Month(Today())1) * [Accruing PTO])  [PTO Hours Used].
QuickBaseCoach App Dev./Training (Mark Shnier), Champion
 70,354 Points
Now, if you do that, they will never get to use up their December PTO though, without going "in the hole"
 90 Points
I have time to figure that one out...I really appreciate your help. Happy New Year!
QuickBaseCoach App Dev./Training (Mark Shnier), Champion
 70,354 Points
:) 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".
Related Categories

Tables & fields
 7304 Conversations
 196 Followers