Bỏ qua nội dung chính

Làm thế nào để tính toán số giờ làm việc thực giữa hai ngày không kể cuối tuần hoặc ngày lễ trong Excel?

Ở nhiều công ty, nhân viên được trả lương theo giờ làm việc. Để tính toán số giờ làm việc thực trong một ngày thì dễ dàng, nhưng cách tính số giờ thực trong một phạm vi ngày thì sao? Vì vậy, bài viết này giới thiệu các công thức tính số giờ làm việc thực giữa hai ngày không bao gồm cuối tuần và ngày lễ trong Excel.

Tính toán ngày làm việc không bao gồm các ngày cuối tuần

Tính giờ làm việc không bao gồm các ngày cuối tuần / ngày lễ

mũi tên màu xanh bong bóng bên phải Tính toán ngày làm việc không bao gồm các ngày cuối tuần

Trong phần này, tôi giới thiệu công thức tính ngày làm việc giữa hai thời điểm ngày không bao gồm ngày cuối tuần.

1. Chọn hai ô mà bạn sẽ nhập thời gian ngày bắt đầu và ngày kết thúc và nhấp chuột phải để chọn Format Cells tạo menu ngữ cảnh. Xem ảnh chụp màn hình:
doc net giờ làm việc 1

2. bên trong Format Cells thoại, bấm Con số Tab, và chọn Tuỳ chỉnh hình thành Phân loại liệt kê và nhập m / d / yyyy h: mm vào Kiểu hộp văn bản trong phần bên phải. Xem ảnh chụp màn hình:
doc net giờ làm việc 2

3. nhấp chuột OK. Và nhập thời gian ngày bắt đầu và ngày kết thúc vào hai ô riêng biệt. Xem ảnh chụp màn hình:
doc net giờ làm việc 3

4. Trong ô bên cạnh hai ô này, ví dụ: C13, hãy nhập công thức này =NETWORKDAYS(A13,B13)-1-MOD(A13,1)+MOD(B13,1), và hãy nhấn đăng ký hạng mục thi và bạn sẽ nhận được kết quả với định dạng tùy chỉnh, hãy chọn ô kết quả và nhấp vào Trang Chủ và chuyển đến Định dạng số danh sách để chọn Chung để định dạng nó thành định dạng chính xác. Xem ảnh chụp màn hình:
doc net giờ làm việc 4

mũi tên màu xanh bong bóng bên phải Tính giờ làm việc không bao gồm các ngày cuối tuần / ngày lễ

Nếu bạn muốn tính số giờ làm việc thực không bao gồm cuối tuần hoặc ngày lễ, bạn có thể làm như sau:

Tính số giờ làm việc thực không bao gồm các ngày cuối tuần

1. Chọn hai ô và định dạng chúng dưới dạng định dạng tùy chỉnh m / d / yyyy h: mm, và nhập ngày bắt đầu và giờ kết thúc. Xem ảnh chụp màn hình:
doc net giờ làm việc 5

doc net giờ làm việc 6

2. Và trong ô bên cạnh, chẳng hạn như C2, hãy nhập công thức này,
=(NETWORKDAYS(A2,B2)-1)*("17:30"-"8:30")+IF(NETWORKDAYS(B2,B2),MEDIAN(MOD(B2,1),"17:30","8:30"),"17:30")-MEDIAN(NETWORKDAYS(A2,A2)*MOD(A2,1),"17:30","8:30"),
nhấn đăng ký hạng mục thi sau đó bạn nhận được một chuỗi số. Xem ảnh chụp màn hình:
doc net giờ làm việc 7

3. Nhấp chuột phải vào chuỗi số và nhấp vào Format Cells từ menu ngữ cảnh và trong Format Cells hộp thoại, chọn Tuỳ chỉnh hình thức Phân loại liệt kê dưới Number tab và nhập vào [h]: mm vào hộp văn bản Loại. Xem ảnh chụp màn hình:
doc net giờ làm việc 8

4. nhấp chuột OK. Giờ đây, giờ làm việc thực giữa hai ngày không bao gồm cuối tuần được tính.
doc net giờ làm việc 9

Mẹo: Trong công thức, A2 là ngày bắt đầu, B2 là ngày kết thúc, 8:30 và 17:30 là thời gian bắt đầu và kết thúc chung trong mỗi ngày, bạn có thể thay đổi chúng khi cần.

Tính số giờ làm việc thực không bao gồm cuối tuần và ngày lễ

1. Tương tự như trên, chọn hai ô và định dạng chúng thành định dạng tùy chỉnh m / d / yyyy h: mm, và nhập thời gian ngày bắt đầu và ngày kết thúc.
doc net giờ làm việc 10

2. Chọn một ô trống và nhập ngày nghỉ vào đó, ở đây tôi có 3 ngày nghỉ và tôi gõ chúng riêng biệt vào H1: H3. Xem ảnh chụp màn hình:
doc net giờ làm việc 11

