MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Excel date conversion

with 22 comments

I put together a post on how to upload to MySQL from a CSV file with dates. It was more or less for my students but one of them was curious how the mega formula worked. As commented, the easier solution is to create a custom format. Oddly, Open Office does support the MySQL default format natively.

Excel doesn’t support the native MySQL date format as a default format mask, which is YYYY-MM-DD, or 2009-06-02 for June 2, 2009. That means you have to convert it from a scalar date to a string or create a custom format mask (see Dmitri’s comment below). If you just shook your head at the term scalar date, maybe a custom format mask is best. However, if you want a programming solution let me explain that Excel supports only three data types. They’re a string literal, a numeric literal, and a formula. Dates in Excel are merely formatted numbers. When the numbers are integers, the date is a date, but when the number has a fractional component, the date is really a timestamp.

Here’s a brief description of the process required to convert a date in Excel into a MySQL date format string literal in a CSV file. You need the following Excel functions:

Date Functions

  • The DAY(date) function returns a 1 or 2 digit numeric value for the day of the month, with ranges of 1 to 28, 1 to 29, 1 to 30, or 1 to 31 dependent on the month and year.
  • The MONTH(date) function returns a 1 or 2 digit numeric value for the month of the year.
  • The YEAR(date) function returns a 4 digit numeric value for the year.

Logical Functions

  • The IF(logical_expression,truth_action,false_action) function returns the truth action when the expression is true, and the false action when the expression isn’t true.

MySQL Server

  • CONCATENATE(string_1, string_2, ...) glues strings together.
  • LEN(numeric_value) function returns the length of a string or number.

MySQL requires that you return an eight character string of numbers. The first four numbers must be a valid year, the fifth and sixth numbers a valid month, and the seventh and eigth numbers a valid day in the context of the year and month provided. Unfortunately, the DAY() and MONTH() functions may return a 1 or 2 digit value. That can’t happen in the CSV file’s string for a date, so you use the IF() and LEN() functions to guarantee a 2 digit return value.

Here are the examples that guarantee 2 digit day and month values, assuming that the base date is in the A1 cell. The concatenation of a "0" (zero between two double quotes) or the "" (two double quotes or a string null) ensures the number data types become strings.

=IF(LEN(DAY(A1))=1,CONCATENATE("0",DAY(A1)),DAY(A1))
=IF(LEN(MONTH(A1))=1,CONCATENATE("0",MONTH(A1)),MONTH(A1))

A zero is placed before the day or month when the logical condition is met, which means the day or month value is a single digit string. A null is place before the day or month when the logical condition isn’t met, which means the day or month value is a two digit string. There’s only one problem with these mega functions. They return a number.

The year calculation doesn’t require the explicit casting when you concatenate it with the other strings because it is implicitly cast as a string. However, it’s a better practice to include it for clarity (most folks don’t know about the implicit casting behaviors in Excel).

=CONCATENATE(YEAR(A1),"-",IF(LEN(MONTH(A1))=1,CONCATENATE("0",MONTH(A1)),MONTH(A1)),"-",IF(LEN(DAY(A1)) = 1,CONCATENATE("0",DAY(A1)),DAY(A1)))

As Goodwin reported in a comment, there’s an easier way that I missed. You can simply use the TEXT function when the source column is a valid serialized date value.

=TEXT(A1,"YYYYMMDD")

You can see the full MySQL import from CSV in the previous post. Naturally, you may want to copy and paste special the value before creating the CSV file. Also, don’t forget to delete any unused columns to the right or rows beneath because if you don’t your file won’t map to your table definition.

Written by maclochlainn

June 16th, 2009 at 8:15 pm

22 Responses to 'Excel date conversion'

