Bob tự hỏi liệu có cách nào dễ dàng để xác định xem năm của một ngày cụ thể có phải là năm nhuận hay không, nhằm mục đích tính lãi và số ngày gộp.

Chúng ta đều biết rằng nếu một năm chia hết cho 4, thì đó là một năm nhuận, phải không? Thực ra, điều đó là sai. Vì vậy, bạn không thể chia một số năm cho 4 một cách đơn giản như sau để xác định xem đó có phải là năm nhuận hay không:

=IF(MOD(YEAR(A1), 4) = 0, "Leap", "Regular")

Công thức sẽ hoạt động cho tất cả các ngày trong thế kỷ này (năm 2000 đến 2099), nhưng nó sẽ không hoạt động đáng tin cậy bên ngoài phạm vi đó. Lý do là vì “chia hết cho 4” không phải là bài kiểm tra duy nhất cho một năm nhuận. Nếu năm đó là năm thế kỷ (chia hết cho 100), thì đó là năm nhuận nếu nó chia hết cho 400. Vì vậy, năm 2000 là một năm nhuận, nhưng năm 2100 thì không.

Nếu bạn muốn có một cách chắc chắn để xác định xem một ngày có xảy ra trong năm nhuận hay không, thì công thức của bạn cần phải phức tạp hơn:

=IF(OR(AND(MOD(YEAR(A1),4)=0,MOD(YEAR(A1),100)>0), MOD(YEAR(A1),400)=0),"Leap","Regular")

Lưu ý rằng đây là một công thức duy nhất; nó được hiển thị ở đây trên hai dòng để nó hoàn toàn phù hợp trên màn hình. Công thức kiểm tra tất cả ba điều kiện cần thiết để xác định xem một ngày có rơi vào năm nhuận hay không.

Tất nhiên, một cách tiếp cận khác là chỉ cần tìm xem một năm nhất định có ngày 29 tháng 2 bên trong nó hay không. Có bất kỳ công thức nào có thể được sử dụng để thực hiện nhiệm vụ này:

=IF(DAY(EOMONTH("1Jan"&YEAR(A1),1))=29, "Leap", "Regular")

=IF(MONTH(DATE(YEAR(A1),2,29))=2, "Leap", "Regular")

=IF(DAY(DATE(YEAR(A1),2,29))=29, "Leap", "Regular")

=IF(DAY(DATE(YEAR(A1),3,0))=29, "Leap", "Regular")

Bất kỳ phương pháp nào được mô tả cho đến nay đều hoạt động tốt cho bất kỳ ngày nào được Excel hỗ trợ, ngoại trừ một ngoại lệ. Những cái sử dụng hàm DATE (ba cái cuối cùng được hiển thị ở trên) sẽ không hoạt động nếu ngày trong ô A1 là năm 1900. Những cái đó sẽ luôn trả về rằng 1900 là một năm nhuận, mặc dù không phải vậy. (Thật thú vị, hàm EOMONTH không có vấn đề giống như hàm DATE về vấn đề này.)

Nếu bạn cần làm việc với các ngày trước ngày được Excel hỗ trợ (trước ngày 1 tháng 1 năm 1900), thì bạn sẽ cần phải làm việc trực tiếp với năm thay vì kéo năm từ ngày Excel. Biến thể của công thức ba kiểm tra sẽ hoạt động tốt, nếu ô A1 chỉ chứa một năm:

=IF(OR(AND(MOD(A1,4)=0,MOD(A1,100)>0),MOD(A1,400)=0),"Leap","Regular")

Bạn cũng có thể tạo một hàm do người dùng xác định để kiểm tra ngày tháng.

Cách tiếp cận này sẽ hoạt động tốt bất kể ô chứa giá trị ngày tháng hay giá trị văn bản cho ngày tháng. (Nếu bạn nhập một giá trị ngày tháng không được hỗ trợ vào một ô — chẳng hạn như 1/1896 — thì Excel sẽ coi mục nhập như một giá trị văn bản thay vì cố gắng phân tích cú pháp nó thành một ngày.) Ví dụ sau hoạt động chính xác với tất cả các ngày được hỗ trợ bởi VBA, nằm trong khoảng từ năm 100 đến năm 9999:

Function IsLeap1(c As Range) As Boolean     YearNo = Year(c.Value)

If YearNo Mod 100 = 0 Then        IsLeap1 = ((YearNo \ 100) Mod 4 = 0)

Else        IsLeap1 = (YearNo Mod 4 = 0)

End If End Function

Để sử dụng hàm trong trang tính của bạn, bạn sẽ nhập một công thức như sau:

=IF(IsLeap1(A1),"Leap", "Regular")

Macro một dòng sau hoạt động bằng cách sử dụng thủ thuật tìm xem có ngày 29 tháng 2 trong năm được đề cập hay không:

Function IsLeap2(c As Range)

IsLeap2 = IsDate("2/29/" & Year(c.Value))

End Function

Macro được tham chiếu theo cùng một cách trong trang tính của bạn với macro trước đó:

=IF(IsLeap2(A1),"Leap", "Regular")

Nếu bạn cần nhiều cách sáng tạo hơn để xác định xem một ngày có phải trong năm nhuận hay không, hãy xem bài viết này:

http://chandoo.org/wp/2012/02/29/check-leap-year-using-excel/

_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 (9978) áp dụng cho Microsoft Excel 2007, 2010, 2013, 2016, 2019 và Excel trong Office 365.