Calculation PTO And Vacation Time based on Work Anniversary Date

[PlanetFitness]

Hello

I am working on a spreadsheet I inherited and trying to get the formula correct for calculation the Vacation and PTO time accrued based on work anniversaary.

The current formula calcuates but does not take in to consideration if say an employee is set to renew in June etc.

https://bbpventures-my.sharepoint.c...22.23.xlsx&action=default&mobileredirect=true

 

[awoohaw]

@PlanetFitness ,

Welcome to the forum.

Many forum users here do not like to click links from new users (At least I don't, too much risk of trojans for me).
If possible can you use the xl2bb add in (link below) to post a mini workbook (anonymized or PII removed)?
If you can't do that, can you post the pertinient columns in a table?

Thanks for helping the forum help you.

 

[PlanetFitness]

2023 PTO Tracker GYMS-Chez-PC-10 UPDATED 3.22.23.xlsx
	H
13	5
Legend-Employee ledger

Cell Formulas
Range	Formula
H13	H13	=IF(C13=0,0,IF($H$3>(C13+1825),15,IF($H$3>(C13+730),10,IF($H$3>(C13+365),5,IF($H$3<C13+365,0)))))

 

[awoohaw]

thanks for getting the add in. But you need to select a range of cells before you click to copy.
If you're concerned with privacy, that is understandable.
But, can you say what kind of data is in columns: C and H, and where is the indicator for renewal, I would think that is a Yes/No Value and the formula doesn't look like it compares Yes/No values. C looks like it may be a date. I guess H does too. What are the relationships of those dates? Is one a hire date and another someother kind of date? Or is H3 the renewal date?

 

[PlanetFitness]

Im still figuring out the ADd On correctly. Column C is their Hire Date , Cell H3 is todays date. Do I need a seperate column for when they renew for PTO and VACATION time? We needa formula that can 1) count the amount of time alloted 2) count when their bank renews?

 

[awoohaw]

Im still figuring out the ADd On correctly. Column C is their Hire Date , Cell H3 is todays date. Do I need a seperate column for when they renew for PTO and VACATION time? We needa formula that can 1) count the amount of time alloted 2) count when their bank renews?

well, you copied a few cells correctly, just highlight more cells. (also, its generally best practice to select all the options for what to paste: Cell Formatting, Conditional Formatting, Data Validation, and Named Ranges. Also copy as mini sheet and not table.

I'm not sure about extra columns I'm just looking at this! I'll have a better idea when I see the data sample.

 

[PlanetFitness]

how do i reopen the add on? it does not appear anymore on the top of my excel work book? So sorry

 

[awoohaw]

mmm. Go to FILE>> OPTIONS >> ADD INS >> in the Manage box... "excel Add ins" click GO then click the box beside xl2bb Add in.

 

[PlanetFitness]

2023 PTO Tracker GYMS-Chez-PC-10 UPDATED 3.23.23 RINA EDITS.xlsx
	C	D	E	F	G	H
3				Current Date		3/24/2023
4
5
6
7
8
9	Management Start Date		Years of service		PTO	Vacation
10	3/6/2023		0		0	0
11	3/6/2023		0		0	0
12	12/27/2021		2		8	15
13	6/21/2021		2		0	5
14	8/10/2022		1		0	0
15	2/19/2023		0		0	0
Legend-Employee ledger

Cell Formulas
Range	Formula
H3	H3	=TODAY()
G10,G15,G12	G10	=IF(C10=0,0,IF($H$3>C10+91,8,0))
H13:H15,H10:H11	H10	=IF(C10=0,0,IF($H$3>(C10+1825),15,IF($H$3>(C10+730),10,IF($H$3>(C10+365),5,IF($H$3<C10+365,0)))))
G11	G11	=IF(C11=0,0,IF($H$3<1+91,8,0))
H12	H12	=IF(E12=0,0,IF($H$3>(E12+1825),15,IF($H$3>(E12+730),10,IF($H$3>(E12+365),5,IF($H$3<E12+365,0)))))
G13:G14	G13	=IF(C13=0,0,IF($H$3<C13+91,8,0))
E10:E15	E10	=DATEDIF(C10,TODAY(),"y")+(TODAY()<DATE(YEAR(TODAY()),MONTH(C10),DAY(C10)))