如何在Excel中将文本转换为日期格式 [英] How to convert text to date format in Excel

问题描述

我需要按时间顺序对这一列进行排序，但是当我导入数据时，一些单元格被识别为一个自定义格式（A2-A4），而其他单元格则是一般格式（A5-11）。 A2-A4被认定为日期，但是A5-A11被认为是一般文本。 Excel不认识到这些都应该是日期。我试图格式化他们作为一个日期，但这是行不通的。我已经使用文本列来从数据中分离出时间，但是像A2-A4这样的单元格在单元格中留下了一个午夜的时间（例如2/10/2012 0:00:00），我仍然无法获得当我使用文本到列时，单元格显示为日期。我已经清除格式化，我已经尝试格式画家，我已经尝试= datevalue。我不知道还有什么要做的。另外，我注意到Excel中的这些单元格将这些值识别为日期，时间是在24小时内列出的，而其他时间则不在这里。

有人可以帮忙吗？

文字看起来像这样：

 
列A 
 2 2 / 10/2012 20:00 
 3 1/11/2012 3:00 
 4 5/12/2013 15:30 
 5 19/10/2012 2:00 pm 
 6 18/04/2013 2:00 pm 
 7 18/10/2013 2:30 pm 
 8 23/12/2013上午9:00 
 9 31/05/2013 1 ：00 pm 
 10 16/02/2013 6:00 pm 
 11 15/02/2013 6:00 pm 
 

对于以文本形式存储的单元格，我假定我们知道正在读取的日期的格式（即DD / MM / YYYY）会保持不变。对于像 DD / MM / YYYY HH：MM PM 这样的字符串，我们可以这样做： （1）一天之前必须存储在第一个斜杠之前的字符中，所以我们可以通过写入来提取它：

DAY_FORMULA：= LEFT（A1，FIND（/，A1,1）-1）

月份必须存储在第一个和第二个斜杠之间的字符中。它占据了一个或两个字符，所以我们可以写下面的内容：

$ b $ p $ MON $ __ MONORM_FORMULA = MID（A1，FIND（ / code

这里， FIND（/，A1，/ A1，1）+ 1，x）

1）+ 1 告诉Excel返回第一个 / 的位置：自从月份开始之后加一个。现在我们需要找到 x ，从这个点读取的字符数。由于第一个 / 在 FIND（/，A1,1），第二个在 FIND（/，A1，FIND（/，A1,1）+1），那么两个斜线之间的字符数由下式给出：

FIND（/，A1，FIND（/，A1,1）+1） - FIND 。

完整的 MONTH_FORMULA 可以写成：

MONTH_FORMULA：= MID（A1，FIND（/，A1,1）+1，FIND（/，A1，FIND（/，A1,1）+1）-FIND （3）年份包含在最后四位数字之后（/，A1,1）-1）

第二个斜线，所以我们可以写年：
$ b

YEAR_FORMULA：= MID（A1，FIND（/， A1，FIND（/，A1,1）+1）+1，4）

可以使用标准日期公式强制这些日期格式：
$ b $ p $ = DATE（YEAR_FORMULA，MONTH_FORMULA，DAY_FORMULA

$ b （4）如果你想保留关于小时的信息，那么如果领域中不存在前导空白（不是不能用 = TRIM ），您可以使用：
$ b

TIME_FORMULA：= TIMEVALUE（MID FIND（，A1,1）+1，LEN（A1）））

然后可以将日期添加到时间值获得完整的日期时间：
$ b $ p $ = DATE（YEAR_FORMULA，MONTH_FORMULA，DAY_FORMULA）+ TIME_FORMULA

如果日期格式正确，您不希望将字符串格式化为日期，那么您还可以添加 = IF



= IF（ISTEXT（A1），ADJUST，A1）

通过上面的步骤替换 ADJUST 的完整解决方案可能如下所示：

$ b

= IF（ISTEXT（A1），DATE（MID（A1，FIND（/，A1，FIND（/，A1,1）+ 1）+1），MID（A1，FIND（/，A1,1）+1，FIND（/，A1，FIND（/，A1,1）+1）-FIND / A1，1）-1，LEFT（A1，FIND（/，A1,1）-1））+ TIMEVALUE（MID，A1，FIND（，A1,1）+1，LEN A1））），A1）

