Converting a Text Date with 4 digit year to Date

%3CLINGO-SUB%20id%3D%22lingo-sub-2796100%22%20slang%3D%22en-US%22%3EConverting%20a%20Text%20Date%20with%204%20digit%20year%20to%20Date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2796100%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20list%20of%20text%20formatted%20dates%20with%204%20digit%20years%20and%20no%200%20padding%2C%20that%20I%20need%20converted%20to%20dates%20preserving%20the%204%20digit%20year%20and%20non-0%20padding.%26nbsp%3B%20Any%20suggestions%3F%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2796100%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2796229%22%20slang%3D%22en-US%22%3ERe%3A%20Converting%20a%20Text%20Date%20with%204%20digit%20year%20to%20Date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2796229%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1170387%22%20target%3D%22_blank%22%3E%40sirlarryoflondon%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDATEVALUE(YourTextDate)%26nbsp%3B%20then%20format%20it%20as%20YYYY%20(shortcut%20CTRL%2B1)%3C%2FP%3E%3CP%3Eor%3C%2FP%3E%3CP%3EText(DATEVALUE(YourTextDate)%2C%22yyyy%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I have a list of text formatted dates with 4 digit years and no 0 padding, that I need converted to dates preserving the 4 digit year and non-0 padding.  Any suggestions? 

2 Replies

@sirlarryoflondon 

 

DATEVALUE(YourTextDate)  then format it as YYYY (shortcut CTRL+1)

or

Text(DATEVALUE(YourTextDate),"yyyy")

 

 

@Juliano-Petrukio 

Thank you so much Juliano! I managed to find a quick cheat by:

 

1. Copying the entire column of "dates"

2. Opening and pasting into Notepad

3. Reformatting the column to text

4. Copying and pasting from Notepad back to the column and pasting as "value"

 

Cheers,

Larry