How to subtract date only format mm dd in excel

0 votes
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?
Dec 25, 2022 in Others by Kithuzzz
• 38,020 points
711 views

1 answer to this question.

0 votes

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)
answered Dec 25, 2022 by narikkadan
• 63,720 points

Related Questions In Others

0 votes
1 answer

How to change date format from 201904 to Apr-19 in excel

This 201904 is recognised as a number ...READ MORE

answered Mar 31, 2023 in Others by Kithuzzz
• 38,020 points
407 views
0 votes
1 answer

Convert column in excel date format (DDDDD.tttt) to datetime using pandas

Given # s = df['date'] s 0 ...READ MORE

answered Oct 2, 2022 in Others by narikkadan
• 63,720 points
3,387 views
0 votes
1 answer

How to format numbers as lakhs and crores in excel/ google spreadsheet when the number could be negative too?

Excel formatting, in my opinion, can only ...READ MORE

answered Oct 31, 2022 in Others by narikkadan
• 63,720 points
14,419 views
0 votes
1 answer

How to format numbers in excel in crores or lacks

Try this: =TEXT(A1/10000000,"#,### \cr") READ MORE

answered Nov 6, 2022 in Others by narikkadan
• 63,720 points
881 views
0 votes
1 answer

Change date format of cell in excel from dd.mm.yyyy to yyy/mm/dd ( excel version 2013 )

Hello :)   Excel’s Format Cells function can quickly ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 23,260 points
1,543 views
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hello, yes u can find your birthdate using ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,690 points
3,642 views
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hi To Calculate the date, we can ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,690 points
1,898 views
0 votes
0 answers

Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

Feb 17, 2022 in Others by Edureka
• 13,690 points
938 views
0 votes
1 answer

How to change two different date format into single date format in excel

With data in A2, in B2 enter: =IF(ISNUMBER(A2),A2,DATE(LEFT(A2,4),MID(A2,6,2),RIGHT(A2,2))) and apply your format ...READ MORE

answered Dec 24, 2022 in Others by narikkadan
• 63,720 points
1,322 views
0 votes
1 answer

How to subtract dates in Excel with different date formats?

You can use dates in any computation ...READ MORE

answered Dec 29, 2022 in Others by narikkadan
• 63,720 points
1,032 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP