Excel Forum

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

ตามไฟล์แนบ ในเซลล์ B8 จะเป็นช่องรวมโอทีทั้งหมดของพนักงานครับ และเซลล์ B9 จะเป็นช่องรวมเงินโอทีที่ได้ครับ

เซลล์ B8 รวมเวลาจาก B12:B42 ได้เท่าไรก็ให้นำมาลบกับ 08:30:00 คูณด้วยจำนวนวันที่มาทำงาน ผมจึงใช้สูตร =SUM($B$12:$B$42)-COUNT($B$12:$B$42)*"08:30:00"
กรณีที่เวลารวมไม่เกิน 23:59:59 สูตรจะรวมถูกต้อง แต่หากเกินเวลาดังกล่าวแล้ว มันจะกลายเป็น 00:00:00 ซึ่งผมก็พอเข้าใจว่าเพราะเหตุใด เพียงแต่ว่าจะใช้ฟอร์แมตไหนในการจัดการ เพราะฟอร์แมตที่จะวางลงไปนั้น มีผลต่อสูตรการคิดเงินในเซลล์ B9 ด้วย

รบกวนดูให้หน่อยครับ

เซลล์ B8 ใช้สูตรที่เขียนมาตามด้านบน จากนั้นจัด Format เป็น

[h]:mm:ss

ส่วน B9 คีย์สูตร

=B8*D8*24

Enter แล้วสังเกตดูผล

ผลลัพธ์ยังไม่ถูกเลยครับอาจารย์ วิธีการดังกล่าวผมได้ลองใช้แล้ว

จะแนบไฟล์จริงมายังไงดีหนอ....

ลองสูตรของผมดูนะครับ ผมคำนวนแล้วได้เวลาทั้งสิ้น 136 ชั่วโมง 20 นาที
ที่ B8 ใส่ =(SUM(HOUR(IF(ISNUMBER($B$12:$B$42),$B$12:$B$42,0))))+(INT((SUM(MINUTE(IF(ISNUMBER($B$12:$B$42),$B$12:$B$42,0)))/60)))+(((MOD((SUM(MINUTE(IF(ISNUMBER(B12:B42),B12:B42,0)))/60),1)*60))/100)
Ctrl + Shift + Enter

ผมแยกชั่วโมงออกมากจากเวลาก่อน จากนั้นจำข้อมูลของนาทีมารวมกัน แล้วหารด้วย 60 เพื่อดึงชั่วโมงออกมาอีกรอบ แล้วนำไป + กับชั่วโมงในชุดแรก จากนั้นนำเศษที่เหลือ ไป * 60 แปลงเป็นฐาน 60 ของหน่วยเวลาครับ แล้วค่อย /100 เพื่อปรับคืนเป็นฐาน 100 แล้วไป + กับชั่วโมง จะได้ 136.20 แล้วนำตัวเลขนี้ไป * กับเวลา / ชั่วโมงเลย เพราะถ้าไม่แยก เวลาต่อชั่วโมงเป็นฐาน 100 เอาฐาน 60 ไปคำนวณด้วย มันจะผิด

ที่ B9 ใส่ =B8*D8

ลองดูนะครับ

songsug wrote:ลองสูตรของผมดูนะครับ ผมคำนวนแล้วได้เวลาทั้งสิ้น 136 ชั่วโมง 20 นาที
ที่ B8 ใส่ =(SUM(HOUR(IF(ISNUMBER($B$12:$B$42),$B$12:$B$42,0))))+(INT((SUM(MINUTE(IF(ISNUMBER($B$12:$B$42),$B$12:$B$42,0)))/60)))+(((MOD((SUM(MINUTE(IF(ISNUMBER(B12:B42),B12:B42,0)))/60),1)*60))/100)
Ctrl + Shift + Enter

ผมแยกชั่วโมงออกมากจากเวลาก่อน จากนั้นจำข้อมูลของนาทีมารวมกัน แล้วหารด้วย 60 เพื่อดึงชั่วโมงออกมาอีกรอบ แล้วนำไป + กับชั่วโมงในชุดแรก จากนั้นนำเศษที่เหลือ ไป * 60 แปลงเป็นฐาน 60 ของหน่วยเวลาครับ แล้วค่อย /100 เพื่อปรับคืนเป็นฐาน 100 แล้วไป + กับชั่วโมง จะได้ 136.20 แล้วนำตัวเลขนี้ไป * กับเวลา / ชั่วโมงเลย เพราะถ้าไม่แยก เวลาต่อชั่วโมงเป็นฐาน 100 เอาฐาน 60 ไปคำนวณด้วย มันจะผิด

ที่ B9 ใส่ =B8*D8

ลองดูนะครับ

ขอบคุณมากครับ แต่ก็ยังไม่ถูกน่ะครับ เพราะเวลาที่ควรจะได้คือ ประมาณ 25 ชั่วโมงกว่าๆ ครับ

ลองคิดมาให้ดูเป็นตัวอย่างครับว่า 25 ชม. กว่า ๆ มาได้อย่างไร

รบกวนดูตามไฟล์แนบครับ
พอดีว่าผมทำไว้หลายขั้นตอน ต้องแยกเวลาออกมาเป็น ช.ม นาที วินาที แล้วนำมาคำนวนหาเวลารวมและยอดเงินครับ

(ต้องอธิบายขั้นตอนการทำด้วยไหมหนอ หรือไม่จำเป็น)

ลองตรวจสอบการคีย์เวลาอีกรอบครับ

เช่น E21:E22 โอทีจะเป็น 27:56:20 และ 28:13:36 ตามลำดับ รวมสองค่านี้ก็เกิน 50 ชม. แล้วครับ

สรุปคือ ข้อมูลที่ผมคัดลอกมาทำเป็นไฟล์แนบนั้นผิดน่ะครับ

เนื่องจากว่า ข้อมูลในฐานข้อมูลมีการคีย์ผิดพลาด เพราะยังไม่ได้ทำระบบลงเวลาย้อนหลังให้พนักงาน เลยใช้วิธีคีย์ด้วยมือลงในฐานข้อมูล มันเลยผิดอย่างที่เห็นครับ เช่น เวลาเข้า 16/04/2012 8:00:00 น. แต่ไปออกตอน 17/04/2012 17:30:30 น
ด้วยเหตุนี้เองครับ ที่ทำให้การคำนวนออกมาผิดพลาด

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

สำหรับอาจารย์นั้น ผมมีเรื่องต้องสะสาง เพราะสับขาหลอกผมอีกแล้ว

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

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