Nov 10 2022 02:57 PM
Good evening,
I am trying to finish an excel sheet for my boss to restart vacation days to the employee's anniversary date. I have tried different formula's and can't get it. I keep getting error messages. I have the years of service, vacation days earned, and anniversary date calculated. Just need to generate a formula so that vacation days restart on employee's anniversary date. Can someone help me with this?
Nov 10 2022 04:55 PM
Nov 11 2022 06:20 AM
I enter things weekly when do payroll. Showing, sick, vacation, personal etc.
It won't let me put a picture of it on here.
Nov 11 2022 06:27 AM - edited Nov 11 2022 06:28 AM
It sounds like you're trying to build a paid time off calculator (PTO). I'm guessing the PTO earned increases each year once the employee reaches the hire anniversary date.
For example:
An employee earns 8 hours of PTO every month for the first year. The second year that gets bumped up to earning 8.25 hours/month, for example.
There might also be a policy where one cannot carry over all PTO hours past a given year, etc. I think those are the kind of details needed to create a calculator here.
Nov 11 2022 06:48 AM - edited Nov 11 2022 06:50 AM
TRY:
=LET(Adate, DATE( YEAR(TODAY()), MONTH(anniversary), DAY(anniversary)),
Adays, TODAY() - Adate,
IF( Adays>0, Adays, TODAY() - EDATE(Adate, -12)))
so Adate is the anniversary date THIS year and Adays is # of days from today - Adate. If that is negative (i.e. anniversary hasn't happened yet) then use today - anniversary date 12 months earlier
you can then convert # days to # weeks by /7 unless you have other nuances to the calculation...
Nov 11 2022 07:51 AM
Nov 11 2022 07:52 AM
Nov 11 2022 08:32 AM
Nov 14 2022 06:24 AM
Nov 14 2022 06:36 AM - edited Nov 14 2022 08:48 AM
Nov 14 2022 08:33 AM