Note: The other languages of the website are Google-translated. Back to English

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 - Bao gồm hơn 300 công cụ tiện dụng cho Excel. Bản dùng thử miễn phí đầy đủ tính năng 30-ngày, 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 - Bao gồm hơn 300 công cụ tiện dụng cho Excel. Bản dùng thử miễn phí đầy đủ tính năng 30-ngày, không cần thẻ tín dụng! Get It Now


Các công cụ năng suất văn phòng tốt nhất

Kutools cho Excel giải quyết hầu hết các vấn đề của bạn và tăng 80% năng suất của bạn

  • Tái sử dụng: Chèn nhanh công thức phức tạp, biểu đồ và bất cứ thứ gì bạn đã sử dụng trước đây; Mã hóa ô với mật khẩu; Tạo danh sách gửi thư và gửi email ...
  • Thanh siêu công thức (dễ dàng chỉnh sửa nhiều dòng văn bản và công thức); Bố cục đọc (dễ dàng đọc và chỉnh sửa số lượng ô lớn); Dán vào Dải ô đã Lọchữu ích. Cảm ơn !
  • Hợp nhất các ô / hàng / cột mà không làm mất dữ liệu; Nội dung phân chia ô; Kết hợp các hàng / cột trùng lặp... Ngăn chặn các ô trùng lặp; So sánh các dãyhữu ích. Cảm ơn !
  • Chọn trùng lặp hoặc duy nhất Hàng; Chọn hàng trống (tất cả các ô đều trống); Tìm siêu và Tìm mờ trong Nhiều Sổ làm việc; Chọn ngẫu nhiên ...
  • Bản sao chính xác Nhiều ô mà không thay đổi tham chiếu công thức; Tự động tạo tài liệu tham khảo sang Nhiều Trang tính; Chèn Bullets, Hộp kiểm và hơn thế nữa ...
  • Trích xuất văn bản, Thêm Văn bản, Xóa theo Vị trí, Xóa không gian; Tạo và In Tổng số phân trang; Chuyển đổi giữa nội dung ô và nhận xéthữu ích. Cảm ơn !
  • Siêu lọc (lưu và áp dụng các lược đồ lọc cho các trang tính khác); Sắp xếp nâng cao theo tháng / tuần / ngày, tần suất và hơn thế nữa; Bộ lọc đặc biệt bằng cách in đậm, in nghiêng ...
  • Kết hợp Workbook và WorkSheets; Hợp nhất các bảng dựa trên các cột chính; Chia dữ liệu thành nhiều trang tính; Chuyển đổi hàng loạt xls, xlsx và PDFhữu ích. Cảm ơn !
  • Hơn 300 tính năng mạnh mẽ. Hỗ trợ Office / Excel 2007-2021 và 365. Hỗ trợ tất cả các ngôn ngữ. Dễ dàng triển khai trong doanh nghiệp hoặc tổ chức của bạn. Đầy đủ tính năng Dùng thử miễn phí 30 ngày. Bảo đảm hoàn lại tiền trong 60 ngày.
