Results 1 to 13 of 13

Convert Dates in 2011:08:17 format into Dates in Excel

Threaded View

  1. #1
    Registered User
    Join Date
    09-11-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    5

    Convert Dates in 2011:08:17 format into Dates in Excel

    Dear Forum,
    I'm sure this question has been asked plenty of times, and despite searching the Forum and wider internet I've not yet found an answer suitable to my problem. Any help would be greatly appreciated!

    I'm transferring date-time stamps from an .txt file in the format 2011:08:17 14:15. I paste this data into separate columns, one column for the date and one for the time. I find that excel automatically converts the date into a different format: it becomes "23/03/1900 19:08:17" or "83.80". I need to convert these into a format which reads 2011/08/17 so that Excel recognises it as 17th August 2011.
    I've tried pasting as text only (which continues to give me the 23/03/1900 format), formatting the column to text before pasting, find & replace ":" with "/" (which then gives "23/03/1900 19/08/17") and all manner of other things which don't work. How can I ask Excel to recognise this data as either a date, or even as just unconverted text?

    I have around 4million lines of data, spread across about 50 worksheets, so I can't use a manual function to change these dates as it will take me forever! I also cannot change the format of the original .txt files.

    This seems like such a frustrating problem, I have the data infront of my eyes but I just can't get Excel to see it the same way that I do! Please help! It might help if I point out that I am a Biologist, and these are the date-time stamps from wildlife cameras which I need to get into a workable format for analysis, so my knowledge of computer programming etc is fairly limited :-)
    Thanks in advance,
    Jess
    Last edited by JessRI; 09-11-2012 at 05:44 AM.

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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