将excel中的单元格的日期格式从dd.mm.yyyy更改为yyy / mm / dd（Excel版本2013） [英] change date format of cell in excel from dd.mm.yyyy to yyy/mm/dd ( excel version 2013 )

问题描述

我已经搜索了一个小时，但不幸的是没有发现任何有效的方法。我有一个Excel工作表，其中包含日期。当前日期格式为 dd.mm.yyyy ，但我想将整个单元格的日期格式更改为 yyyy / mm / dd 。任何帮助将不胜感激。

I have been searching it for an hour but unfortunately nothing found that actually work. I have an excel sheet with a column having date in it. Current date format is dd.mm.yyyy but I want to change date format to yyyy/mm/dd for entire cell. Any help will be appreciated.

推荐答案

选项1）

假定您使用数字格式的日期dd.mm.yyyy存储为excel日期序列，并且仅格式化为dd.mm.yyyy显示，那么最好的解决方法是选择要修改的单元格。转到主页选项卡，然后选择数字格式并将其更改为常规。请参见下图中的绿色圆圈。如果该格式已设置为常规格式，或者当您将其切换为常规格式时，您的数字未更改，则很有可能您的dd.mm.yyyy格式的日期实际上是文本。并需要按照以下选项2进行转换。但是，如果将数字设置为常规时数字确实发生变化，请选择数字区域右下角的箭头（请参见红色圆圈）。

OPTION 1)

Assuming that you source date that is in the number format dd.mm.yyyy stored as an excel date serial and only formatted to display as dd.mm.yyyy then the best fix is to select the cells you want to modify. Go to your home tab, and select the number format and change it to General. See Green circles in image below. IF the format is already set to general, or when you switch it to general your numbers do not change, then it is most likely that your date in dd.mm.yyyy format is actually text. and will needed to be converted as per OPTION 2 below. However, if the number does change when you set it to general, select the arrow in the bottom right corner of the number area (see red circle).

单击红色圆圈中的箭头后，您将看到类似于以下屏幕：

After clicking the arrow in the red circle you should see a screen similar to the one below:

从左侧的类别列表中选择自定义，然后在类型栏中输入所需的格式yyyy / mm / dd。

Select Custom from the category list on the left, and then in the Type bar enter the format you want which is yyyy/mm/dd.

=date(Right(A1,4),mid(A1,4,2),left(A1,2))

这假设您的原始日期是存储在A1中的字符串，并将该字符串转换为日期序列以excel格式存储日期。 ¹ 您可以将此公式复制到旁边你约会。然后，您可以按上述方式为日期应用单元格格式，或者，如果该样式符合您的需要，则使用构建短日期或长日期。

This assumes your original date is a string stored in A1, and converts the string to a date serial in the form excel stores dates in.¹ You can copy this formula down beside you dates. You can then apply cell formatting for the date as described above, or use the build short or long date if that style matches your needs.

¹ _{Excel计算Windows版本的excel自1900年1月0日以来的天数。我相信mac是1904或1905。}

这篇关于将excel中的单元格的日期格式从dd.mm.yyyy更改为yyy / mm / dd（Excel版本2013）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！