Excel Forum

การคำนวณโอทีแบบมีเงื่อนไขเวลา ดังนี้คะ

วันจ.-ศ.
1. เริ่มงานก่อน 8.30 เริ่มนับ โอที 16.30 น.
2. ตั้งแต่ 8.31 เป็นต้นไป ต้องเริ่มนับ โอที 17.30 น.
3. เกิน 9.30 น. ไม่ได้ โอที
4. คิด โอที ชม.ละ 60 บาท ถ้า 3 ชม. คิดเป็น 200 บาท

วันส.-อา. วันหยุด
1. เหมาจ่าย 7 ชม. 400บาท รวมเที่ยง แต่นับถึง 17.30 น. เท่านั้น
2. ถ้าไม่ถึง 7 ชม. คิด ชม.ละ 60 บาท ตัดช่วง 12.00-13.00 น. ออก 1 ชม.

หากมีเศษชม. ตัดทิ้งหมดไม่มีเงื่อนไข

หลังจากดูหลาย ๆ กระทู้แล้ว สามารถวางสูตรเงื่อนไขได้อย่างเดียวคะ คือ คิดจาก 16.30น.
และคิดเงินได้แล้ว แต่จะใส่เงื่อนไขอื่น ๆ ยังไงดีคะ

รบกวนด้วยนะคะ เพิ่งจะต้องทำเรื่องนี้ เห็นว่า การที่ต้องนั่งทำแบบเมนนนวล ไม่ใช่เครื่องคอมพิวเตอร์ช่วยเป็นเรื่องที่เสียเวลามากคะ
น่าจะใช้เอ็กเซลทำได้ ทำให้ถูกต้อง และประหยัดเวลาด้วยคะ ถ้าทำได้คงเป็นประโยชน์ต่อองค์กรมากคะ

ขอบคุณนะคะ

ลองตามนี้ครับ
ที่ E2 คีย์ =IF(C2="",0,D2-C2) คัดลอกลงมา
ที่ F2 คีย์ =IF(C2="",0,IF(C2<="08:30:00"+0,D2-"16:30:00"+0,IF(C2>"09:30:00"+0,0,D2-"17:30:00"+0))) คัดลอกลงมา
ที่ H2 คีย์ =IF(OR(MID(B2,4,2)="เส",MID(B2,4,2)="อา"),1,0) คัดลอกลงมา
ที่ G2 คีย์ =IF(H2=0,HOUR(F2)*60,IF(AND(E2<"07:00:00"+0,H2=1),(HOUR(E2)-1)*60,400)) คัดลอกลงมา

ขอบคุณมาก ๆ ๆ เลยคะ นั่งลองทำสูตรเองอยู่ทั้งวัน กินพาราไปหลายเม็ดแล้วคะ ^_^
เหมือนพระเจ้ามาโปรด ดีใจมาก ๆ คะ ทั้งองค์กรไม่เคยมีใครทำเลยคะ
นั่งนับนิ้วมือกันตลอดคะ ขอบคุณมาก ๆ นะคะ

แต่มีอีก 2 เงื่อนไขคะ วันจ. - ศ. ข้อ
4. คิด โอที ชม.ละ 60 บาท ถ้า 3 ชม. คิดเป็น 200 บาท
ถ้านับแล้วเกิน 3 ชม. เหมาคิดเป็น 200 บาทคะ

วันส.- อา. ถ้าไม่ถึง 7 ชม. คิดเป็นรายชม. ตัดช่วงเที่ยงออก 1 ชม.
คิด ชม. ละ 60 บาท นับเวลาให้แค่ 17:30 น. เท่านั้นด้วยคะ

จะเพิ่มตรงไหนดีคะ รบกวนด้วยนะคะ

เหมือนพระเจ้ามาโปรด ดีใจมาก ๆ คะ ทั้งองค์กรไม่เคยมีใครทำเลยคะ

อาจารย์ของผมคือ "คนควน" ครับ ตอบสูตรยากกว่านี้เป็นสิบๆเท่า อย่าเพิ่งดีใจและยกยอถึงขั้นนั้นเลยครับ คิดสูตรไม่ออกกันพอดี

ลองตามนี้ดูครับ
ที่ G2 คีย์ =IF(AND(H2=0,HOUR(F2)<3),HOUR(F2)*60,IF(AND(H2=0,HOUR(F2)>=3),200,IF(AND(H2=0,HOUR(F2)>=3),200,IF(AND(E2<"07:00:00"+0,H2=1),(HOUR(E2)-1)*60,400)))) คัดลอกลงมา

