Convert Unix Date-time stamp to excel date and/or time columns

**judikz** · 05-13-2015, 04:38 PM

I have a few sheets that contain a column with a Unix time-date stamp of the format: 2014-10-21T04:00:00.000Z

(for midnight October 21, 2014). Is there a way to convert this into two excel columns—one for date, the other for time?

Thanks

**daffodil11** · 05-13-2015, 04:57 PM

=DATEVALUE(LEFT(A1,10))
=TIMEVALUE(MID(A1,12,8))

Format the first cell as date
Format the second cell as time. I prefer a custom "hh:mm:ss".

**judikz** · 05-13-2015, 05:40 PM

You are awesome!!!!

Thank-you so much Daffodil11, I was toiling for hours : )

**daffodil11** · 05-13-2015, 05:46 PM

No problem, glad I could help out. I love me some string manipulations.

Convert Unix Date-time stamp to excel date and/or time columns

LinkBack

Thread Tools

Rate This Thread

Display

Convert Unix Date-time stamp to excel date and/or time columns

Re: Convert Unix Date-time stamp to excel date and/or time columns

Re: Convert Unix Date-time stamp to excel date and/or time columns

Re: Convert Unix Date-time stamp to excel date and/or time columns

Thread Information

Users Browsing this Thread

Similar Threads

Power Query convert Unix time to a date

[SOLVED] Convert Unix timestamp to Readable Date/time

[SOLVED] Date to Unix time stamp?

[SOLVED] Excel Date Time to Unix Time

Excel Date Time to Unix Time

Bookmarks

Bookmarks

Posting Permissions