Answered

Calculated date field and leap years

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

I'm experiencing an issue when using a calculated date field to determine total years of service for a user group. Here's the scenario:

User A logs accesses the app which is connected to a SQL table containing their employee information, including their service date.
Using another date field, the user indicates their *potential exit.
A calculated field then displays the user's total number of service years.

The behavior I'm seeing is as follows:

User A has a service date of 7/21/2003 and they indicate 7/16/2022 as their *potential exit date.
The calculated field indicates their total years of service is 19 years.
(If User A indicates 7/15/2022 as their *potential exit date, the total years of service indicated is 18.)

I would not expect 19 years to be displayed unless the *potential exit date was indicated as 7/21/2022. I'm guessing this has something to do with leap years, but not completely sure, nor can I figure out what function is necessary to correct this behavior. Any help is appreciated. Thanks in advance.

Mike

Categories:

Building Power Apps

I have the same question (0)

All responses (7)

Answers (1)

Sort by

RandyHayes 76,299 Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!

@Anonymous

What is the formula you are using to do the calculation?

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

@RandyHayes

RoundDown(DateDiff(DateTimeValue(Service_Date_Field.Text), Last_Day_Worked_Date_Box.SelectedDate,Days)/365,0)

Was this reply helpful? Yes No
RandyHayes 76,299 Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!
@Anonymous

There is no need for you to do your own calculation on the amount of days. You are only accounting for 365, which is not accurate for leap years.

Change your formula to:

DateDiff(DateValue(Service_Date_Field.Text), Last_Day_Worked_Date_Box.SelectedDate, Years)

Now the real question remains...your formula indicates you are rounding down with no fraction. Is that something you want or do you want fractions of a year?

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Thanks for the reply. For the purpose of this calculator, there are not 'partial' years. (No fractions) So using the same dates as above, User A's service date is 7/21/2003, any date entered into the Last_Day_Worked_Date_Box that is BEFORE 7/21/2022 should render 18 years of service, and any date entered that is 7/21/2022 or later should render 19 years of service. I confirmed with my comp team that service years are determined by the actual day of the month, not necessarily every 365 days. Hope this helps.

Mike

Was this reply helpful? Yes No
Verified answer

RandyHayes 76,299 Super User 2024 Season 1 on at

Like (1)

Report
Copy link

Link copied!
@Anonymous

Thanks! That adds the true requirement then if it is based on the month and day as well as the count of years.

So really, doing the year count and using the days is not helpful in this case. What is needed is the year difference and then a comparison to the month/day of the year. For that last part, I would use a leap year as the determining year so that if a person has a start date of 2/29, then the calculations would be correct.

The formula would be:

With({_start: DateValue(Service_Date_Field.Text), _end: Last_Day_Worked_Date_Box.SelectedDate}, Year(_end) - Year(_start) -1 + If(DateDiff(Date(Year(2020), Month(_end), Day(_end)), Date(Year(2020), Month(_start), Day(_start)))>0, 0, 1) )

Note in the above, 2020 is used as it is a leap year. It need not ever change, it is just reference for the month/day part.

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Thank you so much for this solution. It appears to be working as expected for me and I have sent it to my client for User Acceptance Testing. Again, I really appreciate the insight and quick response.

Was this reply helpful? Yes No
RandyHayes 76,299 Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!

@Anonymous

No problem! Please choose which of the responses in this thread solved the issue for you and accept it as a solution to close this out.

Was this reply helpful? Yes No