←Back to Beyond Market Intelligence
•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community

Converting dates into formatted dates

Our take

Converting non-delimited dates into a standardized format can be challenging, especially when dealing with varying lengths like 6, 7, or 8 digits. Your initial approach using the DATE function was a solid start, but inconsistencies in the dataset may require a multi-step solution. Given the diversity in date formats such as mdyyyy and mmddyyyy, exploring a combination of functions, including text manipulation and error handling, could yield the desired mm/dd/yyyy format.

Hello, I am working with a dataset that has non delimited dates that I want to convert into real dates.

I originally attempted to use =DATE(RIGHT(A1,4), MID(A1,3,2), LEFT(A1,2))

This worked for many of them. The issues is that they are not all stored as 8 digits.

I also tried the text to columns with fixed width and also did not work.

Some are 6 7 or 8.

mdyyyy

mddyyyy

mmdyyyy

mmddyyyy

I would like to convert these into mm/dd/yyyy if possible.

Any help would be great. Thank you so much!!

Edit: Thank you everyone for your help looking into this. The 7s seem to be the great issue. But I am going to try a combo of u/mrslobster and u/hg00000.

We are also going to contact the surveillance site about the issues without leading 0s on the dates. Thank you.

submitted by /u/SavageHulk869
[link] [comments]

Read on the original site

Open the publisher's page for the full experience

View original article →

Related Articles

Tagged with

#Excel alternatives for data analysis#natural language processing for spreadsheets#generative AI for data analysis#financial modeling with spreadsheets#rows.com#Excel compatibility#large dataset processing#real-time data collaboration#real-time collaboration#Excel alternatives#formatted dates#convert#mm/dd/yyyy#real dates#non delimited dates#DATE function#text to columns#dataset#fixed width#mmddyyyy