在Excel中将月份编号从完整日期单元格转换为文本月份 [英] Converting month number from full date cell into text month in Excel

问题描述

所以我有一列很长的名称和日期，例如：
14/01/2017 03:30

（它们是DD / MM / YYYY HH：MM）

So I have a pretty long column with name dates and hours, like this: 14/01/2017 03:30
(They are DD/MM/YYYY HH:MM)

我需要获取月份号并将其显示为文本，例如一月，二月等。
如果我将目标单元格格式设置为 MMM，则即使给定的月份是2、3或12，所有列单元格都显示1月。我还尝试了公式= TEXT（L2， mmmm）（因为给定的信息来自F列。

I need to get the month number and show it as text, like January, February, and so on. If I format the destination cell as 'MMM' all the column cells show January even if the given month is 2, 3, or let's say 12. I also tried the formula =TEXT(L2,"mmmm") (since the given info come from column F.

有什么想法吗？

推荐答案

诸如日期和时间之类的声音以文本形式存储，而不是以excel的序列日期格式存储。您可以通过将单元格的格式更改为一般格式，并查看如果什么都没有变化，那么文本是否会发生任何变化，对此进行测试。使用公式 = istext（A1），其中A1是其中带有日期的单元格。

sounds like your date and time are stored as text and not in excels serial date format. You can test this by changing the format of the cell to general and seeing if anything happens if nothing changes then its text. another way would be to use the formula =istext(A1) where A1 is a cell with a date in it.

现在假设您确实有文本格式的日期，我建议将它们转换为Excel日期序列。这样做的方法很多，我将向您展示一种方法。

Now ASSUMING you do have dates in text format, I would suggest converting them to excels date serial. There are numerous ways of doing this. I will show you one way.

假设您输入的日期是A1

This will strip each component of the text out and then recombine it. We will assume your date in is A1

去除年份

=MID(A1,7,4)

每月带走

=MID(A1,4,2)

每天抽空

=LEFT(A1,2)

现在可以重新组合它：

=DATE(MID(A1,7,4),MID(A1,4,2),LEFT(A1,2))

然后，excel可以转为整数并根据需要显示。

That will give you the date in an integer format that excel can then turn around and display as needed.

为了不浪费时间，您也应该将其剥离并与日期结合使用。请记住，日期是整数，时间是小数点后的所有内容。

In order not to lose the time component you should strip that part off as well and combine it with the date. Remember the date is an integer, and the time is everything after the decimal. Think of time as a fraction of a day.

要获取超时，请使用以下时间：

To get time out use:

=RIGHT(A1,5)

并将其转换为Excel时间：

and to convert it to excel time:

=TIMEVALUE(RIGHT(A1,5))

并简单地添加：

=DATE(MID(A1,7,4),MID(A1,4,2),LEFT(A1,2)+TIMEVALUE(RIGHT(A1,5))

整个过程的关键部分是您需要格式化该单元格以显示所需内容，您可以从下拉菜单中选择预格式化的样式，也可以应用自定义格式。将自定义格式设置为MMMM应该只显示月份的全名。

The key part to this whole thing is you will need to format that cell to display what you want. you can either choose a preformatted style from the drop down menu, or you can apply custom formatting. Setting the custom format to MMMM should display just the full name of the month.

如果您实际上需要月份的文本，而不仅是格式，那么您可以使用问题中的公式：

IF you actually need the text of the month and not just the format, then you could use the formula from your question:

=TEXT(<insert appropriate formula from above>,"mmmm")

这篇关于在Excel中将月份编号从完整日期单元格转换为文本月份的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！