How to subtract date only format mm dd in excel

Question

My date information was retrieved from our system without the year and in the format "mm/dd".

As a result, when I decrease the value between the previous and current years, I run into an issue.

Example: Date action Date Check Result Current Date
12/21 01/03 -352 03/18/2022

The correct result is: Date action Date Check Result Current Date
12/21 01/03 13 03/18/2022

How to subtract correctly?

Answer 1

I suppose Excel has handled December 21 and January 3 as dates, but that it has always assumed the current year. From cell A2, this subtracts one year.

=DATE(YEAR(A2)-1,MONTH(A2),DAY(A2))

For instance, check to see if the earlier date is one month earlier than the later date; if not, subtract one year from the earlier date and then compute the difference.

  +------------+------------+------+
  |      A     |     B      |   C  |
1 |   earlier  |   latter   | diff |
  +------------+------------+------+
2 | 2022-12-21 | 2022-01-03 |   13 |
  +------------+------------+------+

in cell C2

=IF(MONTH(A2) > MONTH(B2),B2-DATE(YEAR(A2)-1,MONTH(A2),DAY(A2)),B2-A2)