# Date subtraction

#### Muthukrishnan V

##### Board Regular
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.

### Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

#### Habtest

##### Board Regular
Excel Formula:
``=(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:
Excel Formula:
``=IF(B1>A1,DATEDIF(A1,B1,"m"),0)``

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

#### Muthukrishnan V

##### Board Regular
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

##### Board Regular
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

Book1
ABC
110/31/20219/15/20210
210/31/202112/4/20212
310/31/20212/21/20224
Sheet1
Cell Formulas
RangeFormula
C1:C3C1=IF(B1>A1,DATEDIF(EOMONTH(A1,1),EOMONTH(B1,1),"m"),0)

#### Muthukrishnan V

##### Board Regular
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.

Replies
2
Views
91
Replies
3
Views
59
Replies
6
Views
188
Replies
3
Views
451
Replies
4
Views
237

1,147,451
Messages
5,741,197
Members
423,648
Latest member
steel1968

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back