Giúp em tính số tháng làm việc theo từng mốc thời gian (2 người xem)

[whykiss]

Chào các anh chị. Em suy nghĩ nát óc mấy ngày nay vẫn chưa ra cách giải quyết, mong anh chị gợi ý hướng dẫn dùm em với.

YÊU CẦU: đếm số tháng làm việc của nhân viên theo từng mốc thời gian (mốc A, mốc B, mốc C, ...). Ví dụ: nhân viên AA đã làm việc từ 9/2015 đến 03/2019 thì anh ta làm được 8 tháng theo mốc A, 14 tháng theo mốc B, 12 tháng theo mốc C, 8 tháng theo mốc D. Sở dĩ em cần tính như vậy là vì mỗi mốc A, B, C... có một bậc lương khác nhau nên em phải tính số tháng làmviệc theo từng mốc cụ thể. Em đã làm mẫu bằng thủ công cho nhân viên AA và BB rồi, mong anh chị giúp đỡ ạ. Cảm ơn anh chị đã xem bài!

 

[Phan Thế Hiệp]

Chào các anh chị. Em suy nghĩ nát óc mấy ngày nay vẫn chưa ra cách giải quyết, mong anh chị gợi ý hướng dẫn dùm em với.

YÊU CẦU: đếm số tháng làm việc của nhân viên theo từng mốc thời gian (mốc A, mốc B, mốc C, ...). Ví dụ: nhân viên AA đã làm việc từ 9/2015 đến 03/2019 thì anh ta làm được 8 tháng theo mốc A, 14 tháng theo mốc B, 12 tháng theo mốc C, 8 tháng theo mốc D. Sở dĩ em cần tính như vậy là vì mỗi mốc A, B, C... có một bậc lương khác nhau nên em phải tính số tháng làmviệc theo từng mốc cụ thể. Em đã làm mẫu bằng thủ công cho nhân viên AA và BB rồi, mong anh chị giúp đỡ ạ. Cảm ơn anh chị đã xem bài!


View attachment 222868

Tạo lại các mốc tháng (Xem H15: L15 file kèm)
Thử:

H5=SUMPRODUCT(N(MATCH(EDATE($C5,ROW(INDIRECT("1:"&$E5))-1),$G$15:$L$15)=COLUMNS($A:B)))

Enter, fill qua phải và xuống dưới.

Thân

 

[whykiss]

Chân thành cảm ơn anh @Phan Thế Hiệp đã giúp đỡ. Em đã giải được bài toán rồi ạ.

 

[luongkiengxuong]

Em chào các anh chị trên diễn đàn Em muốn nhờ các anh chị trên diễn đàn giúp em công thức tính số giờ nghỉ theo móc thời gian < ngày giờ bắt đầu và ngày giờ kết thúc> ạ . Quy định giờ làm việc: Từ thứ 2 đến thư 5 : Mỗi ngày làm 08 tiếng ( sáng từ 8:00 đến 12:00, chiều từ 1:00 đến 5:00) Thứ 6 : làm 7,5 tiếng ( sáng 8:00 đến 12:00, chiều 1:00 đến 4:30) Thứ 7 : làm nửa buổi 04 tiếng ( sáng 8:00 đến 12:00 ,chiều nghỉ) Em nhờ các anh chị trên diễn đàn giúp em công thức tính số giờ nghỉ theo móc thời gian < ngày giờ bắt đầu và ngày giờ kết thúc> nếu trong khoảng thời gian này có rơi vào ngày lễ trong năm ( ngày lễ , chủ nhật ) thì trừ ra ko tính vào giờ nghỉ cũng như trường hợp rơi vào ngày thứ 6 và 7 thì chỉ tính 7,5 /4 tiếng. Em cảm ơn các anh chị ạ

 

[Phan Thế Hiệp]

