Làm cách nào để kiểm tra xem một ngày có phải là ngày nghỉ lễ hay không và đếm ngày trừ ngày nghỉ trong Excel?
Ví dụ, bạn có một danh sách các ngày và muốn kiểm tra xem có ngày nghỉ lễ nào trong danh sách hay không, làm cách nào bạn có thể hoàn thành nhanh chóng? Trong bài viết này, tôi sẽ chỉ cho bạn các cách để tính toán tất cả các ngày của các ngày lễ của Mỹ trong những năm nhất định và sau đó kiểm tra xem một ngày có phải là ngày lễ của Mỹ trong Excel một cách dễ dàng hay không.
- Phần 1: Tính toán ngày nghỉ lễ trong những năm nhất định trong Excel
- Phần 2: Kiểm tra xem một ngày có phải là ngày lễ trong Excel hay không
- Phần 3: Đếm ngày giữa hai ngày ngoại trừ cuối tuần và ngày lễ trong Excel
Phần 1: Tính toán ngày nghỉ lễ với năm nhất định trong Excel
Trước khi kiểm tra xem một ngày có phải là ngày lễ hay không, bạn phải liệt kê tất cả các ngày lễ trong những năm nhất định trong Excel. Do đó, chuẩn bị một bảng như ảnh chụp màn hình được hiển thị sau đây sẽ giúp công việc của bạn dễ dàng hơn.
Có ba loại ngày lễ của Mỹ:
(1) Loại đầu tiên là ngày nghỉ lễ vào một ngày cố định, chẳng hạn như Ngày Tết là vào ngày 1 tháng Một. chúng ta có thể dễ dàng tính ngày Tết bằng công thức = DATE (Năm trước, 1,1);
(2) Loại thứ hai là ngày nghỉ lễ vào một ngày cố định trong tuần, chẳng hạn như Ngày Tổng thống. Chúng ta có thể dễ dàng tính ngày Tổng thống với công thức = DATE (Năm trước, 1,1) + 14 + LỰA CHỌN (TUẦN (NGÀY (Năm trước, 1,1)), 1,0,6,5,4,3,2);
(3) Và loại cuối cùng là Ngày tưởng niệm, chúng ta có thể dễ dàng tính được Ngày tưởng niệm bằng công thức = DATE (Năm trước, 6,1) -WEEKDAY (DATE (Năm trước, 6,6)).
Ở đây tôi liệt kê các công thức để tính tất cả các ngày nghỉ lễ trong bảng sau. Chỉ cần nhập các công thức vào ô thích hợp và nhấn đăng ký hạng mục thi từng phím một.
| Ngày lễ | Pin | Công thức |
| Ngày tết | C2 | = DATE (C1,1,1) |
| Ngày Martin Luther King Jr. | C3 | = DATE (C1,1,1) + 14 + LỰA CHỌN (TUẦN (DATE (C1,1,1)), 1,0,6,5,4,3,2) |
| Ngày tổng thống | C4 | = DATE (C1,2,1) + 14 + LỰA CHỌN (TUẦN (DATE (C1,2,1)), 1,0,6,5,4,3,2) |
| Lể chiến si trận vong | C5 | = DATE (C1,6,1) -WEEKDAY (DATE (C1,6,6)) |
| Independence Day | C6 | = DATE (C1,7,4) |
| Ngày lể lao động | C7 | = NGÀY (C1,9,1) + LỰA CHỌN (TUẦN (DATE (C1,9,1)), 1,0,6,5,4,3,2) |
| Ngày Columbus | C8 | = DATE (C1,10,1) + 7 + LỰA CHỌN (TUẦN (DATE (C1,10,1)), 1,0,6,5,4,3,2) |
| Ngày Cựu chiến binh | C9 | = DATE (C1,11,11) |
| Ngày Lễ Tạ Ơn (Thanksgiving) | C10 | = DATE (C1,11,1) + 21 + LỰA CHỌN (TUẦN (DATE (C1,11,1)), 4,3,2,1,0,6,5) |
| ngày Giáng Sinh | C11 | = DATE (C1,12,25) |
LƯU Ý Trong các công thức trong bảng trên, C1 là ô tham chiếu định vị năm nhất định. Trong ví dụ của chúng tôi, nó có nghĩa là Năm 2015 và bạn có thể thay đổi nó dựa trên nhu cầu của mình.
Với các công thức này, bạn có thể dễ dàng tính toán ngày của các ngày lễ với các năm nhất định. Xem ảnh chụp màn hình bên dưới:
Lưu một dải ô dưới dạng mục nhập Văn bản tự động (các định dạng và công thức ô còn lại) để sử dụng lại trong tương lai
Việc tham chiếu các ô và áp dụng các công thức để tính toán mỗi ngày lễ hẳn là rất tẻ nhạt. Kutools for Excel cung cấp một cách giải quyết dễ thương của Văn bản tự động tiện ích để lưu phạm vi dưới dạng mục nhập Văn bản tự động, có thể giữ nguyên các định dạng và công thức ô trong phạm vi. Và sau đó bạn sẽ sử dụng lại phạm vi này chỉ với một cú nhấp chuột. Công việc trở nên dễ dàng chỉ bằng một cú nhấp chuột để chèn bảng này và thay đổi năm trong bảng này!
Kutools for Excel - Supercharge Excel với hơn 300 công cụ thiết yếu. Tận hưởng đầy đủ tính năng 30-ngày dùng thử MIỄN PHÍ mà không cần thẻ tín dụng! Get It Now
Phần 2: Kiểm tra xem một ngày có phải là ngày lễ trong Excel hay không
Sau khi liệt kê tất cả các ngày nghỉ lễ của những năm cụ thể, chúng ta có thể dễ dàng kiểm tra xem một ngày có phải là ngày nghỉ lễ hay không bằng các công thức trong Excel. Giả sử bạn có một danh sách ngày như ảnh chụp màn hình sau đây và tôi sẽ giới thiệu các cách để thực hiện dễ dàng.
Chọn một ô trống bên cạnh danh sách ngày, cho biết Ô B18, nhập công thức = IF (COUNTIF ($ C $ 2: $ D $ 11, A18), "Ngày lễ", "Không") vào đó, rồi kéo Fill Handle đến phạm vi bạn cần. Xem ảnh chụp màn hình ở trên:
Chú ý:
(1) Trong công thức = IF (COUNTIF ($ C $ 2: $ D $ 11, A18), "Ngày lễ", "Không"), $ C $ 2: $ D $ 11 là phạm vi ngày nghỉ lễ trong những năm cụ thể và A18 là ô có ngày bạn muốn kiểm tra xem đó có phải là ngày lễ hay không và bạn có thể thay đổi chúng dựa trên nhu cầu của mình. Và công thức này sẽ trả về "Ngày lễ" nếu ngày cụ thể là ngày nghỉ lễ và trả về "Không" nếu ngày đó không phải.
(2) Bạn cũng có thể áp dụng công thức mảng này = IF (HOẶC ($ C $ 2: $ D $ 11 = A18), "Ngày lễ", "KHÔNG") để kiểm tra xem ngày tương ứng có phải là ngày lễ hay không.
Phần 3: Đếm ngày giữa hai ngày ngoại trừ cuối tuần và ngày lễ trong Excel
Trong Phần 1, chúng tôi đã liệt kê tất cả các ngày lễ trong một năm nhất định và bây giờ phương pháp này sẽ hướng dẫn bạn đếm số ngày ngoại trừ tất cả các ngày cuối tuần và ngày lễ trong một phạm vi ngày.
Chọn một ô trống, bạn sẽ trả về số ngày và nhập công thức = NETWORKDAYS (E1, E2, B2: B10) vào nó và nhấn đăng ký hạng mục thi Chìa khóa.
Chú thích: Trong các ô trên, E1 là ngày bắt đầu của phạm vi ngày được chỉ định, E2 là ngày kết thúc và B2: B10 là danh sách ngày nghỉ mà chúng tôi tính toán trong Phần 1.
Bây giờ bạn sẽ nhận được số ngày ngoại trừ cuối tuần và ngày lễ trong phạm vi ngày được chỉ định.
Sao chép công thức chính xác / tĩnh mà không thay đổi tham chiếu ô trong Excel
Kutools for Excel Bản sao chính xác tiện ích có thể giúp bạn dễ dàng sao chép chính xác nhiều công thức mà không cần thay đổi tham chiếu ô trong Excel, ngăn việc cập nhật tham chiếu ô tương đối tự động.
Kutools for Excel - Supercharge Excel với hơn 300 công cụ thiết yếu. Tận hưởng đầy đủ tính năng 30-ngày dùng thử MIỄN PHÍ mà không cần thẻ tín dụng! Get It Now
Công cụ năng suất văn phòng tốt nhất
Nâng cao kỹ năng Excel của bạn với Kutools for Excelvà trải nghiệm hiệu quả hơn bao giờ hết. Kutools for Excel Cung cấp hơn 300 tính năng nâng cao để tăng năng suất và tiết kiệm thời gian. Bấm vào đây để có được tính năng bạn cần nhất...
Office Tab Mang giao diện theo thẻ vào Office và giúp công việc của bạn dễ dàng hơn nhiều
- Cho phép chỉnh sửa và đọc theo thẻ trong Word, Excel, PowerPoint, Publisher, Access, Visio và Project.
- Mở và tạo nhiều tài liệu trong các tab mới của cùng một cửa sổ, thay vì trong các cửa sổ mới.
- Tăng 50% năng suất của bạn và giảm hàng trăm cú nhấp chuột cho bạn mỗi ngày!
