如何将文本格式的时间戳转换为MS excel的实际日期格式？ [英] how to convert timestamp in text format to actual date format of MS excel?

问题描述

文字中的时间戳是2016年5月16日00:01:46 IST 2016。
我应该如何将此字符串转换成dd / mm / yyyy hh：mm：ss时间格式excel？

解决方案

您将需要经历一系列字符串操作和日期时间功能。假设你的字符串在A1单元格中，我们开始吧。为了做到这一点，我们将从最大单位（年）到最小单位（秒）工作。您可以按任何顺序进行操作，因为它们都将被整合到一次配方中，但是对于步骤的细分，它可以有一个顺序。

步骤1）拉出年份

  = RIGHT（A1,4）
  

这将给我们最后4个字符的字符串，在这种情况下是一年。

步骤2）拉$ MONIT（DATEVALUE（MID，A1，FIND（，A1）+ 1，FIND（） ，A1，FIND（，A1）+1）-FIND（，A1）-1）& - & 1））

这个丑陋看着第一个和第二个空格在哪里，并拉出你的月份之间的字符串。然后，它将其转换为通过添加一个 - 和数字1来优先识别为日期缩写形式的格式。所以在你的情况下，如果看起来像5月1日。 Datevalue将其转换为excel日期序列号，然后我们拉回并从您的案例中获取该月份5。

如果上述公式对您无效这可能是由于区域设置。如果是这样，您可以使用以下内容：

  = MATCH（MID（A1，FIND（，A1）+ 1,3），{ 一月， 二月， 三月， 低年利率， 五一， 君， 七月， 八月， SEP， 十月， 十一月， Dec}，0）
  

如果使用这个替代公式，一定要调整最终方程

步骤3）推出日期

  = TRIM （MID（A1，FIND（，A1，FIND（，A1）+1）+1,2））
  

所以上面的公式找到第二个空格，然后开始拉，然后再下一个字符后总共两个字符。现在，由于我不知道这个月的第一个月是01还是只有1，我可以在1之后抓住空格。修剪功能删除多余的空格。

步骤4）BUILD THE DATE

excel中的DATE函数需要YEAR，MONTH和DAY，并将这些值转换为excel date serial。在这种情况下，我们将转换：

  = DATE（年，月，日）
  

代替以下代码：

  = DATE（右（A1,4），MONTH（DATEVALUE（MID（A1，FIND（，A1）+ 1，FIND（，A1，FIND（，A1）+1） （，A1）-1）& - & 1）），TRIM（MID（A1，FIND（，A1，FIND（，A1）+1）+1,2） 
  

步骤5）推迟时间

因为没有AM / PM指标，我会认为这是一个24小时钟。您可以通过拉出时间，分钟和秒钟，并与DATE功能相似，使用TIME功能。但是，您的时间是不同的可识别的格式，所以让我们一下子拉一次。我们也会作弊一点，因为我们可以通过你的格式告诉所有的单位数字在当时的领先零。这意味着你的时间字符串是一个恒定的长度8.而不是找到第三个空间，尽可能使嵌套的find语句真的很丑陋，我们将会找到第一个，并且找到第一个：并且结束如下：

  = MID（A1，FIND（：，A1）-2,8）
  

请注意，从第一个角度返回2个字符：然后从该点开始总共输入8个字符。现在我们把它作为一个很好的时间字符串，我们可以使用TIMEVALUE函数将它转换成时间，如下所示：

  = TIMEVALUE （MID（A1，FIND（：，A1）-2,8））
  

步骤6 ）组合日期和时间

由于在excel中，日期存储为一个整数，时间存储为十进制，我们可以简单地将两者一起添加并存储日期和时间在同一个单元格。我们将通过以下方式实现：

  = DATE（右（A1,4），MONTH（DATEVALUE（MID（A1，FIND ，A1）+ 1，FIND（，A1，FIND（，A1）+1）-FIND（，A1）-1）& - & 1）），TRIM （A1，FIND（，A1，FIND（，A1）+1）+1,2）））+ TIMEVALUE（MID（A1，FIND（：，A1）-2,8））
  

步骤7）格式化单元格

具有公式的单元格，而不是GENERAL格式，选择CUSTOM格式。在您可以输入的栏中输入以下显示格式：

  dd / mm / yyyy hh：mm：ss 
  

替代日期方法

如果公式在步骤2中，您可以使用以下日期公式：

  = DATEVALUE（TRIM（MID， FIND（，A1，FIND（，A1）+1）+1,2））& - & A3& - & RIGHT（A1,4））
  

，您的合并日期时间公式为：

  = DATEVALUE（TRIM（MID（A1，FIND（，A1，FIND（，A1）+1）+1,2））& - & A3& - & RIGHT（A1,4））+ TIMEVALUE（MID（A1，FIND（：，A1）-2,8））
  





使用公式

文本/字符串函数

• RIGHT


• MID


• 查找


• TRIM

日期/时间函数

• DATE


• DATEVALUE


• TIMEVALUE


• 月

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 ?

解决方案

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

这篇关于如何将文本格式的时间戳转换为MS excel的实际日期格式？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！