Converting text to date but 29 feb won't show, instead it shows 1 Mar

[avk5021]

Hi, I have date data that are texts and I have to convert them into dates. All other days are okay except this year which has 29 Feb. The formula I'm using to convert text to date is =DATE(RIGHT([@day],4),MID([@day],4,2),LEFT([@day],2)).
If someone can help me out would be much appreciated!

 

[Peter_SSs]

Perhaps you could also show us the text value that you are starting with? .. or even 3 or 4 examples

 

[avk5021]

01/03/2567

28/02/2567

28/02/2567

28/02/2567

29/02/2567

01/03/2567

My date data is in Thai at first. I would use the formula mentioned to convert them into date format, then use =DATE(YEAR([@[TH date]])-543,MONTH([@[TH date]]),DAY([@[TH date]])) to convert them into English date.

 

[Peter_SSs]

I would use the formula mentioned to convert them into date format, then use

The issue with that is if you are initially converting the text date to a numerical date using the DATE function, the year (2567) is not a leap year so Feb 29 does not exist and that is why you are getting 1 March.
I don't know much about Thai dates but does this work directly on your text values?

=DATE(RIGHT([@Day],4)-543,MID([@Day],4,2),LEFT([@Day],2))

 

[avk5021]

It does! Thanks a lot Peter! You've been helping me a lot. If you ever coming to Chiang Mai, Thailand do let me know. I wanna treat you a good meal.

 

[Peter_SSs]

It does! Thanks a lot Peter! You've been helping me a lot.

You're welcome. Thanks for the follow-up.

If you ever coming to Chiang Mai, Thailand do let me know. I wanna treat you a good meal.

I'll keep that in mind.