Edate function

[Yusra]

Is there a better way to automatically change dates of a field based on a start date and another criteria, for example payment numbers.

I attached a sheet here, and If you look at the Transaction Sheet, you will notice multiple rows for the same Property ID. Each row of the same property ID (ex. 64) should have a different Transaction date. This is based on 1. Start date found in Property Revenue & Margin Cost sheet and 2. the Number of payments. so for example, if start date is January 10, 2019, next payment is due April 10, 2019 and July 10, 2019 and October 10, 2019.

What I used: First row will be the start date, the second will be Edate(Start Date, 3), drag to the next two rows etc.

 

[Juniad]

You could try this one:
in cell E2: =VLOOKUP(B2,'Property Revenue & Margin Cost'!$B$2:$F$45,5)
in cell E3: =IF(B3=B2,EDATE(E2,3),VLOOKUP(B3,'Property Revenue & Margin Cost'!$B$2:$F$45,5))

 

[Yusra]

Hi Junaid,

Thanks you. I tried the above formula and as expected I would still have to edit the formula based on the number of payments. So for example, If there are 12 transaction, the Edate formula in E3 will be changed to EDATE(E2,1), If there are 2 transactions in total it will be Edate(E2,6).

Hope this is clear

 

[vletm]

Yusra
As You seems like formulas ...
You can use formula ...
cell E2 =IF(B1<>B2,VLOOKUP(B2,'Property Revenue & Margin Cost'!$B$2:$F$45,5), EDATE(E1,3)) and copy down

 

[bosco_yip]

Or...........

In "Transaction Sheet" E2, copied down :

=EDATE(VLOOKUP(B2,'Property Revenue & Margin Cost'!$B$2:$F$45,5),IF(J2=4,3,IF(J2=2,6,1))*(COUNTIF(B$2:B2,B2)-1))

Regards

 

[Peter Bartholomew]

What you are doing is, in essence, unpivoting a variable number of payments for each property to get a single list. To populate the list you could build a cumulative count of payments as you run down the Property table, Then when you collect data for the transaction sheet you can look up its record number in the cumulative count to determine which row and property ID to return for the current transaction record.