Unanswered

New column that calculates Vacation days earned based on Start date and years employed

(0) Share

Report

Posted on by michellecoslet

192

Looking for help to create a new column in my Dataverse table, that calculates the Accrued Days earned for vacation based on start date. This column would also need to be editable (rare but necessary). This will be used in my Vacation Time Off app to show how many days are alloted for the particular user.

Below are the guidelines that are needed for calculating the days (underlined in examples below)

After one full year of employment = earn 5 days of paid vacation.

After two full years of employment = earn 10 days of paid vacation.

After five years = earn 11 days

After six years = earn12 days

After seven years = earn 13 days

After eight years = earn 14 days

After nine years and higher = earn 15 days

Ex:

Michelle started on 11/16/2008 she would have accrued 15 days of vacation

Jane started on 11/25/2020 she would have accrued 10 days of vacation

Millie started on 1/1/2022 she would have accrued 5 days

Steve started on 2/15/2022 he would have accrued 0 days (would automatically change to 5 days on 2/15/2023)

My Table has the following columns already

EMP_EmployeeID (ex: 18)

EMP_Name_LFM (ex: Coslet, Michelle E)

EMP_DateOfHire (ex: 11/16/2008 6:00 PM)

Status (ex: Active)

Employment Length (ex: 5161) = days employed

Categories:

Building Power Apps

I have the same question (0)

All responses (4)

Answers (0)

Sort by

Jcook 7,783 Most Valuable Professional on at

Like
a
(1)

Report
Copy link

Link copied!
Hello @michellecoslet

1. Create new column example: "Accrued Days".
2. Select Whole number as the type.
3. Under the "Default Value" field, enter a formula to calculate the Accrued Days based on the start date. You can use the DATEDIF function to calculate the number of days between the start date and today's date. For example, the formula could be:
=IF(DATEDIF([EMP_DateOfHire], Today(), "Y") >= 9, 15, IF(DATEDIF([EMP_DateOfHire], Today(), "Y") >= 8, 14, IF(DATEDIF([EMP_DateOfHire], Today(), "Y") >= 7, 13, IF(DATEDIF([EMP_DateOfHire], Today(), "Y") >= 6, 12, IF(DATEDIF([EMP_DateOfHire], Today(), "Y") >= 5, 11, IF(DATEDIF([EMP_DateOfHire], Today(), "Y") >= 2, 10, IF(DATEDIF([EMP_DateOfHire], Today(), "Y") >= 1, 5, 0)))))))

4. Save

Was this reply helpful? Yes No
michellecoslet 192 on at

Like
a
(0)

Report
Copy link

Link copied!

EDITED TO ADD: I am only focusing on the Vacation Days type. Sick, bereavement, Jury duty may not be necessary.

Looks like this should work.
1. I setup the new column in my dataverse table (no where to write the default value however)
2. In my app, I am not able to refer to this dataverse table even though i added the table.

Here is my CURRENT formula used to show "days available out of Accrued days" that was setup with a list. I need to change it to reference the dataverse table column "Accrued Days" instead

ThisItem.~~'TimeOffType: AllottedTime'.Value~~-ThisItem.RequestedTimeOff &"/"&Value(ThisItem.~~'TimeOffType: AllottedTime'.Value~~)

Any tips?

My Dataverse table name is titled "EmployeeWithFullName"
Column name is "Accrued Days"

Was this reply helpful? Yes No
michellecoslet 192 on at

Like
a
(0)

Report
Copy link

Link copied!
ERROR on the column formula
I am not able to choose Number > whole number because there is no Default Value field

I instead am trying Formula type and get 6 errors on the formula:
Unexpected characters. Characters are used in the formula in an unexpected way.
Expected operator. We expect an operator such as +, *, or & at this point in the formula.
3. Expected operator. We expect an operator such as +, *, or & at this point in the formula.
4.'DATEDIF' is an unknown or unsupported function.
5.Unexpected characters. The formula contains 'ParenClose' where 'Comma' is expected.

Was this reply helpful? Yes No
michellecoslet 192 on at

Like
a
(0)

Report
Copy link

Link copied!
=IF(DATEDIF([EMP_DateOfHire], Today(), "Y") >= 9, 15, IF(DATEDIF([EMP_DateOfHire], Today(), "Y") >= 8, 14, IF(DATEDIF([EMP_DateOfHire], Today(), "Y") >= 7, 13, IF(DATEDIF([EMP_DateOfHire], Today(), "Y") >= 6, 12, IF(DATEDIF([EMP_DateOfHire], Today(), "Y") >= 5, 11, IF(DATEDIF([EMP_DateOfHire], Today(), "Y") >= 2, 10, IF(DATEDIF([EMP_DateOfHire], Today(), "Y") >= 1, 5, 0)))))))
What does the "Y" represent? I still have not been able to get this formula to work unfortunately.
Any help appreciated!

thanks

Was this reply helpful? Yes No