How to calculate days in month?

4.14K viewsFormulas and Functions
0

I’m trying to build a statement of adjustment model for Real Estate conveyance. I’m stumped trying to set up the adjustment for rent allocated between the vendor and purchaser.

Say the adjustment date falls on some day in the month. I need to calculate the amount of rent to debit the vendor and credit the purchaser. For simplicity, assume that the rent is always paid on the first of the month. The adjustment day can be any day of the month.

If the adjustment date fell on Feb 20th, I would want to calculate my adjustment factor as: (feb 20 – 1)/(number of days in Feb). Of course, I can manually figure out that the factor would be 19/28 (assuming no leap year). Is there a way that Quantrix could manage this calculation?

0

Hi,

This part of the formula calculates the first day of the next month

DATE(YEAR(Date),MONTH(Date)+1,1)

That result is subtracted by 1… which moves the date to the last day of the previous month.

And the DAY function extracts just the day and produces the result.

-Mike

0

Can you please explain to me how the formula: DAY(DATE(YEAR(Date),MONTH(Date)+1,1)-1) works?

0

Sure – assuming you had an item named ‘date’ that contained a date in the month you want to calculate, you could write a formula like so:

Number Days =DAY(DATE(YEAR(Date),MONTH(Date)+1,1)-1)

See attached model for an example.

-Mike

Latest Questions