I have a spreadsheet of data extracted from a computer system. The data includes a column of timestamps each of which I want to split into a date and a time.
Say in cell A5 I have (in general format)
2016-12-25-04.16.26.330000
I want to get in cell B5 (in date format DD/MM/YYYY) 25/12/2016 and in cell C5 (in time format HH:MM:SS) 04:16:26.
There are over 10,000 lines - so I don't want to do this by hand! But I am sure you Excel wizards out there can show me a relatively easy way to do this.
Thanks in advance.
David
Related resources
Replies (15)
Please login or register to join the discussion.
I would use the text to columns function, splitting by delimiter of - (dash), you will have cells with year, month day and time. Then split the text to column by delimiter . on the newly created time column.
You can then in new columns use functions of =DATE(year,month,day) and =TIME(hour, minute, second) referring to the relevant column to create date and time formatted cells.
Assuming that there are the same number of characters on each line, I would used fixed width rather than delimited when using text to columns.
That's great, thanks a lot.
I have used
=DATE(MID(A5,1,4),MID(A5,6,2),MID(A5,9,2))
to get the date and
=TIME(MID(A5,12,2),MID(A5,15,2),MID(A5,18,2))
to get the time.
David
Or even:
=DATE(LEFT(A5,4),MID(A5,6,2),MID(A5,9,2)), and
=TIME(MID(A5,12,2),MID(A5,15,2),RIGHT(A5,2))
if you're feeling confident that the date stamps are a fixed length format.
Does that really work though, without converting all the text extractions to numeric by using the VALUE function?
It has worked. The cells I am splitting were in general format - not text format, which may be the reason.
David
P.S. It returns #VALUE! where I am attempting to split a blank cell.
Just an alternative, text to column will work anyway.
@left(A5,10) in B5 will return the 1st 10 characters of the cell (assumng no dashes in between the numbers - adjust accordingly). Then format the return cell as date.
@right(A5 ,15) in C5 will return the other time element, format cell accordingly. Again assuming no spaces in between dashes
Happy New Year
Assuming that the data is actually dates when imported into Excel, you could simply use the TEXT function to extract the date and time.
For example, the date would be =TEXT(A1,"dd/mm/yyyy")
and the time would be =TEXT(A1,"hh:mm").
If Excel does not import that data as dates you may need to mess around with the string formatting functions, but usually Excel can import OK if the data is consistently formatted.
Looks like Occam has been sharpening his razor!
I'm the Sales & Marketing Consultant from Cambridge!
Power Query (Get and Transform in Excel 2016) would also provide an effective solution without the need for formulae. The query would need to be built but it is simple.
More detail is probably superfluous at this stage as the question has been answered but always fun to see how many different ways a problem can be tackled. :-)
If it's an actual Excel date/time, rather than a text string, it's even easier:
For a date/time in e.g. cell A1, the formula =int(A1) will give the date, and the formula =A1-int(A1) will give the time. You will then need to format the cells as dates and times to view them more sensibly.
Many years ago I remember walking past a police financial investigator who was busy at his desk with a calculator adding up a column of figures in an EXCEL spreadsheet on his screen so he could type in the total at the bottom. (No kidding!)
Obviously Portia if you had been there you could have helped him out ....
David
I have worked with somebody that did the very same thing, David. And As far as I know, he has never been a police financial investigator.
