Excel Guides

Parsing Non-Standard Date Formats in Excel

Parsing non-standard date formats in Excel can be a challenge. There are a few different ways to approach this, and the best method will depend on the format of the data you're working with. In this article, we'll explore a few different methods for parsing non-standard dates in Excel.

One common way to parse non-standard dates is to use the Text to Columns feature. This can be used if the date format is fairly consistent, and you can specify the delimiter that separates the date parts. For example, if you have a date format like "MMDDYYYY", you can use the Text to Columns feature to split it into three columns, using the "-" character as the delimiter.

Another approach is to use a combination of the Find and Replace feature and Excel's built-in date functions. This can be useful if the date format is not consistent, or if you need to parse dates that are in multiple formats. For example, let's say you have a column of dates that are in various formats, like "MM/DD/YYYY", "DD/MM/YYYY", or "YYYYMMDD". You can use Find and Replace to replace all of the "/" characters with nothing, which will leave you with just the numbers. Then, you can use Excel's DATEVALUE function to convert those numbers into dates.

If you're working with data that includes timestamps, things can get a bit more complicated. One approach is to use Excel's TIMEVALUE function to convert the timestamp into a time, then add that time to the date using the DATEADD function. For example, let's say you have a timestamp in this format: "MMDDYYYY HH:MM:SS". You can use TIMEVALUE to convert the HH:MM:SS part into a time, then add that time to the date using DATEADD.

There are many other ways to parse non=standard dates in Excel - these are just a few of the most common methods. If you have any tips or tricks for parsing non=standard dates in Excel, feel free to share them in the comments!

Move beyond 

Excel

Get started with Causal today.
Build models effortlessly, connect them directly to your data, and share them with interactive dashboards and beautiful visuals.