tab kte 201905

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!
officetab dưới cùng
Nhận xét (8)
Chưa có xếp hạng. Hãy là người đầu tiên xếp hạng!
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
đang cố gắng lập công thức trừ NGÀY LỊCH và ngày lễ. Tôi đã có thể tìm ra các NGÀY LỄ và LỄ, nhưng tôi không thể tìm ra cách thực hiện các ngày LỊCH và các ngày lễ. đây là những gì tôi hiện đang sử dụng cho NGÀY LỄ VÀ NGÀY NGHỈ. Giúp đỡ! Vì vậy, tôi cần đây là ngày LỊCH thay vì WORKDAYS.] = WORKDAY (B28-5,1, HOLIDAYS)
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Làm thế nào tôi có thể làm cho điều này hoạt động cho Ngày lễ Liên bang? Có nghĩa là nếu ngày của một kỳ nghỉ rơi vào một ngày cuối tuần thì ngày lễ của Liên bang sẽ là Thứ Sáu hoặc Thứ Hai.
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Tôi đã sử dụng các công thức ở trên để tính ngày thực tế của kỳ nghỉ và tạo cột thứ hai cho Ngày nghỉ được quan sát. Tôi đã thực hiện công thức này để thực hiện điều này: = IF ((WEEKDAY (B15)) = 1, B15 + 1, IF ((WEEKDAY (B15)) = 7, B15-1, B15)). Tham chiếu ô B15 đề cập đến ngày lễ nằm trong cột ngày lễ thực tế, trong trường hợp này là Ngày đầu năm mới. Khi ngày lễ thực tế rơi vào Thứ Bảy, thì ngày lễ Quan sát sẽ được liệt kê là Thứ Sáu và đối với những ngày lễ thực tế rơi vào Chủ Nhật, ngày lễ được quan sát sẽ được liệt kê là Thứ Hai. Hi vọng điêu nay co ich.
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Đây là một hàm chính xác sẽ hoạt động cho Ngày Năm Mới rơi vào cuối tuần (năm 2022 và 2023): = WORKDAY (DATE (CalendarYear, 1,1), - (WEEKDAY (DATE (CalendarYear, 1,1)) , 2)> 5))
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Cảm ơn, tôi đã tìm ra một hệ thống để xác định xem ngày nghỉ lễ có phải là một ngày trong tuần hay không, nhưng điều này cũng đưa ra một giải pháp thay thế. Vấn đề là nếu một người làm điều đó mỗi tháng, thì sẽ có khoảng cách giữa các ngày mà các ngày nghỉ lễ diễn ra trong các ngày trong tuần. Ví dụ như bên dưới kể từ tháng 2017 năm 6. các số liệu ở ngay bên phải ngày tháng (Col B) là các giá trị WEEKDAY. Nếu ngày rơi vào Thứ Bảy hoặc Chủ Nhật (giá trị 7 hoặc 1) thì Cột C phản ánh một ô trống ("") nếu một ngày trong tuần Ô có "0", nếu Ngày Lễ trong một ngày trong tuần thì "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 1 Sau đó, tôi có thể sắp xếp theo cách thủ công bằng cách sử dụng phương pháp Bộ lọc để lấy số 0 trong một cột liên tục của các hàng mà không có ô trống hoặc số 0. Sao chép và dán vào trang tính nơi tôi có thể nhập dữ liệu vào biểu đồ nhiệt độ. Tôi đang cố gắng để phần bộ lọc được tự động hóa thông qua công thức bằng cách xóa tất cả các ô 1 và ô trống với kết quả là chuyển lên các ô có chứa số 7 hoặc qua VBA. Giải thưởng cuối cùng sẽ là kết hợp các bước trong Cột A và Cột C thành một công thức. Trò chơi kết thúc là điền vào biểu đồ nhiệt độ với tên ngày làm việc và trong hàng tương ứng tiếp theo là ngày của tháng bắt buộc Thứ 8 Thứ 10 Thứ 11, 9 XNUMX XNUMX XNUMX Sử dụng tháng XNUMX làm ví dụ trong đó ngày XNUMX là ngày nghỉ lễ rơi vào thời gian làm việc ngày, dẫn đến dữ liệu liên quan đến Thứ tư bị xóa và phần còn lại của cột dịch chuyển lên một (hoặc nhiều) vị trí. Sau đó chuyển vị vào các ô trên. Tôi hy vọng tôi đang giải thích đầy đủ rõ ràng :-)
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Làm thế nào để làm cho nó hiển thị tên thực tế của kỳ nghỉ thay vì chỉ "kỳ nghỉ"?
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Xin chào KC,
Bạn có thể thay đổi văn bản “kỳ nghỉ” thành INDEX ($ A $ 1: $ A $ 11, MATCH (A18, $ C $ 1: $ C $ 11,0)) trong công thức và toàn bộ công thức sẽ được thay đổi thành
=IF(COUNTIF($C$2:$C$11,A18),INDEX($A$1:$A$11,MATCH(A18,$C$1:$C$11,0)),"No")

Xin lưu ý rằng ngày bạn sẽ kiểm tra nên được đặt trong một cột.
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Làm thế nào bạn sẽ tính toán 15 ngày theo lịch từ một ngày nhất định bao gồm cả cuối tuần nhưng không bao gồm ngày lễ bằng cách sử dụng danh sách / bảng ngày lễ?
Không có bình luận nào được đăng ở đây
Để lại ý kiến ​​của bạn
Đăng với tư cách khách
×
Đánh giá bài viết này:
0   Nhân vật
Các vị trí được đề xuất

Kết nối với chúng tôi

Bản quyền © 2009 - www.extendoffice.com. | Đã đăng ký Bản quyền. cung cấp bởi ExtendOffice. | BẢN ĐỒ CHI NHÁNH
Microsoft và logo Office là các nhãn hiệu hoặc nhãn hiệu đã đăng ký của Microsoft Corporation tại Hoa Kỳ và / hoặc các quốc gia khác.
Được bảo vệ bởi Sectigo SSL