WHEN WE EXPORT THE TIME STAMP FROM OUR SYSTEMS TO A CSV FILE, WE GET THE FOLLOWING NUMBER: :20150903132429 IN THE TIME STAMP COLUMN.
THE FIRST PART IS THE DATE 20150903, WHICH I NEED CONVERTED TO 2015/09/03
THE SECOND PART IS THE TIME STAMP 132429. WHICH IN NEED CONVERTED TO 01:24:29
HOW WOULD I GO ABOUT DOING THIS TASK? AND, CAN I HAVE BOTH DATE AND TIME CONVERTED TO THE SAME CELL I AM RUNNING THE FORMULA FOR THIS TASK?
=datevalue(left(text(A1,"0000-00-00"),8))&" "&if(A1>12,mid(A1,9,2)-12,mid(A1,9,2))&":"&mid(A1,11,2)&":"&right(A1,2)
A1 the cell you want to manipulate
8 is the first 8 charters on the right
9 is the 9th character, so start at 9
2 is the number of characters in from 9
Hope this works. If you find a shorter method let me know. I'm wondering if flash fill will work better, read up on it.
I have a similar problem with delimited data. I export a report and there is a column that supplies day, hour, minute in this format: 15d23h59m
What I would like to do is convert it to a value that can be calculated. I would ultimately like to be able to sum the days, hours and minutes to get an average time for the column.
I know I'm not providing an answer to the original question, but I feel that it's along the same lines and answers to this might potentially be helpful to others as well. I appreciate any feedback!!!
Chris
Hi Chris
Amy's example is a good starting point, in that it shows how to extract the numbers from inside a value that combines numbers and letters.
This formula should do the trick. It assumes that the text value in the report is in A1:
=LEFT(A1,2)*24+MID(A1,4,2)+MID(A1,7,2)/60 (read more about extracting numbers from text here - opens in a new window)
For your example, 15d23h59m, the formula does this:
Your example works out to 383.9833333 hours, which is a number you can then use in calculations.
The caveat with this solution is that I assume that the number of hours, days and minutes will always be in two digits, i.e. 08d03h06m rather than 8d3h6m. If your data has values like the latter, the solution becomes more difficult (but still possible).
I hope that helps - please reply if you have further questions.
Add new comment