Regarding date format

Question

I have an excel data where it is a date but in 15.03.30 format. I wanted to change to date format but it turns out to be 15 Mar 1930. I need it to be 15 Mar 2030. What can I do?

Answer 1

=DATE(RIGHT(A2, 2) + 100, MID(A2, 4, 2), LEFT(A2, 2))

In this formula, the number 100 has been added, which gives the difference in years between 1930 and 2030.

Your formula returns nonsense:

A6: =A5
A7: =A6

Same number format in A5:A7 as in A1:A3 shown in my last post.

B1: =DATE(RIGHT(A1, 2) + 100, MID(A1, 4, 2), LEFT(A1, 2))

drag down

Same number format as in column A

C1: =B1

Numberformat in column C: General

The question was how to convert the date 15-Mar-1930 to 15-Mar-2030, IMHO simply add 100 years:

A9: =A7+36525

or

A9: =DATE(YEAR(A7)+100,MONTH(A7),DAY(A7))

Sample file:
https://www.dropbox.com/scl/fi/14gfvp18m79qzrcf5xt9z/e24d86fd-10a9-4cf3-b929-de8c6cc267b4.xlsx?rlkey=7uhmx6tomca7blm7a9tuk3pnx&st=zdgj8mg6&dl=1

Andreas.

Answer 2

Have you used this formula?

=DATE(RIGHT(A2, 2) + 100, MID(A2, 4, 2), LEFT(A2, 2))

In this formula, the number 100 has been added, which gives the difference in years between 1930 and 2030.

Alternatively you can use this formula:

=LET(

 date, A2, 

 dvalue, DATEVALUE(SUBSTITUTE(date, ".", "/")), 

DATE(YEAR(dvalue) + 100, MONTH(dvalue), DAY(dvalue))

)

Answer 3

A date in Excel is a number! NEVER USE TEXT FUNCTIONS ON DATES!!!

A2: =A1
A3: =A2
A1 NumberFormat: General

A2 NumberFormat: [$-de-DE]dd.mm.yy

A3 NumberFormat:  [$-en-US]ddmmmyyyy

Andreas.

Answer 4

Hi IlirU,

Thank you for the reply and assistance. My concern is that the 15.03.30 is actually 15Mar2030 but when convert the way you advise on excel it turn out to be 15Mar1930. how to make it 15Mar2030?

Answer 5

Let suppose your date is given in cell A2. In cell B2 apply this fomula:

=DATE(RIGHT(A2, 2) + 100, MID(A2, 4, 2), LEFT(A2, 2))

Then go to:

• Format Cells
• Custom
• Type and apply this format: dd mm yyyy

Or you can apply this below formula, if you want results as a text:

=TEXT(DATE(RIGHT(A2, 2) + 100, MID(A2, 4, 2), LEFT(A2, 2)), "dd mmm yyyy")

Hope this helps.