vba_date

VBA | 2018-09-06 15:28:12 | 阅读 98 次 | 评论(0)
Sub mydate_deal()
Dim r_max, r_min, r As Integer
r_max = Range("b65500").End(xlUp).Row
r_min = Range("k65500").End(xlUp).Row
 y = Year(Now())
    dm = Array(31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31)
    If ((y Mod 400 = 0) Or (y Mod 4 = 0 And y Mod 100 <> 0)) Then
        dm(1) = 29
    End If

For r = r_min To r_max
If (Cells(r, 2) <> "") Then
     date_ = Cells(r, 2)
         Select Case Cells(r, 12)
 
             Case "AMS 15 Days"
               Cells(r, 11) = DateSerial(Year(date_), (Month(date_) + 1), 15) '下月15日
                Cells(r, 11) = Format(Cells(r, 11), "yyyy/mmm/dd")
             Case "AMS 30 Days"
               Cells(r, 11) = DateSerial(Year(date_), (Month(date_) + 1), dm(Month(date_)))  '下月底日
                Cells(r, 11) = Format(Cells(r, 11), "yyyy/mmm/dd")
             Case "AMS 60 Days"
               Cells(r, 11) = DateSerial(Year(date_), (Month(date_) + 2), dm(Month(date_) + 1)) '下下月底日
                Cells(r, 11) = Format(Cells(r, 11), "yyyy/mmm/dd")
             Case "NET 60 Days"
               Cells(r, 11) = DateAdd("d", 60, Cells(r, 2)) '60天
               Cells(r, 11) = Format(Cells(r, 11), "yyyy/mmm/dd")
       End Select
End If
 If (Cells(r, 2) = "") Then
Cells(r, 11) = Cells(r - 1, 11)   '填寫空白點
 ' Cells(r, 11) = Format(Cells(r, 11), "yyyy/mmm/dd")
 End If
 Next

End Sub



文章评论,共0条
游客请输入验证码
浏览159885次
最新评论