I need to sort this column chronologically, however when I import the data, some of the cells are recognized as a a custom format (A2-A4) while others are in a general format (A5-11). A2-A4 is recognized as a date, however A5-A11 is being recognized as general text. Excel is not recognizing that these should all be dates. I have tried formatting them as a date, but this does not work. I have used text to columns to separate the time from the data, however cells such as A2-A4 leave behind a time of midnight in the cell (e.g. 2/10/2012 0:00:00) and I still can't get the cells to appear as dates when I use text to columns. I have cleared formatting, I have tried format painter, I have tried =datevalue. I'm not sure what else to do. Also, I noticed that the cells where Excel recognizes the values as dates, the time is listed in 24hr time, where as the others are not.

Can someone please help?

text looks like this:

        Column A
2    2/10/2012 20:00
3    1/11/2012 3:00
4    5/12/2013 15:30
5    19/10/2012 2:00 pm
6    18/04/2013 2:00 pm
7    18/10/2013 2:30 pm
8    23/12/2013 9:00 am
9    31/05/2013 1:00 pm
10   16/02/2013 6:00 pm
11   15/02/2013 6:00 pm

解决方案

I'll assume that, for some reason, adjusting the input data feed so that all the dates follow the convention assumed by Excel is impractical, because that's probably the easiest solution to your problem.

For the cells that are stored as text, I assume we know the format of the date that's being read (i.e. DD/MM/YYYY) and that it'll remain constant. For a string like DD/MM/YYYY HH:MM PM, we can then do the following:

(1) The day must be stored in the characters before the first slash, so we can extract it by writing:

DAY_FORMULA: =LEFT(A1, FIND("/", A1, 1)-1)

(2) The month must be stored in the characters between the first and the second slash. It occupies either one or two characters, so we can write the following:

MONTH_FORMULA: =MID(A1, FIND("/", A1, 1) + 1, x)

Here, FIND("/", A1, 1) + 1 tells Excel to return the location of the first /: since the month starts after that, we add one. Now we need to find x, the number of characters to read from that point. Since the first / is at FIND("/", A1, 1) and the second is at FIND("/", A1, FIND("/", A1, 1)+1), then the number of characters between the two slashes is given by:

FIND("/", A1, FIND("/", A1, 1)+1) - FIND("/", A1, 1) - 1.

The complete MONTH_FORMULA can be written as:

MONTH_FORMULA: =MID(A1, FIND("/", A1, 1)+1, FIND("/", A1, FIND("/", A1, 1)+1)-FIND("/", A1, 1)-1)

(3) The year is contained in the last four digits after the second slash, so we can write the year as:

YEAR_FORMULA: =MID(A1, FIND("/", A1, FIND("/", A1, 1)+1)+1, 4)

You can then use the standard date formula to force these into a date format:

=DATE(YEAR_FORMULA, MONTH_FORMULA, DAY_FORMULA

(4) If you want to keep information about the hours, then if no leading blanks exist in the fields (not that this couldn't be circumvented with a =TRIM), you can use:

TIME_FORMULA: =TIMEVALUE(MID(A1, FIND(" ", A1, 1)+1, LEN(A1)))

You can then add the date to the time value to obtain the full date-time:

=DATE(YEAR_FORMULA, MONTH_FORMULA, DAY_FORMULA) + TIME_FORMULA

You don't want to try to format a string into a date if the date is already properly formatted, so you could also add an =IF statement before performing any of the operations above:

=IF(ISTEXT(A1), ADJUST, A1)

The full solution, replacing ADJUST by the steps above, might look something like this:

=IF(ISTEXT(A1), DATE(MID(A1, FIND("/", A1, FIND("/", A1, 1)+1)+1, 4), MID(A1, FIND("/", A1, 1)+1, FIND("/", A1, FIND("/", A1, 1)+1)-FIND("/", A1, 1)-1), LEFT(A1, FIND("/", A1, 1)-1))+TIMEVALUE(MID(A1, FIND(" ", A1, 1)+1, LEN(A1))), A1)

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