convert time format on a whole file

I have a file with 20,000 dates in it that I need to convert and save back to a开发者_运维知识库 file. The dates are the only thing in the file and they are on separate lines.

This is what they are not

2011-01-03T16:44:09Z

and this is what I need

2011-01-03 12:34:00

If you have Ruby in your system, you can create a file like this:

date_parser.rb

require "date"

new_dates = File.open("new_dates.txt","w")

File.open("old_dates.txt","r") do |f| 
  while line = f.gets
    newdate = DateTime.parse(line)
    new_dates << newdate.strftime("%Y-%m-%d %H:%M:%S") << "\n"
  end
end

new_dates.close       

Then you just run

$ ruby date_parser.rb

All you new dates will be in "new_dates.txt" This script assumes you have a file "old_dates.txt" with each date in a different line.

In Excel, FTW.

=TEXT((SUBSTITUTE(SUBSTITUTE(A1,"Z"," "),"T"," "))-(5/24),"YYYY-MM-DD HH:MM:SS")

Christian is right, but i think u need lower case "date"

require "date"