EXCEL VBA - 日期格式 [英] EXCEL VBA - Date formatting
问题描述
已编辑)
是这样吗?我需要对列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屋!