SOLVED

Excel Date

%3CLINGO-SUB%20id%3D%22lingo-sub-2258614%22%20slang%3D%22en-US%22%3EExcel%20Date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2258614%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%26nbsp%3B%20date%20column%20as%201.1.2021.%20and%20i%20would%20like%20to%20convert%20it%20as%2020210101.%20can%20anyone%20help%20me%20%2C%2C%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2258614%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Occasional Contributor

I have  date column as 1.1.2021. and i would like to convert it as 20210101. can anyone help me ,,,

 

Thanks in advance.

6 Replies

@UserExcel 

Does the column contain 'real' dates, or are they text values that look like dates?

they look like date and are in 1.1.2021 form , but i converted to 1.1.2021  and are date column now....

@UserExcel 

Let's say the dates are in A2 and down.

Enter the following formula in B2 and fill down:

 

=TEXT(DATE(RIGHT(A2,4),MID(A2,FIND(".",A2)+1,FIND(".",A2,FIND(".",A2)+1)-FIND(".",A2)-1),LEFT(A2,FIND(".",A2)-1)),"yyyymmdd")

best response confirmed by UserExcel (Occasional Contributor)
Solution
Thank you for your solution but I found out an easy way to change to the format i need... Format on custom ... and type the format you need,, in the space provided as Type: yyyymmdd. Problem solved !!!

@UserExcel 

That means that the values were already real dates, and not text values that look like dates. Your reply to my earlier question made me think it was the latter.

Anyways Thank you for your help....