Calculations using Dates

I need help developing a formula that looks at the start and end dates for an agreement (eg. rent, where rent amount per month is fixed). The agreement can straddle months or years. I would like to show rent mont by month on a timescale, however, be able to prorate the rental amount in the initial month and the final month where the agreement start and end part-way through the month (eg. Agreement starts 12 August 2016, Ends 22 September 2018 and assume monthly rental of $1000)

Accepted answers

All comments

PrevContributor

Use "start" and "days" functions to fix number of days and then prorata

peter_mcanena

You can prorate the opening month like this:

(END() - Agreement Start + 1) / DAYS()

And prorate the closing month like this:

(Agreement End - START() + 1) / DAYS()

...assuming that Agreement Start and Agreement End are the first and last days of your agreement respectively.

These formulae give you a pro-rata multiplier to apply to your rent or whatever.

njduncanson

Did you get a response on this question? I'm trying to do the same, but don't see how to logically calculate if the month is withing the start-end of the agreement.

Egor

Hi,

It is actually quite easy to check if a period is within two dates. You will have to convert the dates to periods using PERIOD function. Having done that you can compare periods with < or > operators as you would compare numbers.

Ex. IF June 2017 < August 2017 THEN TRUE ELSE FALSE will give us TRUE as a result.

See screenshots attached for more details.

Screen_Shot_2017-05-25_at_15_7951.png

Quick Links

Can you help?

Unanswered questions

Azure blob connection Cloudworks vs ADO
I am trying to connect to Azure blob, the credentials work on cloudworks, but do not work on ADO. Has anyone else experienced this issue?
Berapa nomor Tokopedia?
Tokopedia Menyediakan layanan WhatsApp? Untuk menghubungi layanan Tokopedia melalui (WA)62_08174_9080_16 kamu bisa mengikuti.
Looking for Anaplan End-Users to take a short survey!
We are looking for Anaplan end-users to provide feedback on their experiences with the Excel add-in. Interested individuals will respond to this 5-minute survey to help us understand personal needs and behavior when using the add-in. The feedback provided by survey takers is essential to the roadmap of Anaplan's products.…