Tìm ngày giờ kết thúc dự kiến
- SprinGO.vn
- Excel Ứng Dụng
Điều kiện:
– Ngày làm việc: từ thứ 2 đến thứ 7; Chủ nhật và ngày lễ nghỉ việc
– Giờ làm việc 8 tiếng từ: 8h đến 17h (nghỉ giữa buổi từ 12h-13h không tính vào giờ làm)
Yêu cầu:
- Ngày giờ bắt đầu: Cần điều chỉnh lại Ngày/Giờ (cột B file kèm), trả kết quả vào cột D "Ngày giờ bắt đầu (điều chỉnh)"
- Nếu sớm hơn 8h00: chỉnh thành 8h00
- Nếu rơi vào khung 12h -13h: chỉnh thành 13h
- Nếu bằng hay quá 17h00: chỉnh thành 8h00 của ngày kế tiếp (không rơi vào ngày Chủ nhật và ngày lễ)
[*]Ngày giờ kết thúc dự kiến: Dựa vào "Thời gian thực hiện (tiếng)" và "Ngày giờ bắt đầu (điều chỉnh)", tính toán và trả kết quả vào cột E "Ngày giờ kết thúc dự kiến "
- Theo điều kiện như trên đã nêu
[*]Chỉ dùng công thức, tùy ý dùng bất kỳ từ phiên bản 2010 trở đi; Không dùng cột phụ hay Names.
[*]Kết quả mẫu tính tay như cột G và HBảng dữ liệu như file đính kèm.
Các bạn cũng sẽ như tôi lúng túng trong việc làm sao xác định được số ngày cộng thêm mà không bị rơi vào ngày CN và lễ, thấy rắc rối nhưng không phải vậy, và giống như tôi gợi ý với bạn @vanthinh3101 ở bài #12
Vd: Ngày bắt đầu còn 1.5 tiếng, thời gian thực hiện cần 20 tiếng, vậy thời gian còn lại là = 20 – 1.5 = 18.5 tiếng. Vì 1 ngày làm việc cố định 8 tiếng, vậy cần 3 ngày cộng thêm (8*3=24 tiếng -> bội số của 8) để thực hiện kết thúc, trong đó 2 ngày đầu mất 16 tiếng, vậy giờ kết thúc là 2.5 tiếng. Ngày kết thúc bắt đầu từ lúc 8h00 + 2.5 tiếng = 10h30 là mốc giờ kết thúc.
Muốn tìm bội số của 8 thì tôi dùng hàm Ceiling( 'Thời gian còn lại' /8 ,1). Vậy, đặt bội số này vào hàm:
=WORKDAY.INTL( 'Ngày bắt đầu', 'bội số của 8', 11, 'Ngày CN/ lễ' ) thì tìm được ngày cần thêm để kết thúc công việc mà không rơi vào CN và ngày lễ.
Tôi thực hiện cả hai phiên bản trước và từ 365 để các bạn trước 365 có thể tham khảo:
1/ Công thức trước 365:
a. Ngày giờ điều chỉnh:
=WORKDAY.INTL(B6,N(MOD(B6,1)*24>=17),11,$J$6:$J$12)+IF(MOD(B6,1)*24<17,TEXT(MAX(MOD(B6,1)*24,8),"[<12];[>13];13"),8)/24b.Ngày giờ kết thúc:
=WORKDAY.INTL(D6,CEILING((C6-(17-ROUND(MOD(D6,1)*24,6)-(ROUND(MOD(D6,1)*24,6)<13)))/8,1),11,$J$6:$J$12)+IF(MOD(C6-(17-ROUND(MOD(D6,1)*24,6)-(ROUND(MOD(D6,1)*24,6)<13)),8),8+MOD(C6-(17-ROUND(MOD(D6,1)*24,6)-(ROUND(MOD(D6,1)*24,6)<13)),8)+(MOD(C6-(17-ROUND(MOD(D6,1)*24,6)-(ROUND(MOD(D6,1)*24,6)<13)),8)>4),17)/242/ Công thức 365:
a. Ngày giờ điều chỉnh:
=LET(gio,MOD(B6,1)*24,WORKDAY.INTL(B6,N(gio>=17),11,$J$6:$J$12)+IF(gio<17,TEXT(MAX(gio,8),"[<12];[>13];13"),8)/24)b.Ngày giờ kết thúc:
=LET(Gcl,LET(gio,ROUND(MOD(D6,1)*24,6),C6-16+gio-(gio>12)),Glt,CEILING(Gcl/8,1),Gdc,Gcl-Glt*8,WORKDAY.INTL(D6,Glt,11,$J$6:$J$12)+(16+Gdc+(8+Gdc>4))/24)18402
Các bạn xem đồ biểu, thuật toán tôi có nêu trong file kèm
Xây dựng Lương 3P, KPI cho Doanh nghiệp
Làm thế nào để trả lương cho nhân viên chính xác nhất? Đây là một trong những câu hỏi khó trong quản trị nhân...
Xem khóa học