Khi phát triển một trang tính, bạn có thể cần biết ngày làm việc cuối cùng của một tháng nhất định. Giả sử rằng các ngày làm việc của bạn chạy từ Thứ Hai đến Thứ Sáu, công thức sau sẽ trả về ngày mong muốn:

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

Công thức này trả về một ngày chỉ từ Thứ Hai đến Thứ Sáu và luôn là ngày cuối cùng trong tháng được biểu thị bằng ngày trong A1. Đối với một số mục đích, bạn có thể cần biết thứ Sáu cuối cùng của bất kỳ tháng nào là ngày thứ mấy. Điều này dễ dàng xác định với công thức này:

=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

Công thức này tính ngày cuối cùng của tháng cho ngày trong ô A1 và dựa trên ngày đó là ngày nào trong tuần, trừ đi số ngày thích hợp để trả về thứ Sáu trước đó.

Nếu bạn muốn tính đến ngày nghỉ của doanh nghiệp, thì độ phức tạp của công thức sẽ khá cao, khá nhanh. Do đó, cách tốt nhất là tạo một hàm do người dùng xác định (một macro) sẽ xác định ngày làm việc cuối cùng và bù đắp cho ngày nghỉ.

Macro sau đây trả về một ngày, từ Thứ Hai đến Thứ Sáu, đại diện cho ngày làm việc cuối cùng. Ngày được so sánh với danh sách ngày nghỉ (HolidayList), phải là một phạm vi được đặt tên trong sổ làm việc của bạn. Nếu ngày được phát hiện là ngày nghỉ, thì ngày kết thúc làm việc sẽ giảm xuống cho đến khi xác định được ngày thích hợp.

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

Lưu ý rằng có ba chức năng riêng được bao gồm. Các hàm này được gọi từ trong hàm LastWorkDay chính. Phương thức đầu tiên, myMatch, là “trình bao bọc” cho phương thức Đối sánh thông thường. Việc sử dụng này được bao gồm vì xử lý lỗi bắt buộc.

Chức năng thứ hai, NoWeekdends, được sử dụng để sao lưu một ngày đến thứ Sáu trước đó nếu nó chỉ xảy ra vào thứ Bảy hoặc Chủ nhật. Hàm MakeItFriday được sử dụng để đảm bảo rằng một ngày sẽ luôn là thứ Sáu.

Để sử dụng hàm do người dùng định nghĩa này từ trang tính của bạn, bạn sử dụng nó trong một công thức, như sau:

=LastWorkDay(A1, HolidayList, TRUE)

Tham số đầu tiên (A1) là ngày được đánh giá. Tham số thứ hai (HolidayList) là danh sách các ngày nghỉ tùy chọn. Như được hiển thị ở đây, giả định rằng HolidayList là một phạm vi được đặt tên trong trang tính.

Nếu tham số này được cung cấp, thì hàm đảm bảo rằng bất kỳ ngày nào nó trả về không có trong danh sách các ngày trong HolidayList.

Tham số cuối cùng cũng là tùy chọn; nó có thể là TRUE hoặc FALSE.

(Giá trị mặc định, nếu nó không được chỉ định, là FALSE.) Nếu tham số này được đặt thành TRUE, thì hàm luôn trả về thứ sáu cuối cùng của tháng. Nếu tham số này là TRUE và HolidayList được cung cấp, thì hàm sẽ trả về ngày thứ Sáu không nghỉ cuối cùng của tháng.

_Lưu ý: _

Nếu bạn muốn biết cách sử dụng các macro được mô tả trên trang này (hoặc trên bất kỳ trang nào khác trên trang ExcelTips), tôi đã chuẩn bị một trang đặc biệt bao gồm thông tin hữu ích.

ExcelTips là nguồn của bạn để đào tạo Microsoft Excel hiệu quả về chi phí.

Mẹo này (2452) áp dụng cho Microsoft Excel 97, 2000, 2002 và 2003. Bạn có thể tìm thấy phiên bản của mẹo này cho giao diện ribbon của Excel (Excel 2007 trở lên) tại đây: