将列的数字格式转换为日期"dd.mm.yyyy"； [英] Convert Number Format of a Column into Date "dd.mm.yyyy"

问题描述

我想将G，H和I列的数字格式设置为短日期格式(dd.mm.yyyy).

I want to set the number format of the columns G, H and I to short-date format (dd.mm.yyyy).

sheet.Range("G1").End(xlDown).NumberFormat = "dd.mm.yyyy"
sheet.Range("H1").End(xlDown).NumberFormat = "dd.mm.yyyy"
sheet.Range("I1").End(xlDown).NumberFormat = "dd.mm.yyyy"

单元格G84(此列的最后一个单元格)，H2，I2(这些列的第一个单元格)现在采用适当的格式，其余的仍然只是数字.

The cells G84 (last cell filled of this column), H2, I2 (first cells filled of these columns) are now in a proper format, the rest is still just a number.

推荐答案

来自宏记录器":

Sub Macro1()
    Columns("G:I").NumberFormat = "m/d/yyyy"
End Sub

您会得到的:

如果要将列中的第一个单元格的单元格移到空格之前的第一个单元格，则可以:

If you want to get the cells from the first cell in a column to the first cell before an empty space then this would be ok:

Sub Macro1()
    With Sheet1
        .Range(.Range("E1"), .Range("E1").End(xlDown)) = .NumberFormat = "m/d/yyyy"
    End With
End Sub

要获取从 E1 到E列中最后使用的单元格的范围:

To get the range from E1 to the last used cell in column E:

Sub Macro1()
    With Sheet1
        MsgBox .Range(.Range("E1"), .Range("E" & .Rows.Count).End(xlUp)).Address
        .Range(.Range("E1"), .Range("E" & .Rows.Count).End(xlUp)).NumberFormat = "m/d/yyyy"
    End With
End Sub

这篇关于将列的数字格式转换为日期"dd.mm.yyyy"；的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！