3. Chọn một ô trống sẽ đặt kết quả đã đếm, ví dụ như C2,
=(NETWORKDAYS.INTL(A2,B2,11,H$1:H$3)-1)*("17:30"-"8:30")+IF(NETWORKDAYS.INTL(B2,B2,11,H$1:H$3),MEDIAN(MOD(B2,1),"8:30","17:30"),"17:30")-MEDIAN(NETWORKDAYS.INTL(A2,A2,11,H$1:H$3)*MOD(A2,1),"8:30","17:30"),
và báo chí đăng ký hạng mục thi chính, bạn sẽ nhận được một chuỗi số và định dạng nó thành định dạng tùy chỉnh [h]: mm. Xem ảnh chụp màn hình:
doc net giờ làm việc 12

Mẹo: Trong công thức, A2 là thời gian ngày bắt đầu, B2 là ngày kết thúc, 8:30 và 17:30 là thời gian bắt đầu và kết thúc chung trong mỗi ngày, H1: H3 là các ô ngày nghỉ, bạn có thể thay đổi chúng như bạn cần.

Dễ dàng thêm ngày / năm / tháng / giờ / phút / giây vào ngày giờ trong Excel
Giả sử bạn có dữ liệu định dạng ngày giờ trong một ô và bây giờ bạn cần thêm một số ngày, năm, tháng, giờ, phút hoặc giây vào ngày này. Thông thường, sử dụng công thức là phương pháp đầu tiên cho tất cả người dùng Excel, nhưng thật khó để nhớ tất cả các công thức. Với Kutools cho Excel'S Trình trợ giúp ngày & giờ tiện ích, bạn có thể dễ dàng thêm ngày, năm, tháng hoặc giờ, phút hoặc giây vào thời gian ngày, hơn nữa, bạn có thể tính toán chênh lệch ngày hoặc tuổi dựa trên sinh nhật nhất định mà không cần nhớ công thức. Nhấp để dùng thử miễn phí đầy đủ tính năng trong 30 ngày!
doc thêm giờ phút giây
 
Kutools cho Excel: với hơn 300 bổ trợ Excel tiện dụng, dùng thử miễn phí không giới hạn trong 30 ngày.

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 đầu:  12 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 (67)
No ratings yet. Be the first to rate!
Hugo Pereira
Hugo Pereira
This comment was minimized by the moderator on the site
This formula is very good. Does anyone know how I can convert this to SQL query?
Reply
0
0
Excel Formula
Excel Formula
This comment was minimized by the moderator on the site
it works
how to add lunch break?
Reply
0
0
Sanjeev
Sanjeev
This comment was minimized by the moderator on the site
This is very good, what if the shift time spans over 2 days (start time 17:00 to 02:00 next day)
Reply
0
0
Sameer
Sameer     Sanjeev
This comment was minimized by the moderator on the site
Hi
Have recieved any update regarding for your questions because I am also finding for same
Reply
0
0
JV
JV
This comment was minimized by the moderator on the site
Can anyone help me how the formula would be if the work hours are from 8:00 pm to 5:00 am (20:00 to 5:00)?
Reply
0
0
JV
JV     JV
This comment was minimized by the moderator on the site
Tried the same formula but it's showing negative values.
Reply
0
0
JV
JV     JV
This comment was minimized by the moderator on the site
Anyone need this formula but for graveyard or night shift schedule?
Reply
0
0
Sunny
Sunny
This comment was minimized by the moderator on the site
Why is the Median function used in this formula? what is calculating
Reply
0
0
Pooja .
Pooja .
This comment was minimized by the moderator on the site
Hey, thanks for this formula.

But can we apply this formula for same dates.

For ex,
if start date and time is
" 15/11/20 11:10AM" and end date and time is "15 /11/20 11:25AM"
Reply
0
0
Sunny
Sunny     Pooja .
This comment was minimized by the moderator on the site
Hi, Pooja, use formula (M1 is the start time,M2 is the end time)
=(NETWORKDAYS(M1,M2)-1)*("17:30"-"8:30")+IF(NETWORKDAYS(M2,M2),MEDIAN(MOD(M2,1),"17:30","8:30"),"17:30")-MEDIAN(NETWORKDAYS(M1,M1)*MOD(M1,1),"17:30","8:30")
and format the result cell as time.
Reply
Report
0
0
Pierre
Pierre
This comment was minimized by the moderator on the site
Hi, Sunny, thank you so much, this works perfectly now.
Reply
0
0
Pierre
Pierre
This comment was minimized by the moderator on the site
Hi the formula below works well with me to calculate the tame a task is taking from start to finish excluding a standard weekends of Saturday and Sunday off.
=(NETWORKDAYS(I7,J7)-1)*("18:00"-"8:30")+IF(NETWORKDAYS(J7,J7),MEDIAN(MOD(J7,1),"18:00","8:30"),"18:00")-MEDIAN(NETWORKDAYS(I7,I7)*MOD(I7,1),"18:00","8:30")

