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

**JessRI** · 09-11-2012, 05:41 AM

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

**Kevin UK** · 09-11-2012, 06:02 AM

Hi JessRI

Can you upload a sample workbook.

**dilipandey** · 09-11-2012, 06:08 AM

formatting the column to text before pasting,

After this you need to paste special values.. offcourse for this you need to first copy the data in some other excel workbook and then copy paste special here. thanks.

If you still face issue, upload a sample workbook.. thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

**JessRI** · 09-11-2012, 06:13 AM

Hi Kevin,
Think there is a sample file attached.
I've also tried formatting the cell as text before pasting, but because the dates use colons Excel still converts them.
Cheers
Jess

**Kevin UK** · 09-11-2012, 06:31 AM

Hi JessRI

You have me stumped on this one, I will still have a look at it.

When you apply General formatting to the dates it is: 83.79724537, strange as the number for 2nd August 2011 is: 40757

Some info on dates:

http://www.cpearson.com/excel/datetime.htm

**JessRI** · 09-11-2012, 06:37 AM

It is a curious one! I can solve it by copying and pasting just the dates on their own into Notepad, finding and replacing all : with a / and then copying back to Excel. This is still a fairly laborious way of covering 5million lines in different worksheets, and I'm sure there must be an easier way! Thanks for your help so far!

**Kevin UK** · 09-11-2012, 06:39 AM

Can you post sample data of the dates in txt file!

**Pete_UK** · 09-11-2012, 06:44 AM

Hi Jess,

You can put this formula in E2:

=DATE(INT(C2)*24+HOUR(C2),MINUTE(C2),SECOND(C2))

Format it as a date, and then copy down.

You can then convert those formulae to values by highlighting the column, click <copy>, then right-click and choose Paste Special | Values | OK then press <Esc> key. Then you can copy the values and paste over those in column C, then delete column E.

Hope this helps.

Pete

**JessRI** · 09-11-2012, 09:01 AM

Thanks Pete, that works a treat! I knew there must be a way to do it :-)
Excellent forum, I will be sure to check here in future when I come across my next problem!

**JessRI** · 09-11-2012, 09:05 AM

Hi Kevin,
Here is a sample of the text files which I use to input data into Excel.
Cheers
Jess

**Pete_UK** · 09-11-2012, 09:06 AM

You're welcome, Jess - glad to be of help.

Excel had treated those values as if they were times because of the colons, even though you knew they were really dates.

Pete

**Kevin UK** · 09-11-2012, 09:39 AM

Thanks Jess

That had me stumped. At least you got it sorted.

Kevin

**Kevin UK** · 09-11-2012, 09:55 AM

Just for the record, quite simple straight from the text file

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

LinkBack

Thread Tools

Rate This Thread

Display

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

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

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

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

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

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

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

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

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

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

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

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

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

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions