如何将文本格式的时间戳转换为 MS excel 的实际日期格式? [英] how to convert timestamp in text format to actual date format of MS excel?
问题描述
文本中的时间戳是Mon May 16 00:01:46 IST 2016".我应该如何将此字符串转换为 dd/mm/yyyy hh:mm:ss excel 的时间格式?
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 ?
推荐答案
您将需要经历一系列字符串操作和日期时间函数.让我们首先假设您的字符串在 A1 单元格中.为了做到这一点，我们将从最大的单位(年)到最小的单位(秒)进行工作.您可以按任何顺序进行操作，因为它会全部合并到一个公式中，但是对于步骤的细分，最好有一个订单.
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.
=RIGHT(A1,4)
这将为我们提供字符串的最后 4 个字符，在本例中为年份.
That will give us the last 4 characters of the string which in this case is the year.
=MONTH(DATEVALUE(MID(A1,FIND(" ",A1)+1,FIND(" ",A1,FIND(" ",A1)+1)-FIND(" ",A1)-1)&"-"&1))
那个丑陋的人会查看第一个和第二个空格的位置，然后拉出您所在月份的字符串.然后通过添加 - 和数字 1 将其转换为 excel 倾向于识别为日期短格式的格式.所以在你的情况下，如果看起来像 5 月 1 日.日期值将其转换为 excel 日期序列，然后我们将其拉回并从中获取月份，在您的情况下为 5
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.
=TRIM(MID(A1,FIND(" ",A1,FIND(" ",A1)+1)+1,2))
所以上面的公式找到第二个空格，然后开始拉它后面的下一个字符，总共两个字符.现在因为我不知道这个月的第一天是 01 还是只是 1，所以我可能会抓住 1 之后的空间.trim 函数删除了多余的空间.
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.
excel 中的 DATE 函数需要 YEAR、MONTH 和 DAY，并将这些值转换为 excel 日期序列.在这种情况下，我们将转换:
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)))
第 5 步)拖延时间
我将假设这是 24 小时制，因为没有 AM/PM 指示器.您可以通过拉出小时、分钟和秒来拉出时间，类似于 DATE 函数，使用 TIME 函数.但是，您的时间采用不同的可识别格式，因此让我们一次性拉取所有时间.我们也会作弊，因为我们可以通过您的格式判断时间中的所有单个数字都有前导零.这意味着你的时间字符串是一个长度为 8 的常量.而不是找到第三个空格，虽然可能会使嵌套的 find 语句变得非常丑陋，我们将找到第一个 : 并以以下内容结束:
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)
请注意，我们从第一个 : 返回 2 个字符，然后从该点向右拉出总共 8 个字符.既然我们已经将它作为一个不错的时间字符串，我们可以使用 TIMEVALUE 函数将其转换为时间，如下所示:
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))
第 6 步)结合日期和时间
由于在excel中日期存储为整数，时间存储为小数，我们可以简单地将两者相加并将日期和时间存储在同一单元格中.我们将通过以下方式实现:
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))
第 7 步)格式化单元格
因此，对于包含公式的单元格，请选择 CUSTOM 格式，而不是 GENERAL 格式.在您可以输入的栏中，为您的显示格式输入以下内容:
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
替代日期方法
如果第 2 步中的公式适合您，那么也可以使用以下作为您的日期公式:
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
日期/时间函数
这篇关于如何将文本格式的时间戳转换为 MS excel 的实际日期格式?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
