Answered

Calculate eligible date based on field in SharePoint list every other year

(0) Share

Report

Posted on by Dorinda

1,512

I have an app that does equipment reimbursement. People are eligible for new equipment every two years I need to calculate when that next date will be.

Previous Purchase date: 01/01/2021

Next Eligible Date: 01/01/2023

I do not know how to do this?

Any help would be appreciated.

Categories:

Building Power Apps

I have the same question (0)

All responses (24)

Answers (1)

Sort by

Cre-do 30 on at

Like (0)

Report
Copy link

Link copied!

Hello Dorinda,
have you checked DateAdd function in PowerApps?

Was this reply helpful? Yes No
Dorinda 1,512 on at

Like (0)

Report
Copy link

Link copied!

I have not and I am not even sure how to begin to calculate it so every other year??

Was this reply helpful? Yes No
Cre-do 30 on at

Like (0)

Report
Copy link

Link copied!
Well. In that particular case, you should use

Text( DateAdd( Today(), 2, Years ),
"dd-mm-yyyy hh:mm" )

Where:
Text function is used for formating data.
DateAdd function is used to add units (seconds, minutes, hours, days, months and years) to date/time.
Now() function displaying actual data & time.
"2" - because you want to add two
Years
"dd-mm-yyyy hh:mm" defines format of time and date value.

Was this reply helpful? Yes No
Dorinda 1,512 on at

Like (0)

Report
Copy link

Link copied!

This is great if I was calculating it based on Today, how do I reference a date field in my SharePoint list instead of Today()

Was this reply helpful? Yes No
Cre-do 30 on at

Like (0)

Report
Copy link

Link copied!

Use connector with SharePoint list.
Then Filter data, or use LookUp function.
Use filtered data same way you could use "today ()".

Was this reply helpful? Yes No
Dorinda 1,512 on at

Like (0)

Report
Copy link

Link copied!
@Cre-do Thank you for getting me part of the way there. I have this sorta working

Text( DateAdd(DateValue(Label15.Text), 2, Years ), "mm-dd-yyyy" )

It is returning a date in the future however I need to put a condition in this equation.

If Equipment type = Safety Toe Boot, then do that calculation, if it equals Safety Eye Protection do the same calculation.

I have them in two different labels just cant seem to get the syntax correct. I tried this with no luck

If('Safety Equipment Reimbursement'.'Equipment Type'="Safety Toe Boot",Text( DateAdd(DateValue(Label15.Text), 2, Years ), "mm-dd-yyyy" )

Was this reply helpful? Yes No
Cre-do 30 on at

Like (0)

Report
Copy link

Link copied!

That's just half of your "IF" statement.

If('Safety Equipment Reimbursement'.'Equipment Type'="Safety Toe Boot"||'Safety Equipment Reimbursement'.'Equipment Type'="Safety Eye Protection",Text( DateAdd(DateValue(Label15.Text), 2, Years ),
"mm-dd-yyyy" ))

|| - means "or".

Was this reply helpful? Yes No
Dorinda 1,512 on at

Like (0)

Report
Copy link

Link copied!

I am getting this error now

Was this reply helpful? Yes No
Cre-do 30 on at

Like (0)

Report
Copy link

Link copied!

Yes, you can't compare different value types. As you can see, you've text value in your dateadd formula.
You trying to compare text from your app, with table in your SharePoint. You will need some kind of filtering state.. Like Filter or LookUp.

Was this reply helpful? Yes No
Dorinda 1,512 on at

Like (0)

Report
Copy link

Link copied!
@Cre-do

I guess my brain is not firing I am not getting this to work

LookUp('Safety Equipment Reimbursement',EmployeeName.Email=CurrentUserEmail && 'Equipment Type'="Safety Toe Boot" && Text( DateAdd(DateValue(Label15.Text), 2, Years ), "mm-dd-yyyy" ))

Was this reply helpful? Yes No