รบกวนช่วยคิดวิธีการแปลงเวลา เป็น ตัวเลข เพื่อนำไปคำนวณโอที

[kanyavee]

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

[snasui]

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

[kanyavee]

ตัวอย่างคำตอบที่ถูกต้อง และ เงือนไขค่ะ เช่น วันที่ 1/7/2554 เลิกเวลา 19.20 จะได้โอที 2.5 (เงือนไขคือ เลิกงานหลัง 19.20 = 2.5) ตามไฟล์เงือนไขที่แนบค่ะ

[kanyavee]

เงื่อนไขดังนี้ค่ะ

[snasui]

ตามไฟล์ที่ผมแนบมา ช่วยโยงลูกศรให้ดูสักสองสามตัวอย่างครับว่าแต่ละค่าในคอลัมน์ G ไปเอาค่าจากจุดไหนในตาราง ถ้าเป็นไปได้ช่วยอธิบายได้ว่า ทำไมถึงไปเอาค่านั้นมา

[kanyavee]

โยงความสัมพันธ์มาให้ดูตามตัวอย่างคะ ดูที่เงื่อนไขเวลาออก เป็นหลักค่ะ

[snasui]

พอจะเห็นแนวทางแล้วครับ แล้ว G14 กับ G16 มาได้อย่างไรครับ

[kanyavee]

ที่มีลูกศรชี้ แปลว่า เข้างานเย็นวันนี้ เลิกงานเช้าวันพรุ่งนี้ G14 เกิดจาก การคำนวณเงือนไขตามตาราง เลิกงาน 07.20 = 2.5 และบวกเพิ่มด้วย ประชุม 3 ชม.
ดังนั้น เท่ากับ 5.5 ชม

สำหรับ G16 เกิดจากเงือนไขตามลูกศรที่โยงคะ

[snasui]

ไม่มีสูตรสำหรับตรวจสอบลูกศรชี้ทะแยงในลักษณะที่ว่าเข้างานเย็นนี้เลิกวันพรุ่งนี้อย่างที่อธิบายมาครับ จะต้องทำ Database ใหม่ ให้ข้อมูลที่จะนำมาหาคำตอบว่าเป็นล่วงเวลาหรือไม่ล่วงเวลาอยู่ในบรรทัดเดียวกัน เหมือน E4:G11 ถึงจะใช้สูตรคำนวณหาออกมาได้ครับ

[kanyavee]

G4 ถึง G11 จะสร้างสูตรคำนวณได้อย่างไรคะ
รบกวนแนะนำด้วยค่ะ ขอบคุณค่ะ

[snasui]

ตรงเงื่อนไขต้องแปลงให้เป็นเวลาเสียก่อนครับ เช่น 16.40 ก็เปลี่ยนเป็น 16:40 เช่นนี้เป็นต้น ไม่งั้นก็ไม่สามารถ Lookup กันได้ครับ

[kanyavee]

รบกวนแนะนำวิธีเปลี่ยน Time ให้สามรถคำนวณได้ และการเขียนเงื่อนไขให้ด้วยค่ะ

[snasui]

ลองตามนี้ครับ

1. คลุมข้อมูลเงื่อนไข
2. กดแป้น Ctrl+H
3. ช่องบนกรอกเครื่องหมาย . ช่องล่างกรอกเครื่องหมาย :
4. กดปุ่ม Replace All

เมื่อได้เงื่อนไขเป็นเวลาแล้วส่งไฟล์มาใหม่ครับ

[kanyavee]

แก้เงื่อนไข ตามที่บอกแล้วค่ะ

[snasui]

ที่ส่งมาแก้ผิดที่ครับ ที่คอลัมน์ G ไม่ใช่เงื่อนไข แต่หมายถึงข้อมูลผลลัพธ์ เงื่อนไขคือไฟล์ที่ชื่อว่าเงื่อนไข แก้ที่ไฟล์นั้นครับ

[Bafnet]

อันนี้ลองดูนะว่าพอจะตรงกับความต้องการหรือไม่
แปลงเวลา 19:20
E4=19:20 ซึ่งอยูในรูปแบบ h:mm
M4=IF(F4="",HOUR((E4)),HOUR((D5))) จะได้ค่า 19 (ต้องกำหนดรูปแบบเซลเป็นทั่วไป หรือตัวเลขนะ)
N4=IF(F4="",MINUTE((E4)),MINUTE((D5))) จะได้ค่า 20 (ต้องกำหนดรูปแบบเซลเป็นทั่วไป หรือตัวเลขนะ)
O4=IF(E4="","",(M4&"."&N4)*1) จะได้ค่า 19.20 (ต้องกำหนดรูปแบบเซลเป็นทั่วไป หรือตัวเลขนะ) เพื่อนำค่าไปเปรียบเทียบกับตารางอ้างอิงการกำหนด ช.ม.โอที
ไล่ดูตามไฟล์แนบนะครับ