Subscribe to comments with RSS or TrackBack to 'Excel date conversion'.

  1. This was a very helpful post as I often have to work with CSV import to a database and can relate to the issue because of DB2 UDB’s date format YYYY-MM-DD. However, couldn’t you have been able to acheive the same result by using a custom cell format?

    Emmanuel Garrido

    17 Jun 09 at 2:38 pm

  2. Thanks, you’re right. :-) I actually put it in the other post but forgot to put it here. I should have mentioned it as an alternative. I’ve updated the post.

    maclochlainn

    17 Jun 09 at 4:12 pm

  3. You can format cells in excel, select date field, than format cell, custom format and type yyyy-mm-dd. It will do all job!

    Dmitri

    10 Jun 10 at 8:30 am

  4. Absolutely true. It’s on my list to shot some screen art for that. Thanks!

    maclochlainn

    10 Jun 10 at 11:36 am

  5. =TEXT(A1,"YYYYMMDD")

    Should convert from a date integer to a formatted string for you.

    Goodwin

    7 Oct 10 at 5:28 am

  6. I believe there’s an error in the final formula. It’s missing the “=1″ condition for the months and so it always adds a “0”, even in the two digit months.

    It should be…

    =CONCATENATE(YEAR(A1),"-",IF(LEN(MONTH(A1))=1,CONCATENATE("0",MONTH(A1)),MONTH(A1)),"-",IF(LEN(DAY(A1))=1,CONCATENATE("0",DAY(A1)),DAY(A1)))

    But I never would have gotten it without the article!

    Matt

    7 Jan 11 at 12:44 pm

  7. Dimitri’s comment was sooooooooooo helpul:

    “You can format cells in excel, select date field, than format cell, custom format and type yyyy-mm-dd. It will do all job!”

    Meira Epstein

    24 Apr 11 at 7:29 am

  8. [...] Excel date conversion [...]

  9. Too long I guess, you can simply

    =YEAR(B2)&RIGHT("00"&MONTH(B2),2)&RIGHT("00"&DAY(B2),2)

    Denmas

    5 Jul 11 at 5:21 am

  10. If the date value in excel is 40544.

    SELECT '1900-01-01' + INTERVAL (40544 - 1 - 1) DAY

    It will return ‘2011-01-01′.

    NOTE: -1 in the last is for date bug. (see, http://www.cpearson.com/excel/datetime.htm)

    Jun

    22 Sep 11 at 5:50 pm

  11. “You can format cells in excel, select date field, than format cell, custom format and type yyyy-mm-dd. It will do all job!”

    THIS IS THE BEST AND SIMPLEST WAY TO MAKE IT WORK. REALLY THANK YOU DMITRI =)

    Linh

    17 Jan 12 at 2:17 am

  12. Yes, I agree. However, some folks want a macro to do it for them. That was the genesis of the article. However, I should go back and update how you create custom formats. Thanks.

    maclochlainn

    17 Jan 12 at 9:07 am

  13. I encountered a similiar problem – how to convert Excel dates like 02/04/2011 to the text format YYYYMMDD. Seems easy at first until you realise that the Day and Month values need to account for a leading zero where they are less than 10!

    =IF(LEN(DAY(D2))=1,CONCATENATE(YEAR(D2),IF(LEN(MONTH(D2))=1,CONCATENATE("0",MONTH(D2)),MONTH(D2)),"0",DAY(D2)),CONCATENATE(YEAR(D2),IF(LEN(MONTH(D2))=1,CONCATENATE("0",MONTH(D2)),MONTH(D2)),DAY(D2)))

    Andy

    31 Jan 12 at 10:00 am

  14. My experience with mySQL 5.1 has been that

    YYYY-MM-DD is *the* supported format in mySQL import (now?)

    trying to import in the format YYYYMMDD gave very inconsistent results – nearest I could pin down is that any date with MM > 09 would result in a date of 0000-00-00 after data load.

    The easiest formula for me in Excel to convert an excel Date in another cell (assume A1 with 12/31/2011 in it) is:

    =TEXT(A1,"YYYY-MM-DD")

    that yields 2011-12-31 (this was in Excel 2007).

    And yes for CSV export, as soon as I turned on custom format for the Date column (column A) of YYYY-MM-DD, then it seems to import just fine (via phpmyAdmin 3.3.9 on mySQL 5.1.53)

    Hope that helps.

    Cog

    5 Feb 12 at 10:40 pm

  15. Yes, I went back and updated this blog post with the right format mask. While the YYYYMMDD generally worked, the YYYY-MM-DD is the supported format mask. Calling the CAST function highlights that. When I wrote the post both worked during import without a problem (and opting for the one without hyphens seemed easier for newbies), but that’s no longer true. I suspect that the behavior that accepted the YYYYMMDD format was a bug. If it’s any consolation, the concepts right in the Oracle Database 11g and MySQL 5.6 Developer Handbook that I wrote last year. :-) I blogged about the new book here.

    maclochlainn

    5 Feb 12 at 10:52 pm

  16. Hi all! I’ve tried Dimitri’s solution, but didn’t work for me :( My java code is still throwing an incorrect date format error.

    Abhishek

    16 Jun 12 at 10:51 am

  17. Jun: thx that was clear solution for me, u save my day ^^

    memnon666

    2 Jul 12 at 4:06 pm

  18. Datetime works too:

    =TEXT(A1,”YYYY-MM-DD HH:MM:SS”)

    Dan

    5 Jul 12 at 1:47 pm

  19. Hi all.

    What if I want to change the datevalues ie 38901 to 03/07/2006 in text and not just for one cell, but for multiple columns in excel?

    Example:

    spreadsheet i have contains:

    Column 1, Column 2, Column 3, Column 4…….
    38901, stock price, 38901, stock price……

    Thanks!

    Sam

    27 Aug 12 at 7:43 pm

  20. Similar issues to Sam. MySQL converted dates in Excel from eg 05/05/1999 to 36285. I need to convert this back to a date format but don’t know what this number is! Any help appreciated.

    Steve

    29 Oct 12 at 5:27 am

  21. The number is the integer for the date. Import it and change the formatting and you should be fine.

    maclochlainn

    29 Oct 12 at 11:23 am

  22. I have learned date and time functions through this informative website . PERFECT!

    sam

    25 Feb 14 at 9:25 am

Leave a Reply