PTO Accrual Formula

[leolagoon94]

Hello,

Newbie to Excel formulas. I only know the basics such as =sum and =count. Wanted to know if there was a formula to auto calculate accruals for our PTO spreadsheet instead of constantly typing out the accrual for every employee. Staffing agency so have over 500 employees. Our basic PTO package is 40 hours with a 3.33 monthly accrual rate after reaching 6 months of employment. Higher ups or direct employees could have more as shown in the spreadsheet below. The two examples below are our long-term employees so they have been accruing since January of 2024. Any advice would be greatly appreciated!!

 

[arthurbr]

Hi. Is it important to know what PTO means?

 

[leolagoon94]

Hi. Is it important to know what PTO means?

Hi, it’s paid time off/vacation.

 

[myall_blues]

Assuming your month names are just text, this should work for you.
I made it so that it only accrues on the day of the month that matches the eligibility date. See for example rows 8 and 9.

Book1
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
5	Eligibility date	Acrrual rate	PTO Total	PTO Possible	January	February	March	April	May	June	July	August	September	October	November	December
6	1/01/2024	6.66	80	80	6.66	6.66	6.66	6.66	6.66	6.66	6.66	6.66	6.66
7	1/01/2024	3.33	40	40	3.33	3.33	3.33	3.33	3.33	3.33	3.33	3.33	3.33
8	15/01/2024	3.33	40	40	3.33	3.33	3.33	3.33	3.33	3.33	3.33	3.33
Sheet1

Cell Formulas
Range	Formula
H6:S8	H6	=LET(cd,DATE(YEAR($D6),MONTH("1"&H$5),DAY($D6)),IF(TODAY()>=cd,$E6,""))