问题描述
我写了下面的代码,它将创建一个csv文件并存储指定月份的日期。问题是我希望日期格式为mm / dd / yyyy但我得到的日期是2013年6月21日,而不是06/21/2013。能告诉我如何实现这一目标吗?以下是我的代码。让我知道我错在哪里:
Hi,
I have written below code which will create a csv file and store the dates for a month specified. The problem is i want the date format in mm/dd/yyyy but i am getting the date as 6/21/2013 instead of 06/21/2013. Can you please let me know how i can achieve this. Below is my code. Let me know where i am going wrong:
cMonth = Month(Now)
cYear = Year(Now)
cDays = Days(cMonth, cYear)
rowCount = ws.UsedRange.Rows.Count
Set newwkbk = Workbooks.Add
k = 0
For i = 6 To (rowCount - 4) Step 1
emp_cd = ws.Cells(i, 1).Value
shift_cd = ws.Cells(i, 2).Value
dateValue = ws.Cells(i, 3).Value
If dateValue <> "" Then
tempArray = Split(dateValue, "to")
For j = tempArray(0) To tempArray(1) Step 1
If j <= cDays Then
dtmNewDate = DateSerial(cYear, cMonth, j)
intDay = weekDay(dtmNewDate)
If (intDay <> 7 And intDay <> 1) Then
k = k + 1
With newwkbk.Worksheets(1)
.Cells(k, 1).Value = emp_cd
.Cells(k, 3).Value = shift_cd
dateFormatted1 = j & "/" & cMonth & "/" & cYear
dateFormatted = Format(dateFormatted1, "dd-mm-yyyy")
.Cells(k, 2).Value = dateFormatted
End With
End If
End If
Next j
End If
Next i
谢谢
Thanks
推荐答案
dateFormatted1 = j & "/" & cMonth & "/" & cYear
dateFormatted = Format(dateFormatted1, "dd-mm-yyyy")
.Cells(k, 2).Value = dateFormatted
带
With
.Cells(k, 2).NumberFormat = "mm/dd/yyyy" ' Change to dd/mm/yyyy if you prefer day first format
.Cells(k, 2).Value = CDate(cyear & "-" & cmonth & "-" & j) ' yyyy-mm-dd ISO format eliminates ambiguity of month first vs. day first formats
经过测试在Excel 2010上
默认情况下,Excel将日期值格式化为m / d / yyyy。通过设置NumberFormat属性,我们告诉Excel使用指定的格式而不是默认格式。
Tested on Excel 2010
By default, Excel formats a date value as m/d/yyyy. By setting the NumberFormat property, we tell Excel to use the format specified rather than the default format.
这篇关于用于将excel数据转换为csv文件的vba编码的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!