使用Excel更改日期格式 [英] Changing date formats in Excel using

问题描述

我有一个excel表，其中列的日期日期格式为yyyyMMdd，我想将其格式化为yyyy / MM / DD。

I have a excel sheet in which a column has date date in the format "yyyyMMdd" and I want to format it as "yyyy/MM/dd".

为此，我尝试在宏内使用以下行，但它将单元格数据转换为### .....＃而不是更改日期格式。 / p>

For this I tried to use following line inside macro, but it's converting cell data as "###.....#" instead of changing date format.

Sheet1.Range("C3", "C302").NumberFormat = "yyyy/mm/dd"
...
result = "#####...#"
...

有人可以告诉我为什么会这样吗？有没有其他方法来做这个？

Can someone tell me why it's happening? Is there any other way for doing this?

推荐答案

有了所有好的答案，我将添加简单的vba解决方案...

With all the good answers, I will add simple vba solution...

Option Explicit
Sub FormatDate()
    Dim xlRng As Range
    Dim xlShtRng As Range

    '//- Date format 20160112
    Set xlShtRng = [A3:A10] '//- or [A3, A6, A10]

    For Each xlRng In xlShtRng
        xlRng.Value = DateSerial(Left(xlRng.Value, 4), Mid(xlRng.Value, 5, 2), Right(xlRng.Value, 2))
        xlRng.NumberFormat = "yyyy/mm/dd" '//- 2016/01/12
    Next
End Sub

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