在Excel问题中将文本转换为日期 [英] Convert Text to Date in Excel Problems
问题描述
所以我打开excel，在G2中输入以下内容:UTC Feb/12/2016 01:09:28 PM
So I open excel, in G2 put this: Feb/12/2016 01:09:28 PM UTC
然后在其他任何单元格中尝试以下公式:
Then in any other cell try this formula:
=IFERROR(DATEVALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LEFT(G2,11),"Dec",12),"Nov",11),"Oct",10),"Sep",9),"Aug",8),"Jul",7),"Jun",6),"May",5),"Apr",4),"Mar",3),"Feb",2),"Jan",1))+TIMEVALUE(MID(G2,13,11)),"Error")
我正试图让它显示为2016年2月12日13:09
I am trying to get it to show like 2/12/2016 13:09
但是它给了我我的错误. iv尝试了一切，只是没有用??
But its giving me my error. Iv tried everything, just not working??
非常感谢你们
推荐答案
简短答案:
使用以下公式:
Short answer:
Use the following formula:
=DATE(MID(A1,8,4),MONTH(1&LEFT(A1,3)),MID(A1,5,2)) + IF(MID(A1, 22,2) = "PM", TIME(MID(A1, 13, 2), MID(A1, 16, 2), MID(A1, 19, 2)) + TIME(12, 0, 0), TIME(MID(A1, 13, 2), MID(A1, 16, 2), MID(A1, 19, 2)))
虽然没有必要，但最好将时间和日期分开 用于数据操作.
While not necessary, it's probably better to separate Time and Date for data manipulation.
我建议您使用隐藏的列，这样可以使您的约会变得漂亮且整齐，但此外，这也将帮助我解释答案，而不仅仅是粘贴一个巨大的公式:
What I'd recommend is to use hidden columns, this makes your date nice and neatly separated, but furthermore will also help me explaining the answer rather than just pasting a giant formula:
在假定条件下，您的文本信息始终具有相同的格式
"Mon"/dd/yyyy hh:mm:ss AM/PM UTC
在单元格B1中，我们将粘贴日期:
=DATE(MID(A1,8,4),MONTH(1&LEFT(A1,3)),MID(A1,5,2))
很容易解释，我们将提取日期格式的各个部分并将其应用于它们.唯一的窍门是使用MONTH()函数将月份字符串"转换为实际的月份"格式.
Pretty self explanatory, we'll extract individual parts of the date format and apply it to them. The only trick part is using the MONTH()function to convert the String of Month to an actual Month format.
现在，有点棘手了:
让我们尝试在C1中对其进行解析:
Let's try to parse it in C1:
首先，我们需要实现一个IF函数，它将AM/PM格式转换为24h格式，然后我们将使用字符串函数，就像上面的日期一样，将数据提取到我们的TIME()函数中
First, we'll need to implement an IF function, that will convert AM/PM format to 24h format and then we'll use string functions, similarly like in date above to extract data to our TIME() funciton.
=IF(MID(A1, 22,2) = "PM", TIME(MID(A1, 13, 2), MID(A1, 16, 2), MID(A1, 19, 2)) + TIME(12, 0, 0), TIME(MID(A1, 13, 2), MID(A1, 16, 2), MID(A1, 19, 2)))
最后但并非最不重要的一点，我们将在D列中粘贴结果. 首先，请确保您的格式已正确设置为预期的输出:
Last but not least, in the column D we'll paste our result. First make sure your format is set properly to your expected output:
，然后我们将两个数据字段简单地添加在一起:
and then we'll simply add our two data fields together:
=B1 + C1
产生预期的结果:
这篇关于在Excel问题中将文本转换为日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
