• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Edate function

Yusra

New Member
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.
 

Attachments

Juniad

Member
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

New Member
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
 

bosco_yip

Excel Ninja
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

Well-Known Member
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.
 

Attachments

Top