Answered

Calculating Dates based on other fields

(0) Share

Report

Posted on by malcolmph

=DATE(YEAR(B7),MONTH(B7)-((YEAR(B5)-YEAR(B3))*12+MONTH(B5)-MONTH(B3)),DAY(B7)+729)

which if convert the cell ranges to my fields in the Powerapp looks like

DateAdd(DateAdd(Year(ReConDate),Month(ReConDate)-((Year(ConfDate.SelectedDate)-Year(AccDate.SelectedDate))*12+Month(ConfDate.SelectedDate)-Month(AccDate.SelectedDate)),Day(ReConDate)+729)DateAdd(DateAdd(Year(ReConDate),Month(ReConDate)-((Year(ConfDate.SelectedDate)-Year(AccDate.SelectedDate))*12+Month(ConfDate.SelectedDate)-Month(AccDate.SelectedDate)),Day(ReConDate)+729)

but this won't work, not that i expected it to

So looking for a way to achieve the same result. Basically the formula should

Add 2 years to the ReConDate and minus the difference between AccDate and ConfDate

for example:-

AccDate=01/10/20

ConfDate=01/01/2021

Difference = 3 months

ReconDate = 01/08/2021

Add 2 years = 01/08/2023

minus Difference = 30/04/20

Many thanks in advance. I have all the other dates and fields working as expected

Categories:

Building Power Apps

I have the same question (0)

All responses (4)

Answers (1)

RandyHayes 76,299 Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!

@malcolmph

I assume the repetition of the formula in your sited formula is just a mistake.

I notice that you are using the DateAdd function in your PowerApps formula, however, you are using the Date function in your Excel formula.

Can you explain "Add 2 years to the ReConDate and minus the difference between AccDate and ConfDate" further? This seems to imply two different results - one with 2 years addeded to ReConDate and one result which is the different between two other dates.

Was this reply helpful? Yes No
malcolmph 9 on at

Like (0)

Report
Copy link

Link copied!

Hi
You are correct but I need to combine them.
We need to add two years and then minus the difference between two dates
or another way to look at it would be to calculate the difference between the dates AccDate and ConfDate take this away from two years and add the result
or
StatDate=recondate+2yrs-difference between accdate and confdate
i.e difference between the dates AccDate and ConfDate = 3 months
then add 1yr and 9 months to the ReConDate

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!
@malcolmph
Gotcha! I think your original formula is off by a day. Could be the hardcoded value of 729 - which is not quite 2 years.

Here is the formula you would use in PowerApps (Please note for demonstration purposes I have the dates you provided hardcoded in here...and they are in US format. Please alter as needed):

With({ ReconDate:DateValue("8/1/2021"), AccDate:DateValue("10/1/2020"), ConfDate:DateValue("1/1/2021") }, DateAdd(ReconDate, // the original reconDate DateDiff(ReconDate, DateAdd(ReconDate, 2, Years), Days) - // recon plus 2 years (leap year adjusted) DateDiff(AccDate, ConfDate, Days), // difference in AccDate and ConfDate Days ) )

This formula will also account for leap year for the adding of 2 years worth of days.

I hope this is helpful for you.

Was this reply helpful? Yes No
malcolmph 9 on at

Like (1)

Report
Copy link

Link copied!

Many thanks @RandyHayes - that has worked perfectly

Was this reply helpful? Yes No