Converting Decimal-Separated Dates to Excel Date Format

Decimal Date LeadDates don’t always get imported into Excel in a nice, clean, ready to use format. When they don’t, it can be a huge hassle – un-formatted dates can’t be added or subtracted, or even filtered or sorted easily. It is usually best to convert them to the standard Excel date format. Excel has an entire array of functions you can use to work with them once you do (you can learn about them here). Excel’s string manipulation functions can help you convert date text to the Excel date format. Let’s walk through an example…

Decimal Separated Date Example

Assume we’ve imported a series of dates that are separated by decimals. For example, 29.09.2013 represents September 29, 2013. Here is a list of examples:

DataSet

We are going to use the LEFT, RIGHT, and MID functions along with the FIND function to separate out different parts of the date. Then, we can use the DATE function to stitch them together into Excel’s date format. The DATE function takes the year, month, and then day as inputs, so we’ll start by extracting the year…

Extracting the Year from Text

In our example, the year is always the last 4 characters in the date string, so it is easy to recover using the RIGHT function. The syntax is as follows:

=RIGHT(text, [num_chars])

Using the RIGHT function, we can specify the cell the date is in and then 4 to select the first 4 characters from the right. The formula for the first row in our example will be as follows:

=RIGHT(A1,4)

Extracting the Month from Text

The month is most complicated to pull out of the text string. We know that it will be in between two periods, but we don’t know where those periods will occur since months and days can have one or two characters each. This is where FIND comes in. We can use it to locate each decimal point and then guide a MID function to the correct location.

The FIND function has the following syntax:

=FIND(find_text, within_text, [start_num])

To find the first decimal, the basic function looks like this:

=FIND(".",A1)

To find the second decimal, we can nest two FIND functions together. The second FIND (plus 1), tells the first one where to start looking (just past the first decimal):

=FIND(".",A1,FIND(".",A1)+1)

We can use these functions to construct a MID function. MID has the following syntax:

=MID(text, start_num, num_chars)

We give MID A1 as it’s text input. The start_num input will be the first FIND statement. To get the number of characters, we need to know the distance between the first and second decimal.

We can do this by subtracting the first decimal location (the first FIND function) from the second. The final MID function looks like this:

=MID(A1,FIND(".",A1)+1,FIND(".",A1,FIND(".",A1)+1)-FIND(".",A1)-1)

We subtract one more from the difference in the num_chars because the FIND function finds the decimal and not the first character past it, so we actually need to subtract (FIND+1) from the second FIND.

Extracting the Day from Text

The day is similarly easy to extract to the year, only we use the LEFT function instead. The only difference is that we need to FIND the first decimal to determine how many characters we need. The syntax for LEFT is as follows:

=LEFT(text, [num_chars])

Using the first FIND formula from above, the extraction formula for the first row in our example is as follows:

=LEFT(A1,FIND(".",A1)-1)

Stitching the Year, Month, and Day into a DATE

Now that we have the individual components, we can stitch them together. The DATE function syntax is as follows:

=DATE(year, month, day)

If you’ve been building out the components, your spreadsheet might look something like this:

YearMonthDayExtraction

If so, you can build the DATE function to look at the formulas you have already made. The DATE function for the first row will look like this:

=DATE(B1,C1,D1)

However, if you need all the work to happen in one cell, you can combine all the previous formulas to create a monster:

=DATE(RIGHT(A1,4),
      MID(A1,FIND(".",A1)+1,
          FIND(".",A1,FIND(".",A1)+1)-FIND(".",A1)-1),
      LEFT(A1,FIND(".",A1)-1))

The end result in either case is a beautifully formatted Excel date:

FullDateConversion

Get the latest Excel tips and tricks by joining the newsletter!

Andrew Roberts ThumbnailAndrew Roberts has been solving business problems with Microsoft Excel for over a decade. Excel Tactics is dedicated to helping you master it. You can read more of his writing on his personal blog at NapkinMath.io.

Join the newsletter to stay on top of the latest articles. Sign up and you'll get a free guide with 10 time-saving keyboard shortcuts!

Other posts in this series...

8 thoughts on “Converting Decimal-Separated Dates to Excel Date Format

  1. I have found that it may be a little easier to use the “Text to columns” tool instead of having to write all of those formulas to split up the information. After separating them by the decimal place, you can use a simple “Concatenate” formula to put them back together in the correct order and format. Then you would copy and paste the new column as values and then format as a date.

    1. Hi Samantha,

      I agree – Text to Columns is another great resource for handling this type of problem. I like Excel’s string manipulation tools because you can use them to find and separate much more complicated strings too. Dates are a simpler case, and it’s good to have multiple approaches to solve it!

      Andrew

  2. How to parse a column of dates that are inconsistent: some are 9-10-77 some are 9/10/77, some are Sept 10, 1977, etc. and even 9/77. Standardizing multiple formats such as this requires “different” formulas.

  3. I think the easiest way is to highlight all the cells and use find/replace. Put find as “.” and replace as “/”. Then format the cells as dates.

    1. Genius. The formula gave me some wacky results – some were right and others were just odd, two of the fields would be right and the year would be totally wrong.

  4. I found that it was actually easier just to do a simple find & replace the decimals with backslashes and then excel will recognize it as dates! 🙂

Leave a Reply

Your email address will not be published. Required fields are marked *