ตารางคำนวนชั่วโมงทำงานและล่วงเวลา

[Nakorn]

To คุณสันติ
มีขนมขบเคี้ยวมาให้เคี้ยวเล่นครับ หลังจากนั้นจะมี Sea Food ตามมาทีหลัง (ก็คงจะเป็นพวกปูพวกกั้งประมาณนั้น)

ผมสงสารน้องที่ทำงานครับ นั่งคีย์เวลาของพนักงานเป็นร้อย
ตัวอย่างไฟล์ที่แนบมานี้ เป็นรูปแบบการบันทึกเวลาทำงานของพนักงานแต่ละบุคคล ตารางที่1 คือรูปแบบของ Time ตารางที่2 คือรูปแบบของ General
โดยปรกติจะใช้รูปแบบของ General ในการบันทึก เพราะง่ายต่อการนำไปใช้งานต่อ
ตารางที่ 1
Column D คือ เวลาเข้างาน
Column E คือ เวลาพักกลางวัน
Column F คือ เวลาเลิกงาน
Column G คือ ชั่วโมงทำงาน
Column H-K คือ ล่วงเวลา
ในตารางที่ 2 ก็คล้ายกันครับ
การคิดเวลา จะคิดเศษเป็น 1/2 ชม.

ปล. คุณจะใช้รูปแบบไหนก็ได้นะครับตามสะดวกเลย
Cell ที่ G36 และ J36 แก้ไขไงครับ ถ้าเกิน 24 ชม. มันแสดงแค่ ชม.กับ นท.

[snasui]

ขอเงื่อนไขด้วยครับ หรือหากเงื่อนไขอยู่ในชีทใด แจ้งด้วยครับ เพื่อจะได้เข้าถึงข้อมูลด้วยรวดเร็ว

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

ส่วนการกำหนดให้แสดงเวลาเกิน 24 ชม. ให้กำหนดรูปแบบเป็น [h]:mm ครับ

สำหรับชื่อจริงผมคือ สันติพงศ์ ครับ

[Nakorn]

To สันติพงศ์
ต้องขอโทษด้วยที่โพสท์ช้า งานยุ่งไปหน่อย
1. ไฟล์แนบ TextConvert.xls เป็นไฟล์ที่ Convert มาจากเครื่องสแกน
Text Column A คือ Code ID. ของพนักงาน
Text Column B
1-4 = Year
5-8 = Time In
9-12 = Time Out
13-End = Code Machine Record
ตัวเลขที่ใช้คือ Time In และTime Out
ปล. สังเกตุว่า ถ้ามีการสแกนซ้ำเครื่องจะทำการบันทึกทุกครั้ง เช่น เข้า 8:01, 8:02, 8:03 ออก 17:05 เครื่องจะบันทึก 08011705, 08021705 และ 08021705 หรือ เข้า 8:00, ออก 17:05, 17:06,17:07 เครื่องจะบันทึก 08001705, 08001706, และ 08001707
ช่วงเข้าจะเอาเวลาครั้งแรก ช่วงออกจะเอาเวลาท้ายสุด แต่ไม่เกิน 3 ครั้ง
ในส่วนนี้สามารถใส่เป็น Menual และลบ Row ออกได้ครับเพื่อลดขั้นตอนไม่ให้ซับซ้อนมาก
2. ไฟล์แนบ TimeRecord.xls ส่งมาใหม่ครับ
Column D และ F จะ Link ข้อมูลมาจาก ไฟล์ TextConvert.xls (Column E ไม่จำเป็นก็ได้)
Column G = ชม. ทำงานทั้งหมดในวันนั้นๆ
Column H = ชม. เวลาชดเชย เช่น เข้า 9:00 ออก 20:00 รวม 10 ชม.ทำงาน(ล่วงเวลา 17:00-20:00 = 3 ชม.) แต่ ชม.ปรกติขาด 1 ชม. (9:00-17:00 = 7:00 ชม.) จึงต้องหักล่วงเวลามาชดเชย 1 ชม.
Column I = ล่วงเวลาปรกติ หลังจากหักเวลาชดเชยแล้ว
Column J = ล่วงเวลาวันหยุด ไม่ต้องหักชดเชย
Column K = ชม. ทำงานวันเสาร์และวันหยุดอื่นๆ (ยกเว้นวันแรงงาน) ไม่ต้องหักชดเชย
Column L = ชม. ทำงานวันอาทิตย์และวันแรงงาน ไม่ต้องหักชดเชย
ด้วยความขอบคุณ
นคร
ปล. ในส่วนอื่นๆที่ไม่นำมาแสดง เพราะเป็นแค่ Funtion ธรรมดา ไม่มีความซับซ้อน ในส่วนของ ชม.ลา ตรงนี้ใส่เป็น ?ฟืฟส อยู่แล้ว

