Convert Days to Months - Round Up

[Robby19]

So I have a Large Date - Small Date which outputs the amount of days in-between. What I need is to convert this number into a number of months and round that up.

For example, and output of 49 Days should equal 2 months. 15 Days should equal 1 month. So on and so forth, all the way to 48 months.

I tried using a DatedIF and INT =INT([@Days]/365)&" Y,"&INT(MOD([@Days],365)/30)&" M" however the INT does not round up to the next month if the days are in-between a month.

I hope this makes sense. Any help would be greatly appreciated.

 

[wideboydixon]

It's probably easier to do this with the original dates like this:

Book1
	A	B	C
1	22/02/2018	16/04/2018	2
Sheet1

Cell Formulas
Range	Formula
C1	=MONTH(B1)-MONTH(A1)+(YEAR(B1)-YEAR(A1))*12+IF(DAY(A1)B1),1,0)

WBD

 

[Robby19]

This is close, however I have noticed the following;

16 Apr 20 - 27 May 19 is coming out at 12. It should be 11. Since 27 May 19 plus 11 months would be 27 Apr 20, thus it becomes larger that the original date.

 

[wideboydixon]

Ermmm...

Book1
	A	B	C
1	27/05/2019	16/04/2020	11
Sheet1

Cell Formulas
Range	Formula
C1	=MONTH(B1)-MONTH(A1)+(YEAR(B1)-YEAR(A1))*12+IF(DAY(A1)B1),1,0)

WBD

 

[wideboydixon]

If your larger date is first then you may need to swap the references as appropriate.

WBD

 

[Robby19]

Got it working perfectly. Took me a second to understand the logic and write it for my report, but this is great. Thank you!

 

[iggy_]

Another approach would be to use a DAYS360 function which assumes 30 days in each month.

=TRUNC((DAYS360(A1,B8)/30)+MIN(MOD(DAYS360(A1,B1),30),1))