Em chào các anh chị trên diễn đàn
Em muốn nhờ các anh chị trên diễn đàn giúp em công thức tính số giờ nghỉ theo móc thời gian < ngày giờ bắt đầu và ngày giờ kết thúc> ạ .
Quy định giờ làm việc:
Từ thứ 2 đến thư 5 : Mỗi ngày làm 08 tiếng ( sáng từ 8:00 đến 12:00, chiều từ 1:00 đến 5:00)
Thứ 6 : làm 7,5 tiếng ( sáng 8:00 đến 12:00, chiều 1:00 đến 4:30)
Thứ 7 : làm nửa buổi 04 tiếng ( sáng 8:00 đến 12:00 ,chiều nghỉ)
Em nhờ các anh chị trên diễn đàn giúp em công thức tính số giờ nghỉ theo móc thời gian < ngày giờ bắt đầu và ngày giờ kết thúc> nếu trong khoảng thời gian này có rơi vào ngày lễ trong năm ( ngày lễ , chủ nhật ) thì trừ ra ko tính vào giờ nghỉ cũng như trường hợp rơi vào ngày thứ 6 và 7 thì chỉ tính 7,5 /4 tiếng.
Em cảm ơn các anh chị ạ

Kể cũng lạ là công ty bạn tính ngày nghỉ phép năm tính bằng giờ!?

Thử:

G5=NETWORKDAYS.INTL(C5,E5,"0000111",NgayLe!$A$2:$A$20)*8-((MEDIAN(IF(D5*24<=12,{8,12},{13,17}),D5*24)-IF(D5*24<=12,8,9))*(WEEKDAY(C5,2)<5)+(IF(F5*24<=12,16,17)-MEDIAN(IF(F5*24<=12,{8,12},{13,17}),F5*24))*(WEEKDAY(E5,2)<5))+NETWORKDAYS.INTL(C5,E5,"1111011",NgayLe!$A$2:$A$20)*7.5-((MEDIAN(IF(D5*24<=12,{8,12},{13,16.5}),D5*24)-IF(D5*24<=12,8,9))*(WEEKDAY(C5)=6)+(IF(F5*24<=12,15.5,16.5)-MEDIAN(IF(F5*24<=12,{8,12},{13,16.5}),F5*24))*(WEEKDAY(E5)=6))+NETWORKDAYS.INTL(C5,E5,"1111101",NgayLe!$A$2:$A$20)*4-((MEDIAN(8,12,D5*24)-8)*(WEEKDAY(C5)=7)+(12-MEDIAN(8,12,F5*24))*(WEEKDAY(E5)=7))

Enter, fill xuống.

Xem thêm giải thuật tính thời gian giữa 2 mốc:

Tính thời gian xử lý