ยังติดเงื่อนไขวัน เสาร์-อาทิตย์ อีกครับ อยากทราบว่าเงื่อนไขการเข้าทำงานเหมือนวันปกติหรือไม่ และในกรณีเวลารวมไม่ถึง 7 ชัวโมง ให้คิดหลังจากหักพักเที่ยง 1 ชม. หรือ ก่อนหักพักเที่ยงครับ เช่น นาย A รวมแล้วได้ 7:30 ชม. พอหักพักเที่ยง ก็จะเหลือ 6:30 ชม. แบบนี้ใช่หรือไม่

อีกทั้งที่ว่าคิดถึง 17:30 น. มันคือคิดอย่างไร เพราะสูตรที่ผมเขียนไป หากไม่ถึง 7 ชม. ก็จะคิด ชม. 60 บาท หากมากกว่า 7 ชม. ก็จะเหมา 400 ไปเลย

เสาร์-อาทิตย์ ถ้าครบ 7 ชม. นับก่อน 17.30 น. (ต้องเข้าก่อน 10.30 ถึงจะครบ 7 ชม. ก่อนนั้นได้คะ แต่หลัง 10.31 ไม่ได้คะ เพราะไม่ครบ 7 ชม.) นับรวมเที่ยงคะ จะเหมาจ่าย 400 บาทคะ แต่ถ้าไม่ถึง 7 ชม. ต้องหัก 1 ชม. ออกก่อนคะ แล้วคิด ชม.ละ 60 บาทคะ

ยุ่งยากเนอะ.....รายละเอียดเพียบเลย ถ้าต้องนับนิ้วคงแย่เเน่เลยคะ

ขอโทษที่ตอบช้าคะ คือ เป็นคนทำโอที แต่ไม่ได้โอที กับเขา เลยขอตัวกลับบ้านก่อนคะ

ขอบคุณมากนะคะ ในความช่วยเหลือคะ ตอนนี้ทุกส่วนงานตื่นเต้นใหญ่ ทำสูตรได้ด้วยเหรอคะ
ขอบคุณจริง ๆ คะ

dariga wrote:เสาร์-อาทิตย์ ถ้าครบ 7 ชม. นับก่อน 17.30 น. (ต้องเข้าก่อน 10.30 ถึงจะครบ 7 ชม. ก่อนนั้นได้คะ แต่หลัง 10.31 ไม่ได้คะ เพราะไม่ครบ 7 ชม.) นับรวมเที่ยงคะ จะเหมาจ่าย 400 บาทคะ แต่ถ้าไม่ถึง 7 ชม. ต้องหัก 1 ชม. ออกก่อนคะ แล้วคิด ชม.ละ 60 บาทคะ

ยุ่งยากเนอะ.....รายละเอียดเพียบเลย ถ้าต้องนับนิ้วคงแย่เเน่เลยคะ

ขอโทษที่ตอบช้าคะ คือ เป็นคนทำโอที แต่ไม่ได้โอที กับเขา เลยขอตัวกลับบ้านก่อนคะ

ขอบคุณมากนะคะ ในความช่วยเหลือคะ ตอนนี้ทุกส่วนงานตื่นเต้นใหญ่ ทำสูตรได้ด้วยเหรอคะ
ขอบคุณจริง ๆ คะ

สูตรที่ผมให้ไปล่าสุด แสดงค่าผิดพลาดส่วนไหนบ้างครับ ลองบอกจุดที่ผิดพลาดมาให้ดูหน่อย และคำตอบที่ควรจะเป็นมาให้ดูด้วยครับ จะได้แทนสูตรได้ง่ายขึ้น

ในตาราง ลองแก้เวลาวันอาทิตย์ เป็น เข้า 11.00 น. คะ
ผลลัพธ์ออกมาเป็น 400 บาท แต่เขาต้องได้เงินแค่ 300 บาทคะ คิดเป็น 5 ชม. คะ
เพราะเข้าหลัง 10.31 น. ไม่ครบ 7 ชม.คะ

เรียกว่า จุดที่มีเงื่อนไขเพิ่มดีกว่า ไม่ใช่จุดผิดพลาดคะ (ฟังดูน่ากลัวไปคะ ^_^)

ลองดูในไฟล์นะคะ

ขอบคุณมากคะ

สอบถามอีกนิดครับ

