Need help with a formula to calculate accrued PTO based on # of pay periods
Hello, I am seeking help with a formula to calculate accrued PTO based on the # of pay periods per person each year and if they are fulltime.
Example 1: Nikki started on 7/1/22 and accrues 3.5 hours each pay period (twice a month) so I need a formula to let me know what she has accrued to date.
Example 2: Laura started on 9/25/84 and accrues 4.5 hours each pay period and PTO does not rollover.
My 1st sheet is the Employee database that has all the information regarding employee name, contact info, hire date, fulltime vs parttime, etc.
My 2nd sheet is connected to the PTO request form that tracks the info shown below including how many vacation days they are requesting and how many sick days they are requesting off. I understand how to connect both sheets and just need the formula to calculate correctly.
Answers

You say the PTO does not roll over. When is the cutoff? Is it Dec 31st and we start Jan 1 at zero?

The cutoff date is Dec 31st, yes and restart again at Jan 1.

I was able to figure out the formula in excel but cant seem to convert it in Smartsheets

So basically you would need previous months times two plus either one or two depending on how far into the month we are (assuming 1st and 15th for the pay periods). Ten we multiply that number by the number of hours accrued each pay period.
So this gives us YTD if they were hired in the previous year.
=(MONTH(TODAY()) * 2)  IF(DAY(TODAY())< 15, 1, 0)
This gives us Start to Date if they were hired this year:
=((MONTH(TODAY())  MONTH([Hire Date]@row)) * 2)  IF(DAY(TODAY())< 15, 1, 0)  IF(DAY([Hire Date]@row)< 15, 1, 0)
Then we use an IF statement to determine which one to run:
=IF(YEAR([Hire Date]@row) = YEAR(TODAY()), start_to_date, tear_to_date)
=IF(YEAR([Hire Date]@row) = YEAR(TODAY()), ((MONTH(TODAY())  MONTH([Hire Date]@row)) * 2)  IF(DAY(TODAY())< 15, 1, 0)  IF(DAY([Hire Date]@row)< 15, 1, 0), (MONTH(TODAY()) * 2)  IF(DAY(TODAY())< 15, 1, 0))

I will try this out! Thanks so much for your time and help with this.

Happy to help. 👍️

I would use something like the following. Since each employee has a unique accrual rate you will need to create a column for that value. I also assumed you have a column showing the vacation days used, but didn't see it in the sheet shown. Either way, this should calculate the number of two week periods since the start date. Haven't tested this formula, so won't make any guarantees.
=(FLOOR(WEEKNUMBER(TODAY())WEEKNUMBER([Time off start date]@row))/2)*[Accrual Rate]@row[Vacation Used]@row

@brianswilson I'm not sure that would work. You are comparing the current week number to the week number for when the time off starts. That wouldn't determine how much total has been accrued from the start of the year (or from their start date if current year). You are also going with biweekly instead of semimonthly.

You are correct, this won't work if the pay periods are semimonthly rather than biweekly. The index WEEKNUMBER([Time off start date]@row) that I gave is incorrect as you pointed out. It should have been either the WEEKNUMBER(<start date reference>), or just use a 1 as the start of the time period (don't assume January 1 is in the first week of the year and look up the week number based on that date). An IF() testing if the current date is in the same year [e.g., IF (YEAR(<start date reference>) = YEAR(TODAY()), ..., ...) ] as the start date and using two versions of this formula (one using the start of year if false and one using start date if true) would be needed.
One thing I really dislike is that WEEKNUMBER() incorrectly uses Monday as the first day of the week when this is not correct. I really hate it when companies ignore the actual calendar and make no provision for users needing correct results.
"Smartsheet calculates week numbers based on Mondays. The first day of the year may not be in the first week of the year because of this." [https://help.smartsheet.com/function/weeknumber]

@brianswilson As much as I love Smartsheet... That is definitely one gripe I have with it. At the very least consistency would be nice. If WEEKNUMBER is going to start on Monday, then WEEKDAY should also start on Monday (Sunday is 1 with that function). It is almost like the WEEKNUMBER function is geared more towards working days and should actually be WORKWEEKNUMBER or something like that.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.2K Get Help
 360 Global Discussions
 198 Industry Talk
 427 Announcements
 4.4K Ideas & Feature Requests
 136 Brandfolder
 127 Just for fun
 128 Community Job Board
 444 Show & Tell
 28 Member Spotlight
 1 SmartStories
 283 Events
 35 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!