Nhờ các anh chị giúp đỡ em viết công thức tự động tính thời gian xử lý với ạ. Ví dụ: Thời gian bắt đầu là 05/09/2018 08:35:00, Thời gian kết thúc là 07/09/2018 14:45:20 Tính thời gian xử lý, biết rằng thời gian làm việc của 1 ngày là từ 8h đến 17h30, nghỉ giữa giờ là từ 12:00 đến 13:30 (khoảng...

www.giaiphapexcel.com

Thân

 

[luongkiengxuong]

Kể cũng lạ là công ty bạn tính ngày nghỉ phép năm tính bằng giờ!?

Thử:

G5=NETWORKDAYS.INTL(C5,E5,"0000111",NgayLe!$A$2:$A$20)*8-((MEDIAN(IF(D5*24<=12,{8,12},{13,17}),D5*24)-IF(D5*24<=12,8,9))*(WEEKDAY(C5,2)<5)+(IF(F5*24<=12,16,17)-MEDIAN(IF(F5*24<=12,{8,12},{13,17}),F5*24))*(WEEKDAY(E5,2)<5))+NETWORKDAYS.INTL(C5,E5,"1111011",NgayLe!$A$2:$A$20)*7.5-((MEDIAN(IF(D5*24<=12,{8,12},{13,16.5}),D5*24)-IF(D5*24<=12,8,9))*(WEEKDAY(C5)=6)+(IF(F5*24<=12,15.5,16.5)-MEDIAN(IF(F5*24<=12,{8,12},{13,16.5}),F5*24))*(WEEKDAY(E5)=6))+NETWORKDAYS.INTL(C5,E5,"1111101",NgayLe!$A$2:$A$20)*4-((MEDIAN(8,12,D5*24)-8)*(WEEKDAY(C5)=7)+(12-MEDIAN(8,12,F5*24))*(WEEKDAY(E5)=7))

Enter, fill xuống.

Xem thêm giải thuật tính thời gian giữa 2 mốc:

Tính thời gian xử lý

Nhờ các anh chị giúp đỡ em viết công thức tự động tính thời gian xử lý với ạ. Ví dụ: Thời gian bắt đầu là 05/09/2018 08:35:00, Thời gian kết thúc là 07/09/2018 14:45:20 Tính thời gian xử lý, biết rằng thời gian làm việc của 1 ngày là từ 8h đến 17h30, nghỉ giữa giờ là từ 12:00 đến 13:30 (khoảng...

www.giaiphapexcel.com

Thân

Cảm ơn sự giúp đỡ nhiệt tình của anh, do kỹ năng excel em còn kém nên chưa hiểu về công thức, mình sẽ cố gắng ngâm cứu file và tài liệu tham khảo của anh. Một lần nữa cảm ơn sự giúp đỡ rất nhiệt tình của anh

 

[luongkiengxuong]

Chào anh Phan Thế Hiệp, cho em gởi lời cảm ơn anh đã giúp đỡ em rất nhiệt tình, công thức anh làm trong file đính kèm đã giúp em giải quyết được bài toán, em cũng có đọc tài liệu anh gởi kèm và giải thuật. Do kinh nghiệm và kiến thức về excel của em còn non quá nên có phần tô màu đỏ trong công thức em ko hiểu nếu anh có thời gian thì giúp em hiểu rõ hơn ạ .
=NETWORKDAYS.INTL(C5,E5,"0000111",NgayLe!$A$2:$A$20)*8-((MEDIAN(IF(D5*24<=12,{8,12},{13,17}),D5*24)-IF(D5*24<=12,8,9))*(WEEKDAY(C5,2)<5)+(IF(F5*24<=12,16,17)-MEDIAN(IF(F5*24<=12,{8,12},{13,17}),F5*24))*(WEEKDAY(E5,2)<5))
Em cảm ơn anh nhiều.

 

[Phan Thế Hiệp]

Chào anh Phan Thế Hiệp, cho em gởi lời cảm ơn anh đã giúp đỡ em rất nhiệt tình, công thức anh làm trong file đính kèm đã giúp em giải quyết được bài toán, em cũng có đọc tài liệu anh gởi kèm và giải thuật. Do kinh nghiệm và kiến thức về excel của em còn non quá nên có phần tô màu đỏ trong công thức em ko hiểu nếu anh có thời gian thì giúp em hiểu rõ hơn ạ .
=NETWORKDAYS.INTL(C5,E5,"0000111",NgayLe!$A$2:$A$20)*8-((MEDIAN(IF(D5*24<=12,{8,12},{13,17}),D5*24)-IF(D5*24<=12,8,9))*(WEEKDAY(C5,2)<5)+(IF(F5*24<=12,16,17)-MEDIAN(IF(F5*24<=12,{8,12},{13,17}),F5*24))*(WEEKDAY(E5,2)<5))
Em cảm ơn anh nhiều.

Giống cách giải thích bên bài có giải thuật tính giờ công làm việc bằng công thức:

1. Số ngày (từ Thứ 2 đến Thứ 5) quy ra 8h/ngày, dùng công thức: =NETWORKDAYS.INTL(C5,E5,"0000111",NgayLe!$A$2:$A$20)*8
   • "0000111": là ký tự quy định loại bớt (không tính)ngày Thứ 6, 7 và CN. Với quy ước:
     • Từng ký tự: 0/1, bắt đầu từ Thứ 2, đến CN có 7 ký tự.
     • Ký tự "0": không bị loại ra
     • Ký tự "1": bị loại ra
   • Xem thêm hàm NETWORKDAYS.INTL() tại:
     • https://support.office.com/vi-vn/article/hàm-networkdays-intl-a9b26239-4f20-46a1-9ab8-4e925bfd5e28
2. Tính Giờ Vào phải trừ bớt, dùng công thức: =(MEDIAN(IF(D5*24<=12,{8,12},{13,17}),D5*24)-IF(D5*24<=12,8,9))*(WEEKDAY(C5,2)<5)
   • {8,12},{13,17} : là mốc giờ theo quy định của công ty bạn
   • WEEKDAY(C5,2)<5: Chỉ tính khi C5 thuộc ngày của Thứ 2 đến Thứ 5.
3. Tính Giờ Ra phải trừ bớt, dùng công thức: =(IF(F5*24<=12,16,17)-MEDIAN(IF(F5*24<=12,{8,12},{13,17}),F5*24))*(WEEKDAY(E5,2)<5)
   • IF(F5*24<=12,16,17): Xem lại giải thích bài:
   • https://www.giaiphapexcel.com/diendan/threads/tính-thời-gian-xử-lý.137565/#post-879939

Thân

 

[luongkiengxuong]

Giống cách giải thích bên bài có giải thuật tính giờ công làm việc bằng công thức:

Cảm ơn anh Phan Thế Hiệp rất nhiều , anh rất nhiệt tình, nhờ anh và các anh chị thành viên đầy nhiệt huyết đã giúp những người mới làm quen excel như em học tập được rất nhiều kiến thức. Chúc anh Hiệp nhiều sức khoẻ và diễn đàn ngày càng phát triển ạ
Thật xin lỗi anh Hiệp ạ.
Vừa áp dụng xong công thức tính giờ nghỉ của anh, sếp em lại yêu cầu tính số phép còn lại của NV theo thời điểm nghỉ phép, nhờ anh giúp em với ạ. cảm ơn anh nhiều nhiều

Yêu cầu bài toán:
Số ngày phép dư của năm trước chỉ được phép sử dụng đến ngày 30/4/2020, nếu NV có những lần nghỉ trước 30/4 thì ưu tiên trừ vào phép dư trước , nếu những lần nghỉ sau 30/4 thì trừ phép trong năm(dù phép dư vẫn còn)

 

[Phan Thế Hiệp]

Cảm ơn anh Phan Thế Hiệp rất nhiều , anh rất nhiệt tình, nhờ anh và các anh chị thành viên đầy nhiệt huyết đã giúp những người mới làm quen excel như em học tập được rất nhiều kiến thức. Chúc anh Hiệp nhiều sức khoẻ và diễn đàn ngày càng phát triển ạ
Thật xin lỗi anh Hiệp ạ.
Vừa áp dụng xong công thức tính giờ nghỉ của anh, sếp em lại yêu cầu tính số phép còn lại của NV theo thời điểm nghỉ phép, nhờ anh giúp em với ạ. cảm ơn anh nhiều nhiều

Yêu cầu bài toán:
Số ngày phép dư của năm trước chỉ được phép sử dụng đến ngày 30/4/2020, nếu NV có những lần nghỉ trước 30/4 thì ưu tiên trừ vào phép dư trước , nếu những lần nghỉ sau 30/4 thì trừ phép trong năm(dù phép dư vẫn còn)

Điều chỉnh 1 số cấu trúc dữ liệu như file kèm.
Thử:

F3=MAX(VLOOKUP(A3,TonPhep!$A$2:$C$20,3,)-SUMIF($A$2:A2,A3,$D$2),)
G3=E3+F3+MIN(SUMIFS($D$3:D3,$A$3:A3,A3,$C$3:C3,"<="&$F$1),VLOOKUP(A3,TonPhep!$A$2:$C$20,3,))-SUMIF($A$3:A3,A3,$D$3)-MIN(D3,F3)

Enter, fill xuống.

Thân

 

[luongkiengxuong]

Điều chỉnh 1 số cấu trúc dữ liệu như file kèm.
Thử:

F3=MAX(VLOOKUP(A3,TonPhep!$A$2:$C$20,3,)-SUMIF($A$2:A2,A3,$D$2),)
G3=E3+F3+MIN(SUMIFS($D$3:D3,$A$3:A3,A3,$C$3:C3,"<="&$F$1),VLOOKUP(A3,TonPhep!$A$2:$C$20,3,))-SUMIF($A$3:A3,A3,$D$3)-MIN(D3,F3)

Enter, fill xuống.

Thân

Em cảm ơn anh Hiệp nhiều nhiều ạ. sự nhiệt tình và tâm huyết của anh đã giúp đỡ em rất nhiều. Một lần nữa xin gởi lời cảm ơn chân thành nhất đến anh, chúc anh nhiều sức khoẻ và gặt hái nhiều thành công trong công việc.