However, on Friday we would like to consider 3 working hours only (9:00-12:00), how can I insert it within the formula please? any idea?
Reply
0
0
Sunny
Sunny     Pierre
This comment was minimized by the moderator on the site
Hi, Pierre, I have modified the formula:
=((NETWORKDAYS(A1,B1)-1)*("18:00"-"8:30")+IF(NETWORKDAYS(B1,B1),MEDIAN(MOD(B1,1),"18:00","8:30"),"18:00")-MEDIAN(NETWORKDAYS(A1,A1)*MOD(A1,1),"18:00","8:30"))-INT((WEEKDAY($A$1- 6)-$A$1+$B1)/7)*(("18:00"-"8:30")-("12:00"-"9:00"))
Reply
Report
0
0
Pierre
Pierre     Sunny
This comment was minimized by the moderator on the site
Thank you Sunny, but honestly the updated formula did not give the results properly, not sure if it requires additional adjustment:
Monday - Thursday 8:30-18:00 (working hours)
Friday 9:00-12:00 (working hours)
Saturday - Sunday Off
thank you
Reply
0
0
Sunny
Sunny     Pierre
This comment was minimized by the moderator on the site
Hi, Pierre, I have tested the formula, it works for me. In the formula:
A1 is the start datetime, B1 is the end datetime, and both of the datetime cells are formated as mm/dd/yy hh:mm, then the result you need to format it as time format: 37:30:55.
https://www.extendoffice.com/images/stories/comments/sun-comment/doc-calculate-specific-work-hour.png
https://www.extendoffice.com/images/stories/comments/sun-comment/doc-calculate-specific-work-hour-2.png
Reply
Report
0
0
Nishanth
Nishanth     Sunny
This comment was minimized by the moderator on the site
hi sunny,

I have used the formulla and it is working well except for friday hald day calculation showing in negative hours. Kindly suggest

=((NETWORKDAYS(P9,R9,1)-1)*("15:00"-"07:00")+IF(NETWORKDAYS(R9,R9),MEDIAN(MOD(R9,1),"15:00","07:00"),"15:00")-MEDIAN(NETWORKDAYS(P9,P9)*MOD(P9,1),"15:00","07:00")-INT((WEEKDAY(P9-6)-P9+R9)/7)*(("15:00"-"7:00")-("11:30"-"7:00")))

Start time: 1/12/2024 11:51:02 AM
End Time: 1/12/2024 11:51:13 AM
Result: -3:30:00

Thanks
Nishanth
Reply
0
0
Veronika
Veronika
This comment was minimized by the moderator on the site
Dear all, I would like to ask you for help, I tried this formula for counting working hours between days (without weekends and holidays), but I receive the #Value! error.I formatted the cells as well.
 Start date in A2:  24.11.2021 11:05  <span style="letter-spacing: 0.2px; color: inherit; font-family: inherit; font-style: inherit; font-variant-ligatures: inherit; font-variant-caps: inherit; font-weight: inherit;">   1.12.2021 11:05</span>Workday start in C2:    6:00Workday end  in D2:  18:00Holidays in E2 till E10:
1/1/21
4/2/21
4/5/21
5/1/21
5/13/21
5/24/21
10/3/21
12/25/21
12/26/21
    
I used the following formula:   <span style="letter-spacing: 0.2px; color: inherit; font-family: inherit; font-style: inherit; font-variant-ligatures: inherit; font-variant-caps: inherit; font-weight: inherit;">#Value! error, can you please advise?</span>
Reply
0
0
sandeep
sandeep
This comment was minimized by the moderator on the site
Hello Sir,
My predecessor has set below formula.I am not getting  what is "Holidays[#All]", not getting where he has set this.
Please let me know.....


=ABS(IF([@Priority]<3,settings!$F$2-K3,(((NETWORKDAYS(K3,settings!$F$2,Holidays[#All]))*((settings!$B$3-settings!$B$2)*24)-IF(AND(NETWORKDAYS(K3,K3,settings!$D$2:settings!$D$33)>0,(K3-INT(K3))*24>(settings!$B$2-INT(settings!$B$2))*24,(K3-INT(K3))*24<(settings!$B$3-INT(settings!$B$3))*24),((K3-INT(K3))*24)-((settings!$B$2-INT(settings!$B$2))*24),0)-IF(AND(NETWORKDAYS(settings!$F$2,settings!$F$2,settings!$D$2:settings!$D$33)>0,(settings!$F$2-INT(settings!$F$2))*24<(settings!$B$3-INT(settings!$B$3))*24,(settings!$F$2-INT(settings!$F$2))*24>(settings!$B$2-INT(settings!$B$2))*24),((settings!$B$3-INT(settings!$B$3))*24)-((settings!$F$2-INT(settings!$F$2))*24),0)-IF(AND(NETWORKDAYS(K3,K3,settings!$D$2:settings!$D$33)>0,(K3-INT(K3))*24>(settings!$B$3-INT(settings!$B$3))*24),(settings!$B$3-settings!$B$2)*24,0)-IF(AND(NETWORKDAYS(settings!$F$2,settings!$F$2,settings!$D$2:settings!$D$33)>0,(settings!$F$2-INT(settings!$F$2))*24<(settings!$B$2-INT(settings!$B$2))*24),(settings!$B$3-settings!$B$2)*24,0))/24)))
Reply
Report
0
0
There are no comments posted here yet
Load More
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations