آخرین روز کاری

Question

هنگام ایجاد یک کاربرگ، ممکن است نیاز داشته باشید که آخرین روز کاری یک ماه مشخص را بدانید. با فرض اینکه روزهای کاری شما از دوشنبه تا جمعه ادامه دارد، فرمول زیر تاریخ مورد نظر را برمی گرداند:

=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 است. این استفاده به دلیل رسیدگی به خطای مورد نیاز گنجانده شده است.

تابع دوم، NoWeekendds، برای پشتیبان گیری تاریخ تا جمعه قبل استفاده می شود، اگر اتفاقاً شنبه یا یکشنبه باشد. تابع MakeItFriday برای اطمینان از اینکه تاریخ همیشه جمعه خواهد بود استفاده می شود.

برای استفاده از این تابع تعریف شده توسط کاربر از کاربرگ خود، از آن در فرمولی مانند زیر استفاده می کنید:

=LastWorkDay(A1, HolidayList, TRUE)

اولین پارامتر (A1) تاریخی است که باید ارزیابی شود. پارامتر دوم (HolidayList) یک لیست اختیاری از تاریخ تعطیلات است. همانطور که در اینجا نشان داده شده است، فرض می شود که HolidayList یک محدوده نامگذاری شده در کاربرگ است. اگر این پارامتر ارائه شده باشد، تابع مطمئن می شود که هر تاریخی که برمی گرداند در لیست تاریخ های HolidayList نباشد.

پارامتر نهایی نیز اختیاری است. می تواند درست یا نادرست باشد. (پیش فرض، اگر مشخص نشده باشد، FALSE است.) اگر این پارامتر روی TRUE تنظیم شود، تابع همیشه آخرین جمعه ماه را برمی گرداند. اگر این پارامتر TRUE باشد و HolidayList ارائه شده باشد، تابع آخرین جمعه غیر تعطیل ماه را برمی گرداند.