EXCEL VBA - 日期格式 [英] EXCEL VBA - Date formatting

查看:163
本文介绍了EXCEL VBA - 日期格式的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

已编辑)
是这样吗?我需要对列P做同样的处罚。我应该做另一个循环吗?

Edited) is this right? I need to do the same thign for column P. Should I do another for loop?

Dim i As Long
        For i = 1 To Rows.Count
        If Len(Cells(i, "Q").Value) <= 4 Then
        Cells(i, 1).NumberFormat = "01/01/yyyy"
        Else: Cells(i, "Q").NumberFormat = "MM/DD/YYYY"
        End If
        Next i

当我导入excel文件进​​行访问,导入后无法正确显示列表(19xx)或完整日期的列。
这几年似乎很好,但是完整的日期被更改为随机数,如39213等。
所以我试图将整个列格式化为文本并导出到访问,同样的事情发生。
我该怎么办?
原始列为一般格式

When I import excel file to access, the column that has either the years(19xx) or a full date don't display properly after imported. The years seem fine but full date is changed to a random number like 39213, etc. So I tried to format the whole column to text and export to access, the same thing happens. What should I do? The original column is in 'general' format

01/01/1962
01/01/1966
01/01/1956
  1964
01/01/1943
01/01/1943
01/01/1964
  1964
01/01/1972
01/01/1948
01/01/1961
01/01/1953
01/01/1961
01/01/1963
01/01/1963
01/01/1973
  1960
01/01/1956
01/01/1940
  1958
1958
1955
01/01/1948
01/01/1948
01/01/1970
  1959
  1964
01/01/1975
  1966

这将成为

22647
24108
20455
1964
15707
15707
19
23377
1964
26299
17533
22282
19360
22282
23012
23012
26665
1960
20455
14611
1958
1958
1955
17533
17533
25569
1959
1964
27395
1966

后一列为文本格式。我不知道是什么错误

And the latter column is in 'Text' format. I dont know what is wrong

推荐答案

我认为日期格式应该是所有行的MM / DD / YYYY Q。我已经修改了你的代码。

I think the date format should be "MM/DD/YYYY" across all the rows of "Q". I have modified your code slightly.

Sub test1()
            k = Cells(Rows.Count, "Q").End(xlUp).row
            Dim i As Long
            For i = 1 To k
            If Len(Cells(i, "Q").Value) <= 4 Then
            Cells(i, "Q").Value = "01/01/" & Cells(i, "Q").Value
            Cells(i, "Q").NumberFormat = "MM/DD/YYYY"
            Else: Cells(i, "Q").NumberFormat = "MM/DD/YYYY"
            End If
            Next i
End Sub

执行Q列后的所有日期将被格式化为MM / DD / YYYY

After executing all the dates in the column "Q" will be formatted to "MM/DD/YYYY"

如果要保留格式化年份Q列中的4位数字和第1列中的年份格式,然后使用以下代码

And if you want to keep the formatting year with 4 digits in the Q column and format that year separately in column 1, then use the below code

Sub test1()
            k = Cells(Rows.Count, "Q").End(xlUp).row
            Dim i As Long
            For i = 1 To k
            If Len(Cells(i, "Q").Value) <= 4 Then
            Cells(i, 1).Value = "01/01/" & Cells(i, "Q").Value
            Cells(i, 1).NumberFormat = "MM/DD/YYYY"
            Else: Cells(i, "Q").NumberFormat = "MM/DD/YYYY"
            End If
            Next i
End Sub

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

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