[snasui]

ผมดูแล้วยังไม่ทราบชัดเจนว่าสิ่งที่ต้องการคืออะไร เดาว่า

1. จากไฟล์ TextConvert ให้นำเวลามาใช้ เงื่อนไขคือ จาก ID.No ของพนักงาน ในชีท Part-1 เซลล์ C1 ไฟล์ TimeRecord
2. คำนวณเวลาในคอลัมน์ G:L ในไฟล์ TimeRecord

ถ้าเป็นเช่นที่เดานี้ สิ่งที่จะต้องทำคือ

1. จะต้องแปลงค่าที่ได้จากไฟล์ TextConvert ออกมาก่อน เพื่อให้ได้ค่าต่าง ๆ ที่ต้องการนำไปใช้ ไม่ว่าจะเป็น วันเดือนปี, เวลา ฯลฯ
2. Link ค่าต่าง ๆ เข้ามาใช้ในข้อ 1 โดยดูตามงวดเวลาและรหัสพนักงาน
3. คำนวณเวลาตามเงื่อนไข

สิ่งที่เดาด้านบนถูกต้องบ้างหรือไม่ครับ

[Nakorn]

ผมดูแล้วยังไม่ทราบชัดเจนว่าสิ่งที่ต้องการคืออะไร เดาว่า

1. จากไฟล์ TextConvert ให้นำเวลามาใช้ เงื่อนไขคือ จาก ID.No ของพนักงาน ในชีท Part-1 เซลล์ C1 ไฟล์ TimeRecord
2. คำนวณเวลาในคอลัมน์ G:L ในไฟล์ TimeRecord

ถ้าเป็นเช่นที่เดานี้ สิ่งที่จะต้องทำคือ

1. จะต้องแปลงค่าที่ได้จากไฟล์ TextConvert ออกมาก่อน เพื่อให้ได้ค่าต่าง ๆ ที่ต้องการนำไปใช้ ไม่ว่าจะเป็น วันเดือนปี, เวลา ฯลฯ
2. Link ค่าต่าง ๆ เข้ามาใช้ในข้อ 1 โดยดูตามงวดเวลาและรหัสพนักงาน
3. คำนวณเวลาตามเงื่อนไข

สิ่งที่เดาด้านบนถูกต้องบ้างหรือไม่ครับ

1. ใช่ครับ TextConvert ใช้เฉพาะ ID.No, เวลาเข้า และเวลาออก
ตัวอย่าง
ID.No,31 เวลาเข้า 8:07 เวลาออก 17:07 ใน D5 และ F5 ซึ่งกับ Row 11 ใน TextConvert (Text out 5-8 และ 9-12)
2. ใช่ครับ

ถ้าเป็นเช่นที่เดานี้ สิ่งที่จะต้องทำคือ

1-3 ถูกต้องครับ
ปล.ผมได้คุยกับทาง Adm. แล้ว และได้ลดเงื่อนไขลง (ทำงานบ้าง เอ๊ะอะก้อ Autoๆๆๆ )

[snasui]

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

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

[Nakorn]

1. Sheet 01-31 ทดแทน Sheet 01032011 นะครับ เพื่อให้มองเห็นเงื่อนไขได้ง่ายขึ้น
2. Sheet 01-31 คือ ไฟล์ที่ Convert ทุกๆวันใน 1 เดือน (ในที่นี้คือ 31 วัน) ใน Column A และ B ซึ่งเมื่อถึงเวลาก็นำข้อมูลมาใส่ (บาง Sheet อาจจะว่าง และใน 1 Sheet คือจำนวนคนที่มาทำงานตาม ID.No.) ตรงนี้ อาจารย์ไม่ต้องไปสนใจวันที่หรอกครับ จะเป็นภาระมากขึ้นเปล่า เพราะส่วนใหญ่ Timerecord จะมีลักษณะแบบนี้
3. ใน Column D และ F นั่นแหละ ที่ดึงข้อมูลมาจาก Sheet 01-31
4. 1 ไฟล์ (TimeRecord) ต่อ 1 ID. ครับ