ในวัน เสาร์ - อาทิตย์ คิดโอทีให้คนงานหรือไม่ เช่น
นาย A เข้า 8.40 น. ออก 18.30 น. เวลารวมของเขาเกิน 7 ชม. ให้คิดโอทีให้เขาหลัง 7 ชม. ด้วยหรือไม่ หรือแค่คิดเหมาจ่ายให้เขาไป 400 ไปเลย โดยไม่ต้องคำนึงว่าเขาออกกี่โมง ใช่หรือไม่

ในกรณีที่ นาย A เข้างาน 10.40 น. ออก 19.30 น. เวลารวมของเขาอยู่ที่ 8.50 ชม. แต่เขามาหลัง 10.30 เขาจึงต้องโดนคิดแค่ 17.30 น. เวลาของเคาก็จะได้ 6.50 ชม. หักออกอีก 1 ชม. ก็จะเหลือ 5.50 ชม. คิดเป็นเงิน 5x60 บาท ใช่หรือไม่

วันเสาร์ -อาทิตย์ จะได้โอทีหมดคะ ไม่ต้องหลัง 7 ชม.คะ เพราะไม่ใช่วันทำงานคะ
แต่ต้องทำงานให้ครบ 7 ชม. ขึ้นไป ถึงได้เหมาจ่าย 400 บาทคะ โดยการนับ 7 ชม.
ต้องนับก่อน 17.30 น.คะ แต่ถ้าช้าเพียง 1 นาที ก็จะคิดเป็นรายชั่วโมง หัก เที่ยง 1 ชม.
ถ้าเริ่มก่อนเที่ยงคะ ถ้าเริ่ม 13.00 น. ก็ไม่หัก 1 ชม. คะ แล้ว คูณ ด้วย 60 บาทคะ

ถ้านาย A เข้า 8.40 ออก 18.30น. จะเกิน 7 ชม. เหมาจ่าย 400 บาทคะ
แต่ถ้านาย A เข้า 10.40 ออก 19.30 น. คิดเวลา 6 ชม. (ปัดเศษ 50 นาที ทิ้งคะ)
หักออก 1 ชม. เหลือ 5 ชม. คิดเป็นเงิน 5X60 บาท

ต้องนับเวลา ก่อน 17.30 น. ถ้าหลังจากนั้นไม่คิดเวลา ทั้ง 2 กรณีคะ

งั้นลองสรุปตามนี้ดูใหม่อีกรอบครับ

1. หาก่อนว่าวันไหนวันทำงาน วันไหนวันหยุด
ที่ H2 คีย์ =IF(OR(MID(B2,4,2)="เส",MID(B2,4,2)="อา"),1,0) คัดลอกลงมา

2. คิดเวลารวมทั้งวัน หากเป็นวันหยุด เกิน 7 ชม. ให้แสดง 7 ชม. หากไม่ถึง 7 ชม. ก็ให้คิดถึง 17:30:00 หัก ออก 1 ชม. เบ็ดเสร็จ
ที่ E2 คีย์ =IF(C2="",0,IF(AND(C2<="10:30:00"+0,H2=1),"07:00:00"+0,IF(AND(C2>"10:30:00"+0,H2=1),("17:30:00"+0-C2)-"1:00:00"+0,D2-C2)))
คัดลอกลงมา

3. หาโอทีในแต่ละวัน หากเป็นวันหยุด ให้แสดง 0 ชม. ไปเลย เพราะไม่ใช่วันทำงาน
ที่ F2 คีย์ =IF(C2="",0,IF(AND(C2<="08:30:00"+0,H2=0),D2-"16:30:00"+0,IF(AND(C2>"09:30:00"+0,H2=0),0,IF(AND(C2<>"",H2=1),0,D2-"17:30:00"+0)))) คัดลอกลงมา

4. คิดโอที
ที่ G2 คีย์ =IF(AND(H2=0,HOUR(F2)<3),HOUR(F2)*60,IF(AND(H2=0,HOUR(F2)>=3),200,IF(AND(H2=0,HOUR(F2)>=3),200,IF(AND(E2<"07:00:00"+0,H2=1),HOUR(E2)*60,400))))
คัดลอกลงมา

โอ้พระเจ้า จอร์จ มันยอดมากเลย....^_^

ไม่น่าเชื่อว่าในที่สุด เอ็กเซลล์ ก็เป็นที่พึ่งของเราได้
รักเอ็กเซลล์จังเลย

