楼主
DJ_Soo |
Q：edate函数可以返回某个日期之前或之后几个月的日期,但是遇到如4月30号往后推一个月,就不能判断30号是4月最后一天.结果为5月30，这个时候与我想要的5月31相差了一天.如何解决这样的问题呢?
A：用自定义函数解决此问题:
ALT+F11→插入模块→输入以下代码:- Function mDate(Start_Date As Date, Months As Integer) As Date
- Dim LstDay As Byte
- LstDay = Day(DateSerial(Year(Start_Date), Month(Start_Date) + 1, 0))
- If Day(Start_Date) = LstDay Then
- mDate = DateSerial(Year(Start_Date), Month(Start_Date) + Months + 1, 0)
- Else
- mDate = DateSerial(Year(Start_Date), Month(Start_Date) + Months, Day(Start_Date))
- End If
- End Function
测试:- Sub Test()
- Dim testDate As Date
- testDate = Application.InputBox("请输入测试日期", "测试", "2012-2-29")
- MsgBox mDate(testDate, 1)
- End Sub
默认测试结果为"2012-3-31"
当然,函数本身有edate也可以有"类似"效果,但是对于月末最后一天的判断还要根据需要自行选择是使用edate还是自定义.
测试中仅往后推一个月,测试日期可随意输入.
 mdate.rar
修改了一次代码:- Function mDate(Start_Date As Date, Months As Integer) As Date
- Dim LstDay As Byte
- LstDay = Day(DateSerial(Year(Start_Date), Month(Start_Date) + 1, 0))
- If Day(Start_Date) = LstDay Then
- mDate = DateSerial(Year(Start_Date), Month(Start_Date) + Months + 1, 0)
- Else
- mDate = DateSerial(Year(Start_Date), Month(Start_Date) + Months, Day(Start_Date))
- If Month(mDate) <> (Month(Start_Date) + Months) Mod 12 Then'增加一次判断,弥补二楼所指出的缺陷.
- mDate = DateSerial(Year(Start_Date), Month(Start_Date) + Months + 1, 0)
- End If
- End If
- End Function
再次修改..- Function mDate(Start_Date As Date, Months As Integer) As Date
- Dim LstDay As Byte
- LstDay = Day(DateSerial(Year(Start_Date), Month(Start_Date) + 1, 0))
- If Day(Start_Date) = LstDay Then
- mDate = DateSerial(Year(Start_Date), Month(Start_Date) + Months + 1, 0)
- Else
- mDate = DateSerial(Year(Start_Date), Month(Start_Date) + Months, Day(Start_Date))
- If (Month(mDate) - (Month(Start_Date) + Months) Mod 12) Mod 12 <> 0 Then '增加一次判断,弥补4楼所指出的缺陷.
- mDate = DateSerial(Year(Start_Date), Month(Start_Date) + Months + 1, 0)
- End If
- End If
- End Function
更简单的,VBA内置函数:
- Function mDate(Start_Date As Date, Months As Integer) As Date
- mDate=DateAdd("m",Months,Start_Date)
- End Function
|