how to convert timestamp in text format to actual

问题:

Timestamp in text is "Mon May 16 00:01:46 IST 2016". how should i convert this string into dd/mm/yyyy hh:mm:ss time format of excel ?

回答1:

you are going to need to go through a series of string manipulation and date time functions. lets start by assuming your string is in the A1 cell. In order to do this we are going to work from the largest unit (years) to the smallest unit (seconds). You can do it in any order as it will all be lumped into once formula, but for the breakdown of steps its good to have an order.

Step 1) PULL OUT THE YEAR

=RIGHT(A1,4)

That will give us the last 4 characters of the string which in this case is the year.

Step 2) PULL OUT THE MONTH

=MONTH(DATEVALUE(MID(A1,FIND(" ",A1)+1,FIND(" ",A1,FIND(" ",A1)+1)-FIND(" ",A1)-1)&"-"&1))

that ugliness looks at where the first and second spaces are and pulls out the string in between which is your month. It then converts it to a format that excel tends to recognize as a date short form by adding a - and the digit 1 to it. So in your case if would look like May-1. Datevalue converts this to an excel date serial, which we then pull back and grab the month from and in your case that is 5

If the above formula does not work for you it could be due to regional settings. If that is the case you can use the following:

=MATCH(MID(A1,FIND(" ",A1)+1,3),{"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"},0)

If you use this alternat formula , be sure to adjust the final equation accordingly.

Step 3) PULL OUT THE DAY

=TRIM(MID(A1,FIND(" ",A1,FIND(" ",A1)+1)+1,2))

So the above formula finds the second space and then starts pulling then next characters after it for a total of two characters. now since I do not know if the first of the month is 01 or just 1, I may wind up grabbing the space after the 1. The trim function removes excess spaces.

Step 4) BUILD THE DATE

The DATE function in excel requires the YEAR, MONTH, and DAY and converts those values in to the excel date serial. In this case we will convert:

=DATE(year,month,day)

to the following by substituting our equations from above:

=DATE(right(A1,4),MONTH(DATEVALUE(MID(A1,FIND(" ",A1)+1,FIND(" ",A1,FIND(" ",A1)+1)-FIND(" ",A1)-1)&"-"&1)),TRIM(MID(A1,FIND(" ",A1,FIND(" ",A1)+1)+1,2)))

Step 5) PULL THE TIME

I am going to assume this is a 24 hour clock since there is no AM/PM indicator. You could pull the time by pulling out the hours, minutes and seconds and similar to the DATE function, use the TIME function. However your time is in a vary recognizable format so lets just pull all the time at once. We will also cheat a bit since we can tell by your format all single digits in the time have a leading zero. This means your time string is a constant length of 8. Instead of finding the 3rd space which while possible makes the nested find statement really ugly, we will instead find the first : and wind up with the following:

=MID(A1,FIND(":",A1)-2,8)

Note we go back 2 characters from the first : and then pull a total of 8 character going right from that point. Now that we have that as a nice time string, we can convert it to time using the TIMEVALUE function as follows:

=TIMEVALUE(MID(A1,FIND(":",A1)-2,8))

Step 6) COMBINE DATE AND TIME

Since in excel the date is stored as an integer, and time is stored as a decimal, we can simply add the two together and store date and time in the same cell. We will achieve that by:

=DATE(right(A1,4),MONTH(DATEVALUE(MID(A1,FIND(" ",A1)+1,FIND(" ",A1,FIND(" ",A1)+1)-FIND(" ",A1)-1)&"-"&1)),TRIM(MID(A1,FIND(" ",A1,FIND(" ",A1)+1)+1,2)))+TIMEVALUE(MID(A1,FIND(":",A1)-2,8))

Step 7) FORMAT THE CELL

So for the cell with the formula in it, instead of GENERAL format, select CUSTOM format. In the bar where you can type, enter the following for your display format:

dd/mm/yyyy hh:mm:ss

ALTERNATE DATE METHOD

IF the formula in Step 2 works for you then could alternatively use the following as your date formula:

=DATEVALUE(TRIM(MID(A1,FIND(" ",A1,FIND(" ",A1)+1)+1,2))&"-"&A3&"-"&RIGHT(A1,4))

and your combined date time formula would be:

=DATEVALUE(TRIM(MID(A1,FIND(" ",A1,FIND(" ",A1)+1)+1,2))&"-"&A3&"-"&RIGHT(A1,4))+TIMEVALUE(MID(A1,FIND(":",A1)-2,8))

FORMULAS USED

Text/String Functions

• RIGHT
• MID
• FIND
• TRIM

Date/Time Functions

• DATE
• DATEVALUE
• TIMEVALUE
• MONTH