Hi Everyone, I am trying to build a simple PTO tracker for my team. I have my list of employees and I would like to add a column with a formula that create 1 PTO day per month but I dont have clue on how to do it. Any help on this please?
Thanks!
If the requirement is just a simple incrementor that gains 1 in value each month, it should be pretty simple.
Assumptions:
- You have a “Start Date” field where each team member’s start date with the team/company is indicated as a Date
- You want the PTO value to increase by 1 for each month since the team member’s start date (implying everyone’s PTO will increment by 1 every month, but not necessarily on the same day – a team member who started on the 6th of the month will earn their PTO day on the 6th every month; a team member who started on the 27th of the month will earn their PTO day on the 27th every month, etc.)
IF(
{Start Date},
DATETIME_DIFF(TODAY(), {Start Date}, 'months')
)
+1Jeremy_Oglesby wrote:
If the requirement is just a simple incrementor that gains 1 in value each month, it should be pretty simple.
Assumptions:
- You have a “Start Date” field where each team member’s start date with the team/company is indicated as a Date
- You want the PTO value to increase by 1 for each month since the team member’s start date (implying everyone’s PTO will increment by 1 every month, but not necessarily on the same day – a team member who started on the 6th of the month will earn their PTO day on the 6th every month; a team member who started on the 27th of the month will earn their PTO day on the 27th every month, etc.)
IF(
{Start Date},
DATETIME_DIFF(TODAY(), {Start Date}, 'months')
)
Thanks so much Jeremy! it was so helpful. And one more question, what about for those employees who has 2 weeks per Year, which formula can I use to do a monthly accrual of this case? Thanks!
+6Multiply the above formula by 0.833333333
Assuming 2 weeks/year is 10 days per year. Maybe use a round() or rounddown() formula to process it.
Reply
Most helpful members this week
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.