กราบขอบพระคุณงาม ๆ เลยนะคะ เพิ่งต้องรู้ว่าจะต้องทำเรื่องนี้เมื่อวาน
วันนี้เราได้สูตรแล้ว ดีใจจริง ๆ คะ .....ขอบคุณนะคะ ๆๆๆๆๆ

คราวนี้ถ้าวันไหนเป็นวันหยุดเพิ่ม วันหยุดราชการ เราก็ใส่เลข 1 ในช่อง H ได้ใช่ไหมคะ
เพราะคงมีไม่เยอะคะ คงไม่ต้องใช้สูตรมั้งคะ

ขอบคุณมาก ๆ นะคะ

dariga wrote: โอ้พระเจ้า จอร์จ มันยอดมากเลย....^_^

ไม่น่าเชื่อว่าในที่สุด เอ็กเซลล์ ก็เป็นที่พึ่งของเราได้
รักเอ็กเซลล์จังเลย

กราบขอบพระคุณงาม ๆ เลยนะคะ เพิ่งต้องรู้ว่าจะต้องทำเรื่องนี้เมื่อวาน
วันนี้เราได้สูตรแล้ว ดีใจจริง ๆ คะ .....ขอบคุณนะคะ ๆๆๆๆๆ

คราวนี้ถ้าวันไหนเป็นวันหยุดเพิ่ม วันหยุดราชการ เราก็ใส่เลข 1 ในช่อง H ได้ใช่ไหมคะ
เพราะคงมีไม่เยอะคะ คงไม่ต้องใช้สูตรมั้งคะ

ขอบคุณมาก ๆ นะคะ

เอ็กเซลล์ ทำอะไรได้มากกว่านี้อีกมายนักครับ

คราวนี้ถ้าวันไหนเป็นวันหยุดเพิ่ม วันหยุดราชการ เราก็ใส่เลข 1 ในช่อง H ได้ใช่ไหมคะ

ในกรณีที่มันแสดงตัวเลขด้วยสูตร เราไม่ควรจะไปใส่ตัวเลขด้วยมือ มันจะทำให้สูตรไม่ต่อเนื่อง และไม่เป็นไปตามหลักการของมัน

ลองตามนี้ครับ
ที่ B2 คีย์ 7/8/2012 จัดรูปแบบเซลล์เป็น "วววว-ดดดด-ปปปป" แล้วคัดลอกลงมา กรณีวันหยุด มีการทำงานบ้าง ไม่ทำบ้าง ให้ใช้การคีย์เปลี่ยนวันที่เอาเอง หรือ คัดลอกยาวลงมาเลย วันไหนไม่มีการทำงาน ก็เว้นว่างไว้ไม่เสียหายอะไร

ที่ H2 คีย์ =IF(AND(IF(OR(WEEKDAY(B2)=7,WEEKDAY(B2)=1),1,0)=0,I2=1),1,IF(AND(IF(OR(WEEKDAY(B2)=7,WEEKDAY(B2)=1),1,0)=1,I2=""),1,0))
คัดลอกลงมา

ในกรณีที่ต้องการระบุว่าวันไหนเป็นวันหยุดนักขัตฤกษ์ ให้คีย์เลข 1 ลงในคอลัมน์ I ให้ตรงกับวันหยุดนักขัตฤกษ์นั้น

ลองดูในไฟล์แนบครับ

จริง ๆ ฐานข้อมูล ไปคัดลอกมาจากระบบคะ ช่อง A B C D E คะ
ถ้าให้แก้ช่อง B คงจะต้องแก้ใหม่ทุกครั้งคะ

ถ้าไม่แก้ สูตร ช่อง H2 จะใช้ไม่ได้ใช่ไหมคะ

เอ็กเซลล์ ทำอะไรได้มากกว่านี้อีกมายนักครับ

จริงเหรอคะ ต้องศึกษาเทคนิคเพิ่มซะแล้ว
ไว้ขอคำแนะนำอีกนะคะ
ขอบคุณล่วงหน้าคะ

dariga wrote:จริง ๆ ฐานข้อมูล ไปคัดลอกมาจากระบบคะ ช่อง A B C D E คะ
ถ้าให้แก้ช่อง B คงจะต้องแก้ใหม่ทุกครั้งคะ

ถ้าไม่แก้ สูตร ช่อง H2 จะใช้ไม่ได้ใช่ไหมคะ

