Date time to Date conversion

[sharshra]

In the excel file I received, I have a date column in custom format (01/18/2024 17:14:08). From this, I have to extract the date in yyyymmdd format. I tried using Text & Int functions, but getting incorrect results. Refer the table below. Looks simple, but am I making any mistake? Experts in this forum, can you please advise how can I get the date in yyyymmdd format?

Book1
	B	C	D
1		Text	Int
2	01/18/2024 17:14:08	yyyy0118	01/18/2024 00:00:00
Sheet1

Cell Formulas
Range	Formula
C2	C2	=TEXT(B2,"yyyymmdd")
D2	D2	=INT(B2)

 

[Asbestos_Jen]

The value in B2 looks like it is stored as a serial number, so when you convert to date only with INT as in D2, the value is correct. You should just need to change the display format.

 

[Rick Rothstein]

Your formula works for me here in the US. Is 'y' the letter used in your locale for year? This alternative should work for you no matter what...

=TEXT(B2,"emmdd")

 

[sharshra]

Your formula works for me here in the US. Is 'y' the letter used in your locale for year? This alternative should work for you no matter what...

=TEXT(B2,"emmdd")

That´s quick & Brilliant Thanks a lot, Rick. You are right. In my locale, I have to use "aaaammdd" instead of "yyyymmdd" & it works

 

[Peter_SSs]

That´s quick & Brilliant Thanks a lot, Rick. You are right. In my locale, I have to use "aaaammdd" instead of "yyyymmdd" & it works

The marked solution has been changed accordingly. In your future questions, please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers. No further action is required for this thread.