ปัญหาคือ
1. exell คุณสกุล.xls ซ้อนสูตรได้ไม่พอกับเงื่อนไขของตารางอ้างอิง ถ้าใช้ .xlsx เพียงพอ
2.เวลาออก ของคุณมีเศษนาทีไม่ตรงกับตารางอ้างอิง + กับข้อจำกัดที่1. จึงต้องสร้างการแปรเวลาใหม่ (คอลัมม์ M:AB การเทียบเวลานี้ทำใน VBA จะสะดวกกว่าครับ กำหนดช่วงเวลาและกำหนดค่าโอที เหมือนกับตัดเกรดคะแนนสอบครับ)
3.ในการคำนวณจากตารางข้อมูลคือตารางข้อมูลเวลาแยกไม่ออกว่าเป็นกะกลางวันหรือกะกลางคืน เช่นวันที่ 21/7/2011 หากคุณไม่ทำลูกศรไว้ก็คงไม่ทราบว่าทำงานกะกลางคืน
อาจเข้าใจได้ว่าเริ่มเข้างาน7.21 เลิกงาน 19.00 ดังนั้นควรกำหนดค่าเงื่อนไขก่อนว่าเป็นกะกลางวันหรือกลางคืน ดังนั้นในเบื่องต้นหากข้อมูลมาแบบนี้ คงต้องให้คุณกำหนดเลข 1 ในวันที่เป็นกะกลางคืน
4.ในตารางอ้างกะกลางคืนมีเวลาออกสูงสุด 9.00 แต่ความจริงมีผู้ออกเวลา 11.30 (วันที่ 13/7/2011) ดังนั้นควรทำตารางอ้างอิงเวลาออกให้ครอบคลุม เท่าที่บริษัทกำหนด
และตารางอ้างอิงกะกลาง เงื่อนไขอยูที่เวลาออก ดังนั้นควรปรับเปลี่ยนให้ช่วงเวลออกเป็นเงื่อนไขที่ชัดเจน เช่น Caseที่5. ไม่เข้าใจว่าทำไมมี 5.40 ซึ่งซ้ำกับ Caseที่1
5.การคิดค่าแรงว่ากี่เท่าอันนี้ไม่แน่ใจว่าคุณมีเงื่อนไขอย่างไร แต่เท่าที่สังเกตุ คิดว่าใครทำในวันหยุดในเวลาปกติ ได้โอที 8 ชม.คิดค่าแรง 1 เท่า ใครทำโอต่อในวันหยุด(ที่ไม่ใช่เสาร์มอาทิตย์) ก็ได้ 1.5 เท่า แต่ใครที่ทำโอวันเสาร์ อาทิตย์ ได้สามเท่า

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

Time July1 ' 2011.xls

[kanyavee]

จากไฟล์ที่ส่งกลับมาให้ถูกต้องตรงกับความต้องการมากๆ ค่ะ แต่ติดปัญหาคือไม่เข้าใจวิธีการ Copy มาค่ะ ลอง Copy มาทำให้ไฟล์ใหม่ (Scan of Aug ' 2011)
ไม่ได้ค่ะ
สำหรับเงื่อนไขได้ปรับแก้มาให้ใหม่แล้วค่ะ รบกวนทำให้ดูเป็นตัวอย่างด้วยค่ะ จักขอบพระคุณเป็นอย่างสูง ไฟล์ใหม่ได้แนบบมาพร้อมกันนี้ค่ะ

[kanyavee]

ตอนนี้ทำได้แล้วค่ะ แต่ยังติดปัญหาบางประการอยู่ รบกวนชี้แนะด้วยค่ะ
1. วันที่เป็นวันหยุด หลัง 16.30 ให้คิดเป็นโอที 3 เท่าค่ะ
2. ช่องที่สีแดงผลการคำนวณไม่ออกค่ะ

ขอบคุณค่ะ

[snasui]

จากไฟล์ที่ส่งกลับมาให้ถูกต้องตรงกับความต้องการมากๆ ค่ะ แต่ติดปัญหาคือไม่เข้าใจวิธีการ Copy มาค่ะ ลอง Copy มาทำให้ไฟล์ใหม่ (Scan of Aug ' 2011)
ไม่ได้ค่ะ
สำหรับเงื่อนไขได้ปรับแก้มาให้ใหม่แล้วค่ะ รบกวนทำให้ดูเป็นตัวอย่างด้วยค่ะ จักขอบพระคุณเป็นอย่างสูง ไฟล์ใหม่ได้แนบบมาพร้อมกันนี้ค่ะ

ในไฟล์เดิมคุณ Bafnet มีการซ่อนพื้นที่คำนวณไว้ที่คอลัมน์ M:AB ครับ หากต้องการจะ Copy ไปใช้แนะนำให้ใช้การ Save As ไปเป็นไฟล์ใหม่ครับ

[Bafnet]

สวัสดีครับ
ขอบคุณอาจารย์ด้วยครับที่ช่วยดูแล

วันหยุด = เสาร์+อาทิตย์+วันหยุดตาม TMT หรือ
วันหยุด = วันหยุดตาม TMT

ลองดูตามไฟล์แนบนะครับ

Scan Aug ' 2011.rar

ปล.ผมว่าตาลายนะ น่าจะทำชีทเป็นรายคนครับมีแค่ 31 วัน 31 บรรทัด
ทำชีทแม่แบบชีทเดียวแล้วสร้างสำเนา แล้วนำข้อมูลแต่ละคนมาใส่

หรือชีทแรกเป็นชีทแม่แบบ ชีทสองเป็นข้อมูลเวลาสแกน
กำหนดชื่อพนักงานที่ชีทแรก ใช้การกรองข้อมูลดึงข้อมูลมาจากหน้าข้อมูลเวลา แสดงผลที่หน้าชีทแม่แบบ ได้เวลาสรุปโอที สั่งพริ๊น..อ่า...
ศึกษาได้จากกระทู้ดีๆของอาจารย์นะครับ