Need help with a formula to calculate accrued PTO based on # of pay periods

NikkiOno

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 full-time.

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, full-time vs part-time, 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.

Paul Newcome

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

NikkiOno

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

NikkiOno

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

Paul Newcome

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))

NikkiOno

@Paul Newcome

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

Paul Newcome

Happy to help. 👍️

brianswilson

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

Paul Newcome

@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 bi-weekly instead of semi-monthly.

brianswilson

You are correct, this won't work if the pay periods are semi-monthly rather than bi-weekly. 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]

Paul Newcome

@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.