percentage of way through lease

[mindbender]

having another brain cramp.

If I have 2 date fields LEASE_START_DATE and LEASE_EXPIRY_DATE how can I find the percentage of the way through the lease

I know in excel the following works - =IF(C3="","",(DATEDIF(C3,TODAY(),"d")+1)/(DATEDIF(C3,D3,"d")+1)) - where C3 is Start and D3 is End Date



Some of the fields are blank.

examples

LEASE START DATE	LEASE END DATE	% Way Through Contract
2014-12-30	2019-12-29	127%
2016-06-16	2021-06-15	98%
2016-07-28	2022-01-27	87%
2016-06-29	2021-12-28	88%
2016-10-13	2022-04-12	83%
2016-12-14	2021-12-13	88%

Thanks for the help as always

[ranman256]

Q1 get the basics
TotDays =Datediff("d",[LEASE_START_DATE],[LEASE_EXPIRY_DATE])
UsedDays = Datediff("d",[LEASE_START_DATE],Date())

Q2:
then do the math: UsedDays / TotDays

[Micron]

You've posted in programming, so post the relevant code if you get stuck because you'll also have to allow for the divisor being zero. Null fields will likely raise an error about division by zero.

[mindbender]

q1 get the basics
totdays =datediff("d",[lease_start_date],[lease_expiry_date])
useddays = datediff("d",[lease_start_date],date())

q2:
Then do the math: Useddays / totdays

that worked. Thank you so much