Excel日期格式 [英] Excel date format

问题描述

我有一个excel文件，日期格式如下：20120529

我可以手动将其更改为29-05-2012，但需要很长时间因为我有五千多行，有没有办法为每一行自动更改日期格式？

解决方案

我假设你正在处理一个字符串/数字。如果是日期，只需将日期格式化为 dd-mm-yyyy 。

否则，您可以打破它

  =（右（A1,2）&/& ;中间（A1,5,2）&/&左（A1,4））* 1 
  

然后通过格式化单元格格式化为 dd-mm-yyyy 。我认为如果区域设置在美国而不是英国可能会有一些问题。如果是前者，你会得到第一个公式的问题，我会使用：

  =（mid（A1,5， 2）&/& right（A1,2）&/& left（A1,4））* 1 
  

然后格式为 dd-mm-yyyy 。

I have an excel file with dates in the following format: 20120529

I could change it manually to 29-05-2012, but it will take a long time since I have more than five thousand rows, is there any way to change the date format automatically for every row?

解决方案

I assume you're dealing with a string/number. If it's a date, simply format the date to dd-mm-yyyy.

Otherwise, you could break it down using the string manipulation functions mid, left and right:

=(right(A1,2)&"/"&mid(A1,5,2)&"/"&left(A1,4))*1

Then format as dd-mm-yyyy through 'Format Cells'. I believe there might be some issues if the regional settings are in US instead of UK. If it's the former and you do get issues with the first formula, I would use:

=(mid(A1,5,2)&"/"&right(A1,2)&"/"&left(A1,4))*1

Then format as dd-mm-yyyy.

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