Bỏ qua nội dung chính

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ễ 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.

doc kiểm tra xem ngày lễ 1

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:

doc kiểm tra xem ngày lễ 2

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 cho mỗi ngày nghỉ hẳn là rất tẻ nhạt. Kutools cho Excel cung cấp một giải pháp dễ thương 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!


ad auto mỹ ngày lễ 1

Kutools cho Excel - Supercharge Excel với hơn 300 công cụ thiết yếu. Tận hưởng bản dùng thử MIỄN PHÍ 30 ngày đầy đủ tính năng 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.

doc kiểm tra xem ngày lễ 6

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 cho 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.


quảng cáo sao chép chính xác công thức 3

Kutools cho Excel - Supercharge Excel với hơn 300 công cụ thiết yếu. Tận hưởng bản dùng thử MIỄN PHÍ 30 ngày đầy đủ tính năng 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

🤖 Trợ lý AI của Kutools: Cách mạng hóa việc phân tích dữ liệu dựa trên: Thực thi thông minh   |  Tạo mã  |  Tạo công thức tùy chỉnh  |  Phân tích dữ liệu và tạo biểu đồ  |  Gọi các hàm Kutools...
Các tính năng phổ biến: Tìm, đánh dấu hoặc xác định các bản sao   |  Xóa hàng trống   |  Kết hợp các cột hoặc ô mà không làm mất dữ liệu   |   Vòng không có công thức hữu ích. Cảm ơn !
Siêu tra cứu: Nhiều tiêu chí VLookup    VLookup Nhiều Giá Trị  |   VLookup trên nhiều trang tính   |   Tra cứu mờ ....
Danh sách thả xuống nâng cao: Tạo nhanh danh sách thả xuống   |  Danh sách thả xuống phụ thuộc   |  Danh sách thả xuống nhiều lựa chọn ....
Trình quản lý cột: Thêm một số cột cụ thể  |  Di chuyển cột  |  Chuyển đổi trạng thái hiển thị của các cột ẩn  |  So sánh dãy và cột hữu ích. Cảm ơn !
Các tính năng nổi bật: Tiêu điểm lưới   |  Chế độ xem thiết kế   |   Thanh công thức lớn    Trình quản lý sổ làm việc & trang tính   |  Thư viện tài nguyên (Văn bản tự động)   |  Bảng chọn ngày   |  Kết hợp các bảng tính   |  Mã hóa/Giải mã ô    Gửi email theo danh sách   |  Siêu lọc   |   Bộ lọc đặc biệt (lọc in đậm/nghiêng/gạch ngang...) ...
15 bộ công cụ hàng đầu12 bản văn CÔNG CỤ (thêm văn bản, Xóa ký tự,...)   |   50 + Biểu đồ Các loại (Biểu đồ Gantt,...)   |   40+ Thực tế Công thức (Tính tuổi dựa trên ngày sinh,...)   |   19 chèn CÔNG CỤ (Chèn mã QR, Chèn ảnh từ đường dẫn,...)   |   12 Chuyển đổi CÔNG CỤ (Số thành từ, Chuyển đổi tiền tệ,...)   |   7 Hợp nhất & Tách CÔNG CỤ (Các hàng kết hợp nâng cao, Chia ô,...)   |   ... và nhiều hơn nữa

Nâng cao kỹ năng Excel của bạn với Kutools for Excel và 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...

Mô tả


Tab Office mang lại giao diện Tab cho Office và giúp công việc của bạn trở nê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!
Comments (9)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Like the Observed Holiday, I need to also recognize additional days off outside of the Holiday or observed holidays. Example: If Christmas is on a Friday, I need to calculate the days before and after (until New Years) off.
This comment was minimized by the moderator on the site
How would you calculate 15 calendar days from a given date including weekends but excluding holidays using a list/table of holiday dates?
This comment was minimized by the moderator on the site
How to make it show the actual name of the holiday instead of just "holiday"?
This comment was minimized by the moderator on the site
Hi KC,
You can change the text “holiday” to INDEX($A$1:$A$11,MATCH(A18,$C$1:$C$11,0)) in the formula, and the whole formula will be changed to
=IF(COUNTIF($C$2:$C$11,A18),INDEX($A$1:$A$11,MATCH(A18,$C$1:$C$11,0)),"No")

Please note that the dates you will check should be placed in one column.
This comment was minimized by the moderator on the site
Thanks, I have worked out a system to determine whether a public holiday is a weekday, but this also gives an alternative. The problem is that if one does it per month, then there are gaps between days where public holidays occur during weekdays. An example as below taking part of December 2017. the figures to immediate right of dates (Col B) are the WEEKDAY values. If date falls on a Saturday or Sunday (value 6 or 7) then the C Column reflects a blank cell ("") if a weekday the Cell has a "1", if a Public Holiday during a weekday then a "0" 21/12/2017 4 1 22/12/2017 5 1 23/12/2017 6 24/12/2017 7 25/12/2017 1 0 26/12/2017 2 0 27/12/2017 3 1 28/12/2017 4 1 29/12/2017 5 1 30/12/2017 6 31/12/2017 7 I can then sort manually using the Filter approach to get the 1's in one continuous column of rows without the blanks or 0's. Copy and paste to a worksheet where I can import the data into the temperature charts. I am trying to get the filter section automated either via formula by deleting all the 0's and blank cells with the resultant shifting up of cells containing the 1's, or via VBA. The ultimate prize would be combining the steps in Column A and Column C into one formula. The end game is to populate a temperature chart with the workday name and in the next corresponding row the day of the required month Mon Tue Thu Fri 7 8 10 11 Using August as an example where the 9th is a public holiday that falls during a work day, resulting in the data relating to the Wed being removed and the rest of the column shifting up one (or more) places. Then transposed into the above cells. I hope I am explaining with sufficient clarity :-)
This comment was minimized by the moderator on the site
How could I make this work for Federal Holiday? Meaning if the date of a holiday happens to fall on a weekend then the Federal holiday would either be Friday or Monday.
This comment was minimized by the moderator on the site
I used the formulas above to calculate the actual day of the holiday and made a second column for Observed holiday. I made this formula to accomplish this: =IF((WEEKDAY(B15))=1,B15+1,IF((WEEKDAY(B15))=7,B15-1,B15)). The cell reference B15 is referring to the holiday which is in the actual holiday column, in this case New Years Day. When the actual holiday falls on a Saturday, the Observed holiday will be listed as Friday and for actual holidays falling on Sunday, the observed holiday will be listed as Monday. Hope this helps.
This comment was minimized by the moderator on the site
This is an accurate function which will work for New Years Day that would fall on a weekend (years 2022 and 2023): =WORKDAY(DATE(CalendarYear,1,1),--(WEEKDAY(DATE(CalendarYear,1,1),2)>5))
This comment was minimized by the moderator on the site
trying to make a formula for subtracting CALENDAR DAYS and holidays. I have been able to figure out for WORKDAYS and HOLIDAY, but I cannot figure out how to do CALENDAR days and holidays. here is what I am currently using for WORKDAYS AND HOLIDAYS. Help! So I need this to be CALENDAR days instead of WORKDAYS.] =WORKDAY(B28-5,1,HOLIDAYS)
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations