Date subtraction

Muthukrishnan V

Board Regular
Joined
May 29, 2008
Messages
203
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jul 30, 2020
Messages
197
Office Version
  1. 365
Platform
  1. Windows
Hi, please try:
Excel Formula:
=(A1<=B1)*(MONTH(B1)-MONTH(A1))
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,024
Office Version
  1. 365
Platform
  1. Windows
=(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
Joined
May 29, 2008
Messages
203
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,024
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Jul 30, 2020
Messages
197
Office Version
  1. 365
Platform
  1. Windows
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
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)
 
Solution

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,024
Office Version
  1. 365
Platform
  1. Windows
Ah yes, I missed the part about partial months counting as whole months.
Your revision to my formulas handles that nicely.
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top