时间戳

首页 > 解决方案 > 如何确定 ReceivedTime 之前的工作日？

vba - 如何确定 ReceivedTime 之前的工作日？

问题描述

以下代码保存 Outlook 附件并将生成电子邮件的日期添加到名称的末尾。

我正在尝试添加前一个工作日。

Public Sub K_P3(itm As Outlook.MailItem)
    
    Dim objAtt As Outlook.Attachment
    
    Dim saveFolder As String
    saveFolder = "W:XXXX\"

    Dim dateformat As String
    dateformat = Format(itm.ReceivedTime, "yyyy-mm-dd Hmm ")
    
    For Each objAtt In itm.Attachments
        
        objAtt.SaveAsFile saveFolder & _
          Left(objAtt.FileName, InStrRev(objAtt.FileName, ".") - 1) & _
          " " & dateformat & _
          Mid(objAtt.FileName, InStrRev(objAtt.FileName, "."))
        
    Next
End Sub

标签： vbadateoutlook

解决方案

尝试这个：

    dateformat = Format(application.workday(itm.ReceivedTime),-1), "yyyy-mm-dd") & _
                 Format(itm.ReceivedTime, " Hmm")

编辑：忘记您使用的是 Outlook：

dateformat = Format(Workday(itm.ReceivedTime,1), "yyyy-mm-dd Hmm")

一些代码（我认为）模仿 Excel 的工作日功能：

Sub Tester()
    Dim arrHolidays
    arrHolidays = Array(#12/25/2019#) 'array of holidays
    
    Debug.Print Workday(#12/26/2019#, -1)              '12/25/2019
    Debug.Print Workday(#12/26/2019#, -1, arrHolidays) '12/24/2019
End Sub

'Find the workday `days` ago or in the future starting from `dt`.
'Ignores weekends, and optionally also an array of dates passed to `arrHols`
Function Workday(dt As Date, ByVal days, Optional arrHols As Variant = Empty)
    Dim rv, i, dy, delta, hol As Boolean
    rv = dt
    i = 0
    delta = IIf(days < 0, -1, 1) 'what direction are we headed?
    Do While i <> days
        rv = rv + delta
        dy = Weekday(rv, vbMonday)         'Monday=1
        If dy <> 6 And dy <> 7 Then        'not a weekend?
            'Int() below removes any time component from the date
            If Not IsEmpty(arrHols) Then hol = InArray(Int(rv), arrHols)
            If Not hol Then i = i + delta  'not a holiday?
        End If
    Loop
    Workday = rv
End Function

'Is value `v` in the array `arr` ?
Function InArray(ByVal v, arr) As Boolean
    Dim i As Long
    If IsEmpty(arr) Then Exit Function
    For i = LBound(arr) To UBound(arr)
        If arr(i) = v Then
            InArray = True
            Exit Function
        End If
    Next i
End Function

推荐阅读