Date subtraction

[Muthukrishnan V]

Excel 365

Date subtraction

Cell A1 and Cell B1 contain dates in dd-mm-yyyy format.

If A1 is greater than B1, result should be 0.
(example: A1 = 31-10-2021 and B1 = 15-09-2021)

If A1 is earlier than B1, result should be in
number of months (part of a month to be
reckoned as a full month)
(example: A1 = 31-10-2021 and B1 = 04-12-2021,
result should be 2. (i.e. 2 months)

Request formula to achieve this.

 

[Habtest]

Hi, please try:

=(A1<=B1)*(MONTH(B1)-MONTH(A1))

 

[Joe4]

=(A1<=B1)*(MONTH(B1)-MONTH(A1))

That may not work for dates that span years, so if the month number of the later date is less than the month number of the earlier date.
For example, if A1 was 31-10-2021 and B1 was 28-02-2022.

Try using the DATEDIF function instead:

=IF(B1>A1,DATEDIF(A1,B1,"m"),0)

See here for details on that: Calculate the difference between two dates

 

[Muthukrishnan V]

Mr Habtest Sir, in the second case (where A1 is earlier than B1) result is 2. Absolutely correct.

But in the first case ,where A1 is greater than B1, result shows -10. (instead of 0)

 

[Joe4]

I am not sure if you saw my reply, or the original version.
My original reply did not have a new equation in it, but I updated it to include a solution, that you might not have seen if we were posting at the same time.

 

[Habtest]

You are right @Joe4 , and good to know DATEDIF(), Thank you! One tweak though as:

part of a month to be
reckoned as a full month

@Muthukrishnan V Sorry about the mistake. Please try below:

Book1
	A	B	C
1	10/31/2021	9/15/2021	0
2	10/31/2021	12/4/2021	2
3	10/31/2021	2/21/2022	4
Sheet1

Cell Formulas
Range	Formula
C1:C3	C1	=IF(B1>A1,DATEDIF(EOMONTH(A1,1),EOMONTH(B1,1),"m"),0)

 

[Muthukrishnan V]

Thank you Mr Habtest Sir, Great. Thank you Mr Joe Sir,

 

[Joe4]

Ah yes, I missed the part about partial months counting as whole months.
Your revision to my formulas handles that nicely.