Bạn có biết trong quý 1 của năm 2019 có bao nhiêu ngày thứ bảy, bao nhiêu ngày chủ nhật không? Để trả lời câu hỏi này có thể bạn sẽ phải mở lịch ra để đếm. Tại sao không làm việc này một cách tự động trong Excel nhỉ? Hãy cùng Học Excel online tìm hiểu công thức đếm số ngày thứ bảy, chủ nhật trong một khoảng thời gian nhé.
Ví dụ để xét khoảng thời gian từ ngày 01/01/2019 đến ngày 12/01/2019, chúng ta có thể đặt 2 giá trị ngày vào các ô B1 và B2 như sau:
Dùng hàm Netwworkdays.INTL để đếm số ngày có trong khoảng thời gian bất kỳ
Hàm NETWWORKDAYS.INTL là hàm dùng để tính số ngày làm việc trong tuần, trong đó loại trừ những ngày nghỉ. Cấu trúc của hàm này như sau:
=NETWORKDAYS.INTL[start_date, end_date, [weekend], [holidays]]
Trong đó:
- Start_date là ngày bắt đầu [hay Từ ngày]
- End_date là ngày kết thúc [hay Đến ngày]
- Weekend: ngày nào là ngày nghỉ, ngày nào là ngày làm việc.
- Holidays: ngày nghỉ lễ [nếu có] xen giữa trong khoảng thời gian Từ ngày… Đến ngày…
Đối tượng Weekend được biểu diễn bằng chuỗi 7 con số, trong đó:
- Mỗi 1 con số được biểu diễn cho 1 ngày trong tuần, tính từ thứ 2
- Tại mỗi vị trí ngày tương ứng, nếu chọn số 1 thì là Ngày nghỉ tuần, chọn số 0 là Ngày làm việc
Để đếm số ngày thứ 7 trong khoảng thời gian trên, ta có thể coi trong tuần chỉ làm việc vào thứ 7, còn các ngày khác đều là ngày nghỉ. Do đó ta có chuỗi số như sau:
Công thức tại ô B4=NETWORKDAYS.INTL[$B$1,$B$2,”1111101″]
Kết quả thu được là 2 ngày
Với ngày chủ nhật ta có:
Công thức tại ô B5=NETWORKDAYS.INTL[$B$1,$B$2,”1111110″]
Kết quả thu được là 1 ngày
Như vậy chỉ cần đổi khoảng thời gian bất kỳ trong ô B1 và B2 là chúng ta đã có được kết quả như ý muốn rồi.
Nếu bạn muốn tính cho ngày khác trong tuần, chỉ cần đổi vị trí số 0 tương ứng cho ngày đó.
Ví dụ đếm số ngày thứ 7 và số ngày chủ nhật trong nửa đầu năm 2019
Để tìm hiểu thêm về các hàm xử lý thời gian trong Excel, mời các bạn tìm hiểu các bài viết:
Cách tính thời gian trong Excel – chênh lệch thời gian, cộng/trừ thời gian
Hàm thời gian thông dụng trong Excel
Định dạng thời gian trong Excel & cách sử dụng hàm NOW và TIME để chèn thời gian
Hàm DATEDIF, tính khoảng thời gian giữa 2 thời điểm trong Excel
Nếu bạn cần phải xếp lịch làm một việc vào tất cả các ngày chủ nhật trong tháng thì bạn sẽ làm thế nào? Chắc là phải mở lịch lên và xem tháng đó ngày nào là chủ nhật đúng không? Vậy trong trường hợp chúng ta không có lịch thì sao? Tin vui là bạn hoàn toàn có thể sử dụng excel để làm việc này.
Cách xác định những ngày chủ nhật trong khoảng thời gian bất kỳ
Ví dụ chúng ta có khoảng thời gian bất kỳ là từ ngày đầu tháng đến ngày cuối tháng 8 năm 2022:
câu hỏi đặt ra là:
1. Có bao nhiêu ngày chủ nhật trong tháng?
2. Cụ thể ngày chủ nhật trong tháng là ngày nào?
Sau đây chúng ta lần lượt trả lời từng câu hỏi như sau:
Có bao nhiêu ngày chủ nhật trong tháng?
Trong bài viết Hướng dẫn cách đếm số ngày thứ bảy, chủ nhật trong một khoảng thời gian chúng ta thấy có 1 cách rất hay để đếm số ngày chủ nhật trong 1 khoảng thời gian, đó là dùng hàm Networkdays.INTL
Trong ví dụ trên, để xác định số ngày chủ nhật trong khoảng giữa hai ngày tại ô B3 và B4 ta có thể viết như sau:
=NETWORKDAYS.INTL[B3,B4,”1111110″]
Kết quả thu được là:
Cụ thể ngày chủ nhật trong tháng là ngày nào?
Để xác định các ngày chủ nhật cụ thể trong tháng, chúng ta cần sử dụng các hàm xét thứ trong tuần. Trong đó quan trọng nhất là tìm ra ngày chủ nhật đầu tiên trong tháng là ngày nào.
Logic để xác định ngày chủ nhật đầu tiên trong tháng như sau:
- Kiểm tra xem ngày đầu tháng [tại ô B3] có phải ngày chủ nhật không. Nếu đúng thì lấy luôn ngày này.
- Nếu không phải, xét xem ngày đó là thứ mấy.
- Dựa vào thứ, chúng ta biết được cần thêm bao nhiêu ngày nữa là tới chủ nhật => Xác định khoảng cách này
- Cộng thêm khoảng cách đó vào ngày đầu tháng => Thu được ngày chủ nhật đầu tiên.
Công thức có thể xây dựng như sau:
=B3+IF[WEEKDAY[B3,2]=7, 0, 7-WEEKDAY[B3,2]]
Ta có:
WEEKDAY[B3,2] là xét thứ của ngày tại ô B3 bằng hàm Weekday. Tham số 2 để thể hiện giá trị ngày từ 1 đến 7 tương ứng từ Thứ hai tới Chủ nhật [Thứ hai = 1, thứ 3 = 2… chủ nhật = 7]
IF[WEEKDAY[B3,2]=7, 0 là nếu ngày tại ô B3 là chủ nhật, sẽ coi giá trị số ngày cần cộng thêm là 0
7-WEEKDAY[B3,2] trường hợp ngày tại ô B3 không phải chủ nhật, sẽ lấy 7 [ứng với chủ nhật] trừ đi giá trị thứ của ô B2 để tính khoảng cách số ngày cần cộng thêm.
Kết quả thu được là:
Những ngày chủ nhật tiếp theo, chúng ta chỉ cần lấy Ngày chủ nhật đầu tiên + 7.
Lưu ý: Xét điều kiện nếu Ngày chủ nhật trước đó + 7 vượt quá ngày cuối tháng thì không cộng nữa.
Chúc các bạn áp dụng tốt kiến thức này vào công việc!
Ngoài ra các bạn có thể tham khảo thêm một số bài viết thú vị khác về Ngày tháng trong Excel:
Định dạng Ngày Tháng Năm trong Excel đúng cách
Hàm DATEDIF, tính khoảng thời gian giữa 2 thời điểm trong Excel