Convert text date format to number date format

Question

I have stored some data entrys in a CSV file in following Format:
Thu Jul 28 08:42:33 GMT+01:00 2016
and need to convert it to just a time stamp (eg. h:m:s). How can I quickly and easily do this?

Using what programming language? Or is this sql loader? You need to be way more specific in order to get adequate help. — Gnqz, Aug 16 '16 at 14:51
A little more information might be helpful. While some of us (not me) are god-like geniuses, none of us are mind readers. — Matthew Brown aka Lord Matt, Aug 16 '16 at 16:44

score 1 · Answer 1 · answered Aug 16 '16 at 14:17

1

To convert a text date in a cell to a serial number, you use the DATEVALUE function. Then you copy the formula, select the cells that contain the text dates, and use Paste Special to apply a date format to them. Select a blank cell and verify that its number format is General.

answered Aug 16 '16 at 14:17

Mustafa Gondalwala

81
1
5

Can I do this using LIbreOffice? – Aug 16 '16 at 14:36
Using LibreOffice, `DATEVALUE` on the time stamp gives "Err:502". However I will not downvote this answer, because the `libreoffice` tag was added *after* this answer was written. – Jim K Aug 16 '16 at 21:50

score 0 · Answer 2 · answered Aug 16 '16 at 21:59

0

If the size is fixed, then assuming the string is in A1:

=MID(A1,12,8)

The result can then be converted to an actual time value using TIMEVALUE.

answered Aug 16 '16 at 21:59

Jim K

12,824
2
22
51

score -1 · Answer 3 · answered Aug 16 '16 at 16:06

-1

Looks like a simple call to a single function would extract the substring you want, since it begins at a specific offset and only runs for eight characters (two hh plus colon plus two mm plus colon plus two ss).

answered Aug 16 '16 at 16:06

Bruce David Wilner

463
2
4

Convert text date format to number date format

3 Answers3