Calculating Dates based on other fields

[malcolmph]

Thanks for looking. I am looking for some help calculating dates. We are using excel but want to convert to an app. The excel formula is : 

=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

[malcolmph]

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

[RandyHayes]

@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.

[malcolmph]

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

[RandyHayes · Accepted solution]

@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.

[malcolmph]

Many thanks @RandyHayes - that has worked perfectly