[snasui]

คงต้องค่อย ๆ ทำไปนะครับ

ผมปรับสูตรมาให้ใหม่ในชีท Mr.A เพื่อให้ใช้สูตรเดียวแล้วสามารถ Copy ลงไปด้านล่างได้เลย ไม่ต้องแก้สูตรกันทุกบรรทัด

ลองตรวจสอบดูค่าที่คำนวณได้ในคอลัมน์ G ว่าถูกต้องหรือไม่ครับ หากเข้างาน 13:00 น. แล้ว ต้องหักเวลาพักหรือไม่

[Nakorn]

Column G เงื่อนไขตามไฟล์แนบครับ
ข้อสังเกตุ
ช่วงเวลาเข้าออกระหว่าง 12:00-13:00 น. จะไม่คิดให้ เช่น เข้า 8:00 ออก 13:00 น.และ เข้า12:00 ออก 17:00 น. จะไม่ได้ 5 ชม. แต่จะได้ 4 ชม.

[snasui]

ลองดูว่าสูตรในคอลัมน์ G ให้ผลลัพธ์ที่ถูกต้องหรือเปล่าครับ

ที่ H5 คีย์

=IF(AND(D5="",F5=""),"",ROUNDDOWN((F5-D5-IF(AND(D5<=12/24,F5>=13/24),E5,0))*48,0)/48)

Enter > Copy ลงด้านล่าง

ดูไฟล์แนบประกอบครับ

[Nakorn]

ต้องขออนุญาติแก้ไขตัวบางส่วนนะครับ เพื่อความเหมาะสม
ตอนนี้มากว่าครึ่งทางแล้วครับ
จากสูตร ผมลองทดสอบและใส่เวลาเพื่อทดสอบ (Row ที่ 32-35)
ผลปรากฎว่า ติดอยู่ที่ Row 35 ครับ โดยเงื่อนไข 08:16-17:16 คำตอบจะต้องเป็น 07:30 แต่ผลเป็น 08:00 ตามไฟล์แนบครับ

[snasui]

ยังงงกับเงื่อนไขการหักเวลาในการเข้างานครับ

ถ้าเข้า 8:16 หักครึ่งชม. หากเข้า 9:00 หรือ ฯลฯ เช่นเซลล์ G12, G13 มีการหักครึ่งชม.หรือไม่ กรณีไหนหักหรือไม่หัก ช่วยแจกแจงรายละเอียดด้วยครับ

[Nakorn]

G12 และ G13 ถูกต้องแล้วครับ
Row 12 = เข้า 8:30 ออก 20:00 = 8:30-17:00 = 7:30 ชม. (เวลาปรกติขาด 1/2 ชม.)
ล่วงเวลา 17:00-20:00 = 3:00 ชม.
เพราะฉนั้น จึงต้องหักเวลาจากล่วงเวลาไป 1/2 ชม. ชดเชยที่ OT. x 1.0 = 0:30 ชม. จึงหลือล่วงเวลาจริง = 2:30 ชม.
Row 13 เช่นเดียว ในส่วนนี้ไม่มีปัญหาครับ
มันมีปัญหาตรงนี้ครับ ที่ Flexible-Time (ยืดหยุ่นเวลา) ไปถึงเวลา 8:15 น. ถ้าเข้างานไม่เกินนี้จะถือว่าไม่สาย แต่ว่า ต้องตอนเลิกงาน จะต้องให้จำนวนนาทีไม่น้อยกว่าตอนเข้า ตัวอย่าง
1. เข้า 8:15 ออก 17:15 จะได้ 8:00 ชม. (อยู่ในเงื่อนไข)
2. เข้า 8:07 ออก 17:07 จะได้ 8:00 ชม. (อยู่ในเงื่อนไข)
3. เข้า 8:15 ออก 17:14 จะได้ 7:30 ชม. (เวลาออกไม่ถึงเงื่อนไข)
4. เข้า 8:07 ออก 17:06 จะได้ 7:30 ชม. (เวลาออกไม่ถึงเงื่อนไข)
5. เข้า 8:16 ออก 17:16 จะได้ 7:30 ชม. (สังเกตุนะครับ เวลาเข้าเกินจากเงื่อนไข จึงปรับเป็นสาย 0:30 ชม. ถึงแม้นาทีจะจะได้ แต่ไม่มีผล)
จุดนี้แหละครับที่เป็นเจ้าปัญหา ถ้าไม่มีในเรื่องของ Flexible-Time (ยืดหยุ่นเวลา) เข้ามาเกี่ยวข้อง สูตรที่อาจารย์ให้มา Perfect ทุกอย่าง
ผมทำมาหลายรอบแล้ว แต่สมองน้อยๆของผมมันมึนไปหมดก็ตรงนี้แหละ
ปล.อาจารย์ดูสูตรที่ให้มาซิครับ ลงตัวหมด ยกเว้น Row 35 นี่แหละ พอคีย์ 8:16, 8:17 => และ นาที ออกมากกว่า มันจะออกเป็น 8:00 ชม. เลย

