最后一个工作日(Microsoft Excel)
在开发工作表时,您可能需要知道给定月份的最后一个工作日。假设您的工作日为星期一至星期五,则以下公式将返回所需的日期:
=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及更高版本)找到本技巧的版本: