+ Reply to Thread
Results 1 to 3 of 3

Excel interpret date as time even before I can change any settings

  1. #1
    Registered User
    Join Date
    05-16-2013
    Location
    Amsterdam, The Netherlands
    MS-Off Ver
    Excel 2010
    Posts
    1

    Excel interpret date as time even before I can change any settings

    Hi!

    I hope one of you can help me with the following problem:
    I'm working with a program to analyse and tag photos, so in this program I can change the metadata that comes with the photo's. This metadata can be exported to Excel by saving it as a separated by commas (.csv) file. I can import this file in Excel, change the settings so a comma means new column. So far, all works very well. However, my photoanalysing program stores the date as yyyy:mm:dd. Excel interpret this input as a time. So, date 2013:05:11 becomes 23-3-1900 21:05:11, even before I was able to change any settings to tell Excell that yyyy:mm:dd means yyyy/mm/dd. I can see, when I go over the cell with the date with my mouse, that the raw data is correct, it says 2013:05:11. I tried all settings in the 'home' menu and I made custom settings, but because Excel already interpret this date as time, I never get the right date. Is there a way to tell Excel that it should use the raw data and that it should not interpret it already?
    I hope my story is clear enough and I hope even more that someone can help me

    Thank you very much in advance!

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Excel interpret date as time even before I can change any settings

    However, my photoanalysing program stores the date as yyyy:mm:dd.
    1st thought -- Do you have any control over this in the photoanalysing program? One of the easiest solutions would be to have this program output the data as yyyy/mm/dd, which Excel should recognize just fine.

    2nd thought -- Since a CSV file is simply a text file with a .csv extension, it might be easiest to open the file in a text editor like notepad, execute a find ":" replace with "/", save the .csv file, then open in Excel. Again, we are simply making the dates look more like something Excel will recognize before we try to import into Excel.

    Final thought -- Instead of saving with the .csv extension, save with a .txt extension. Excel automatically tries to parse CSV files using built in assumptions. Most of the time when opening a TXT file, Excel will bring up the text import wizard, where you can control how Excel parses the data. You could tell Excel to treat this "date" field as text. Then, once you have the data imported, you can figure out how to convert the yyyy:mm:dd to a date rather than time.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Forum Expert judgeh59's Avatar
    Join Date
    02-07-2013
    Location
    Boise, Idaho
    MS-Off Ver
    Excel 2016
    Posts
    2,310

    Re: Excel interpret date as time even before I can change any settings

    check out Get External Data and then using text to columns....there is a function in there that allows you to set the date/time format....

    leave it Delimited - click Next
    UnClick all Delimters - Click Next
    Click on the Radio Button - Date: and chose YMD
    Click Finish
    Ernest

    Please consider adding a * if I helped

    Nothing drives me crazy - I'm always close enough to walk....

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1