[snasui]

อย่าไปมุ่งแต่ 8:xx ครับ ให้ดูที่ 9:xx, 10:xx ฯลฯ

เช่น บรรทัดที่ 13 เข้า 9:00 และ ออก 20:00 ผลลัพธ์ใน G13 ได้ 10:00 เป็นผลมาจาก

1. 9:00-12:00 ทำงาน 3 ชม.
2. 13:00-20:00 ทำงาน 7 ชม.

รวมทำงาน 10 ชม.

คำถาม เข้าสายกว่า 8:15 ทำไมไม่หัก 1/2 ชม.

[Nakorn]

อย่าไปมุ่งแต่ 8:xx ครับ ให้ดูที่ 9:xx, 10:xx ฯลฯ

เช่น บรรทัดที่ 13 เข้า 9:00 และ ออก 20:00 ผลลัพธ์ใน G13 ได้ 10:00 เป็นผลมาจาก

1. 9:00-12:00 ทำงาน 3 ชม.
2. 13:00-20:00 ทำงาน 7 ชม.

รวมทำงาน 10 ชม.

คำถาม เข้าสายกว่า 8:15 ทำไมไม่หัก 1/2 ชม.

ถ้าเกิน 8:15 คือ 8:16-8:29 จะตัดไปที่ 8:30 เลย
และจาก 8:30 ไปนั้นคิดปรกติครับ เช่น 8:30 -12:00 = 3:30, 9:00 -12:00 = 3:00 นั่นแหละ

[Nakorn]

อาจารย์ครับ ผมลองเพิ่ม Column และใส่เงื่อนไขดู ผลปรากฎว่าได้ครับ อีกซักคู่ผมจะส่งให้อาจารย์พิจารณา

[Nakorn]

อาจารย์ครับ ผมลองเพิ่ม Column และใส่เงื่อนไขดู ผลปรากฎว่าได้ครับ อีกซักคู่ผมจะส่งให้อาจารย์พิจารณา

[Nakorn]

ผลออกมาแล้วครับ อาจารย์ช่วยพิจารณาปรับปรุงหน่อยครับ ( เย้บบบบบบบบ สำเร็จอีกขั้นแล้วครับอาจารย์ )
ไฟล์แนบ "TimeRecord(6).xls"

[snasui]

ไม่พบไฟล์แนบครับ

[Nakorn]

ขอโทษครับพอดีรีบเลิกงานไปเต๊ะบอลกับน้องๆหลานที่ทำงานนะครับ
เรามาว่ากันต่อ
ไฟล์แนบ <TimeRecord(6).xls>
แผ่นงาน <Test>
1. กรณีเงื่อนไข Flexible Time (ยืดหยุ่นเวลา) แก้ได้แล้วครับ ( ผมเพิ่ม Column F เข้ามา แล้วลองทำดู
ผลออกมาใช้ได้ อาจารย์ช่วยพิจารณาด้วยครับ)
2. เกิดกรณีใหม่ขึ้นมาที่ Row 27,28 และ 31, 32