หากเป็นเช่นนั้น ที่ H2 แก้สูตรเป็นดังนี้
=IF(AND(IF(OR(MID(B2,4,2)="เส",MID(B2,4,2)="อา"),1,0)=1,I2=""),1,IF(AND(IF(OR(MID(B2,4,2)="เส",MID(B2,4,2)="อา"),1,0)=0,I2=1),1,IF(AND(IF(OR(MID(B2,4,2)="เส",MID(B2,4,2)="อา"),1,0)=1,I2=1),1,0))) คัดลอกลงมา

แล้วในคอลัมน์ I ก็ให้ใส่เลข 1 กรณีเป็นวันหยุดนักขัตกฤษ์ครับ

ว้าว สุดยอดคะ สามารถทำได้ด้วย........^_^

ถ้าไม่รู้จักเอ็กเซลล์ คงเสียใจแย่

แต่ถ้าไม่ได้คำแนะนำจากคุณแบงค์ คงแย่กว่า ...ขอบคุณนะคะ ^_^

อ้อ ลืมไปคะ ขออนุญาติเผยแพร่สูตรนี้ให้กลุ่มงานอื่นทราบนะคะ
เพื่อเป็นวิทยาทาน ให้กับกลุ่มอื่น ๆ ด้วยคะ

แล้วจะยกเครดิต ขอบคุณให้ทั้งท่านอาจารย์ "คนควน"
และคุณแบงค์นะคะ ...^_^

แต่ต้องมีสรุปใส่อีกไฟล์นึงด้วย สรุปยอดเงินทุกคน ให้บัญชีคะ
จะลองทำเองก่อนนะคะ ว่าทำได้ไหม สงสัยจะใช้
แบบให้มันดึงข้อมูลจากเซลล์เอาคะ คงไม่ต้องมีสูตรใช่ไหมคะ

ขอบคุณนะคะ

อ้อ ลืมไปคะ ขออนุญาติเผยแพร่สูตรนี้ให้กลุ่มงานอื่นทราบนะคะ
เพื่อเป็นวิทยาทาน ให้กับกลุ่มอื่น ๆ ด้วยคะ

ตามสบายครับ ไม่ต้องขออนุญาตก็ได้ครับ เพราะยิ่งคนอื่นรู้มากขึ้นเท่าไหร่ ก็จะดีเท่านั้น ผมไม่สงวนความรู้มีแต่อยากให้คนอื่นรู้ตามมากกว่าครับ

แต่ต้องมีสรุปใส่อีกไฟล์นึงด้วย สรุปยอดเงินทุกคน ให้บัญชีคะ
จะลองทำเองก่อนนะคะ ว่าทำได้ไหม สงสัยจะใช้
แบบให้มันดึงข้อมูลจากเซลล์เอาคะ คงไม่ต้องมีสูตรใช่ไหมคะ

กรณีนำข้อมูลจากไฟล์อื่น หรือ ชีทอื่นมาแสดง จำเป็นต้องใช้สูตรครับ ลองทำตัวอย่างไฟล์มาก่อนครับ หากเป็นไปได้ ให้ตั้งกระทู้ใหม่ในกรณีที่ปัญหาใหม่นี้ต่างกับปัญหาเดิมครับ

สรุปอีกครั้งครับ กรณีการใช้สูตรหาโอที ที่ได้ถามมาทั้งหมด จะใช้สูตรตามไฟล์นี้ครับ

เมื่อสักครู่ ลองเผยแพร่แล้วคะ ร้อนวิชาคะ
แต่มันมีปัญหานิดนึง ตรงที่ถ้าเขาไม่ได้โอทีคือไม่ถึง 17.30 น.
ในตารางมันขึ้น ##### ในช่อง F คะ ช่อง G เลยติด NUM ด้วยเลยคะ
แต่ถ้ากด 0 ลงไปช่อง F คำตอบออกมาคะ

แล้วถ้าข้อมูลเวลาออก ไม่มี มันก็ขึ้น #### คะ

ลองดูในไฟล์แนบนะคะ

อ้อ แล้วเวลากอปปี้ สูตรไปวางชีทใหม่ทำไมไม่ได้คะ
ต้องเอาข้อมูลใหม่ มาทำชีทเดิมคะ มีเทคนิคการกอปปี้สูตรยังไงคะ
เมื่อกี้ กอปปี้ไปแล้วไม่ขึ้นสูตรคะ มันกลับมาชีทเดิมเองเลยคะ
ดูเหมือนปัญหาง่าย นะคะ