在开发工作表时,您可能需要知道给定月份的最后一个工作日。假设您的工作日为星期一至星期五,则以下公式将返回所需的日期:

=DATE(YEAR(A1),MONTH(A1)+1,0)-(MAX(0,WEEKDAY (DATE(YEAR(A1),MONTH(A1)+1,0),2)-5))

此公式返回的日期只有星期一至星期五,并且始终是由A1中的日期表示的月份中的最后一天。出于某些目的,您可能需要知道给定月份的最后一个星期五是什么。可通过以下公式轻松确定:

=DATE(YEAR(A1),MONTH(A1)+1,0)-WEEKDAY(DATE (YEAR(A1),MONTH(A1)+1,0))+(WEEKDAY(DATE (YEAR(A1),MONTH(A1)+1,0))>5)*7-1

该公式计算单元格A1中日期的月份的最后一天,并基于该日期是星期几,减去适当的天数以返回上一个星期五。

如果您要考虑到商务假期,那么公式的复杂性就会变得很高,非常快。因此,最好创建一个用户定义的函数(宏),该函数将确定最后一个工作日并补偿假期。

以下宏返回从星期一到星期五的日期,该日期代表最后一个工作日。将日期与假日列表(HolidayList)进行比较,该假日列表应是工作簿中的命名范围。如果发现该日期是假日,则减少最后一个工作日,直到找到合适的日期为止。

Function LastWorkDay(lRawDate As Long, _     Optional rHolidayList As Range, _     Optional bFriday As Boolean = False) As Long

LastWorkDay = DateSerial(Year(lRawDate), _       Month(lRawDate) + 1, 0) - 0     If bFriday Then         LastWorkDay = MakeItFriday(LastWorkDay)

Else         LastWorkDay = NoWeekends(LastWorkDay)

End If

If Not rHolidayList Is Nothing Then         Do Until myMatch(LastWorkDay, rHolidayList) = 0             LastWorkDay = LastWorkDay - 1             If bFriday Then                 LastWorkDay = MakeItFriday(LastWorkDay)

Else                 LastWorkDay = NoWeekends(LastWorkDay)

End If         Loop     End If End Function
Private Function myMatch(vValue, rng As Range) As Long     myMatch = 0     On Error Resume Next     myMatch = Application.WorksheetFunction _         .Match(vValue, rng, 0)

On Error GoTo 0 End Function
Private Function NoWeekends(lLastDay As Long) As Long     NoWeekends = lLastDay     If Weekday(lLastDay) = vbSunday Then _       NoWeekends = NoWeekends - 2     If Weekday(lLastDay) = vbSaturday Then _       NoWeekends = NoWeekends - 1 End Function
Private Function MakeItFriday(lLastDay As Long) As Long     MakeItFriday = lLastDay     While Weekday(MakeItFriday) <> vbFriday         MakeItFriday = MakeItFriday - 1     Wend End Function

请注意,其中包含三个私有函数。这些函数从LastWorkDay主函数中调用。第一个是myMatch,是常规Match方法的“包装器”。包含此用法是因为需要进行错误处理。

第二个功能NoWeekdends,用于恰好是星期六或星期日,用于将日期备份到上一个星期五。 MakeItFriday函数用于确保日期始终为星期五。

要在工作表中使用此用户定义的函数,请在公式中使用它,如下所示:

=LastWorkDay(A1, HolidayList, TRUE)

第一个参数(A1)是要评估的日期。第二个参数(HolidayList)是假日日期的可选列表。如此处所示,假设HolidayList是工作表中的命名范围。

如果提供了此参数,则该函数将确保它返回的任何日期不在HolidayList中的日期列表中。

最后一个参数也是可选的。它可以是TRUE或FALSE。

(如果未指定,则默认值为FALSE。)如果将此参数设置为TRUE,则该函数始终返回该月的最后一个星期五。如果此参数为TRUE并且提供了HolidayList,则该函数返回该月的最后一个非假日星期五。

注意:

如果您想知道如何使用此页面(或_ExcelTips_网站上的任何其他页面)中描述的宏,我准备了一个特殊页面,其中包含有用的信息。

_ExcelTips_是您进行经济高效的Microsoft Excel培训的来源。

本技巧(2452)适用于Microsoft Excel 97、2000、2002和2003。可以在以下功能区中为Excel的功能区界面(Excel 2007及更高版本)找到本技巧的版本: