ปัญหาเกี่ยวกับการคำนวณเวลา

[run]

สวัสดีครับอาจารย์ ผมมีเรื่องจะรบกวนสอบถามอาจารย์เป็นข้อๆครับ
เรื่องมีอยู่ว่า ผมมีเครื่องแสกนลายนิ้วมือ ลงเวลาเข้าและออกงาน ซึ่งโปรแกรมที่เค้าแถมมาให้นั้น แจ้งได้แต่เวลาที่เข้าและออก ไม่สามารถคิดโอทีเช้า-เย็นได้ครับ ที่ผ่านมา ผมใช้วิธีนั่งลบตัวเลขเองเพื่อคำนวนหาโอทีเช้า-เย็น (ทำมานานกว่าครึ่งปี)ซึ่งก็เสียเวลามาก ติดต่อไปที่บริษัท ก็ได้รับแจ้งว่า ต้องซื้อโปรแกรมเงินเดือนเพิ่มเติม เพื่อมาคำนวณโอทีเช้า-เย็นให้ได้ แต่ไม่แนะนำเราว่าสามารถ export ไฟล์ออกมาคำนวณได้ จนวันนี้ผมพึ่งทราบว่าโปรแกรมลงเวลาเข้าออกสามารถ export ข้อมูลออกมาเป็นไฟล์ exel ได้ครับ เลยว่าจะลองใส่สูตรเพื่อคำนวณเอาครับ จะได้ไม่ต้องเสียเงินซื้อโปรแกรมเพื่อคำนวณโอทีเช้า-เย็นครับ
พร้อมกันนี้ผมได้แนบไฟล์ตัวอย่างมาแล้ว รบกวนอาจารย์ช่วยแนะนำซักหน่อยครับ

1.ถ้าช่อง H ผมต้องการให้เป็นโอทีเช้า หมายความว่า (08:00-เวลาที่เข้างาน) ในแต่ละวัน จนกว่าจะครบทั้งเดือน(ซึ่งแต่ละเดือน วันทำงานจะไม่เท่ากัน) โดยที่ไม่ต้องมานั่งกรอกสูตรเองทีละช่องเหมือนที่ผมทำมานี้ ต้องทำอย่างไรครับอาจารย์

2.จากตัวอย่าง ที่ช่อง H19 และ H27 (สีเหลือง)จะเกิด error เพราะไม่มีการลงเวลาเข้าทำงาน ทำให้ผลรวมผิดพลาด (ซึ่งผลรวมที่ถูกต้องคือ 15:10 นาที) ไม่ทราบว่าจะแก้ไขอย่างไรดีครับ แล้วถ้าจะให้ออกมาเป็น 00:00 จะง่ายต่อการคำนวณไหมครับ

3.ที่ช่อง H32 อยากให้แสดงเป็น hh:mm เช่น 31:10 นาที (สามสิบเอ็ดชั่วโมงสิบนาที) ต้องกำหนดอย่างไรครับ

4.ถ้าช่อง I ผมต้องการให้เป็นโอทีเย็น หมายความว่า (เวลาที่ออกงาน-18:00) ในแต่ละวัน จนกว่าจะครบทั้งเดือน(ซึ่งแต่ละเดือน วันทำงานจะไม่เท่ากัน) โดยที่ไม่ต้องมานั่งกรอกเองทีละช่องเหมือนที่ผมทำมานี้ ต้องทำอย่างไรครับอาจารย์

5.จากตัวอย่าง ที่ช่อง I15 , I19 และI27 (สีแดง)จะเกิด error เพราะไม่มีการลงเวลาออกงาน ทำให้ผลรวมผิดพลาด ไม่ทราบว่าจะแก้ไขอย่างไรดีครับ

6.แล้วถ้าผมต้องการก็อปสูตรในช่อง HและI ไปวางใน ไฟล์exelของพนักงานอื่นๆ(เพราะ 1 คน ต้อง export จากโปรแกรมแสกนลายนิ้วมือ 1 ไฟล์) ต้องก็อปสูตรไปวางอย่างไรครับ ผมลองก็อปแล้ว จะได้แต่ผลลัพธ์ของไฟล์ต้นฉบับไปครับ ก็อปสูตรไปไม่ได้ครับ

ขอบพระคุณอาจารย์มากครับ

[run]

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

กฎการใช้บอร์ด
2.ตั้งชื่อกระทู้ให้สื่อถึงปัญหาที่จะถาม ไม่ตั้งชื่อว่า ช่วยด้วยครับ, มีปัญหามาปรึกษาครับ เป็นต้น

[snasui]

1.ถ้าช่อง H ผมต้องการให้เป็นโอทีเช้า หมายความว่า (08:00-เวลาที่เข้างาน) ในแต่ละวัน จนกว่าจะครบทั้งเดือน(ซึ่งแต่ละเดือน วันทำงานจะไม่เท่ากัน) โดยที่ไม่ต้องมานั่งกรอกสูตรเองทีละช่องเหมือนที่ผมทำมานี้ ต้องทำอย่างไรครับอาจารย์

หากไม่ใช้สูตรก็ต้องใช้ VBA ซึ่งต้องเป็นไปตามกฎการใช้บอร์ดข้อ 5 ด้านบน ดังนั้น หากใช้สูตรได้ให้เลือกใช้สูตรครับ
สูตรที่ H2 ควรจะคีย์เป็น

=$H$1-D2

สูตรที่ I2 ควรจะคีย์เป็น

=E2-$I$1

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

2.จากตัวอย่าง ที่ช่อง H19 และ H27 (สีเหลือง)จะเกิด error เพราะไม่มีการลงเวลาเข้าทำงาน ทำให้ผลรวมผิดพลาด (ซึ่งผลรวมที่ถูกต้องคือ 15:10 นาที) ไม่ทราบว่าจะแก้ไขอย่างไรดีครับ แล้วถ้าจะให้ออกมาเป็น 00:00 จะง่ายต่อการคำนวณไหมครับ

เราสามารถใช้ฟังก์ชัน If มาดักค่าที่ไม่ได้คีย์ได้ครับ ที่ H2 คีย์สูตร

=IF(D2="",0,$H$1-D2)

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

หมายความว่า หาก D2 เป็นค่าว่างให้แสดงค่า 0 หากไม่เป็นค่าว่างให้นำ $H$1-D2 การทำให้เป็น 0 ก็จะทำให้การคำนวณถูกต้องครับ

3.ที่ช่อง H32 อยากให้แสดงเป็น hh:mm เช่น 31:10 นาที (สามสิบเอ็ดชั่วโมงสิบนาที) ต้องกำหนดอย่างไรครับ

ให้คลิกขวาที่ H32 จากนั้นเลือก Format Cells... > เลือก Custom > ที่ช่อง Type: คีย์ [h]:mm > OK

4.ถ้าช่อง I ผมต้องการให้เป็นโอทีเย็น หมายความว่า (เวลาที่ออกงาน-18:00) ในแต่ละวัน จนกว่าจะครบทั้งเดือน(ซึ่งแต่ละเดือน วันทำงานจะไม่เท่ากัน) โดยที่ไม่ต้องมานั่งกรอกเองทีละช่องเหมือนที่ผมทำมานี้ ต้องทำอย่างไรครับอาจารย์

ดูคำตอบในข้อ 2 ครับ

5.จากตัวอย่าง ที่ช่อง I15 , I19 และI27 (สีแดง)จะเกิด error เพราะไม่มีการลงเวลาออกงาน ทำให้ผลรวมผิดพลาด ไม่ทราบว่าจะแก้ไขอย่างไรดีครับ

ปรับสูตรที่ I2 เป็น

=IF(E2="",0,E2-$I$1)

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

6.แล้วถ้าผมต้องการก็อปสูตรในช่อง HและI ไปวางใน ไฟล์exelของพนักงานอื่นๆ(เพราะ 1 คน ต้อง export จากโปรแกรมแสกนลายนิ้วมือ 1 ไฟล์) ต้องก็อปสูตรไปวางอย่างไรครับ ผมลองก็อปแล้ว จะได้แต่ผลลัพธ์ของไฟล์ต้นฉบับไปครับ ก็อปสูตรไปไม่ได้ครับ

การ Copy สูตรไม่น่าจะมีปัญหาใด ลอง Copy ไปใช้งานดูก่อน ติดตรงไหนค่อยมาถามกันต่อครับ

[run]

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

[run]

กราบสวัสดีปีใหม่อาจารย์ครับ
หลังจากที่ได้รับความช่วยเหลือจากอาจารย์เมื่อปีที่แล้ว พอเดือนถัดๆมา ก็พบปัญหาในตัว exel ที่ใช้อยู่ครับ
กล่าวคือ
ข้อ1.ถ้ามาลงเวลาเข้างานสาย (เกิน 08:00) ที่ช่องD ผลลัพธ์ในการคำนวนจะผิดพลาดครับอาจารย์
ข้อ2.ถ้าลงเวลาออกงานก่อนเวลาที่กำหนด (ก่อน 18:00) ที่ช่องE ผลลัพธ์ในการคำนวนจะผิดพลาดเช่นกันครับอาจารย์

ปล.ลองพยายามแก้เองแล้วครับ เช่นการมาสาย จากสูตรเดิม =IF(D2="",0,$H$1-D2) เป็น =IF(D2="",0,D2-$H$1) ซึ่งผลลัพธ์ในกรณีมาสายหน่ะออกมาถูกต้องครับ แต่ถ้ามาก่อน08:00 จะ error ครับ เลยต้องวนกลับมาขอความช่วยเหลือจากอาจารย์อีกรอบละครับ
ขอบคุณครับ

[snasui]

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

1. เซลล์ H2 คีย์สูตร
   =MAX(0,$H$1-D2)
   Enter
2. เซลล์ I2 คีย์สูตร
   =MAX(0,E2-$I$1)
   Enter
3. Copy H2:I2 ลงด้านล่าง

[run]

สวัสดีครับอาจารย์
ยังคอยติดตามผลงานและกด Like ท่านอาจารย์ตามประสาศิษย์ที่ดีอยู่เสมอครับ
สืบเนื่องจากปัญหาด้านบน หลังจากลองแก้สูตรตามที่ท่านอาจารย์ว่าแล้ว ยังคงเจอปัญหาอยู่ครับอาจารย์ กล่าวคือ

1.วันที่ไม่ได้ลงเวลา ช่อง H จะคำนวนผิด กลายเป็น 08:00 ซึ่งที่ถูกต้อง ต้องแสดง 00:00 ครับ

2.ในวันที่กลับก่อนเวลา เช่นช่อง I12 ที่ถูกต้อง ต้องแสดงว่า 00:57 ครับ (ออกก่อนเวลา 57 นาทีครับ)

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

[bank9597]

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

ที่ H2 คีย์ =IF(D2="","00:00"*1,MAX(0,$H$1-D2)) คัดลอกลงมา
ที่ I2 คีย์ =IF(E2="","00:00"*1,IF(E2*1<$I$1,$I$1-E2,MAX(0,E2-$I$1))) คัดลอกลงมา

[run]

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

ที่ H2 คีย์ =IF(D2="","00:00"*1,MAX(0,$H$1-D2)) คัดลอกลงมา
ที่ I2 คีย์ =IF(E2="","00:00"*1,IF(E2*1<$I$1,$I$1-E2,MAX(0,E2-$I$1))) คัดลอกลงมา

ขอบพระคุณท่าน bank9597 มากครับ
แต่พอลองเอามาใช้งานแล้ว ที่ช่อง I12 จะคำนวนผิดพลาดครับ ผมเลยเลองนั่งไล่สูตรอีกรอบ สรุปคือต้องแก้ตรงนี้ครับ
เนื่องจากช่อง E12 เป็นการออกก่อนเวลา 18:00 จึงไม่ได้คำนวนค่าล่วงเวลา ผมเลยลองแก้สูตรดังนี้ครับ
ที่ I12 คีย์ =IF(E12="","00:00"*1,IF(E12*1<$I$1,"00:00"*1,MAX(0,E12-$I$1)))
หมายความว่า ที่ช่อง I12 ถ้าช่อง E12 ว่าง ให้แสดงค่าของผลคูณ 00:00*1 (นั่นก็คือ 0 นั่นเอง) แต่ถ้าไม่ว่าง ให้เข้าสู่ตร IF ต่อไปคือ
ถ้าช่อง E12*1 (คือค่าของ E12 นั่นแหละ) น้อยกว่า ช่อง I1 ให้แสดงค่าของผลคูณ 00:00*1 (นั่นก็คือ 0 นั่นเอง) แต่ถ้าE12*1ไม่น้อยกว่า I1 ให้เปรียบเทียบและหาแสดงค่าที่มากที่สุดระหว่าง 0 และ ช่อง E12-I1

ตอนนี้ผลออกมา ใช้งานได้ถูกต้องแล้วครับ ผมขอขอบพระคุณท่านอาจารย์ที่มอบweb ที่แสนดีนี้แก่ชาวโลก
ขอบคุณท่าน Bank9597 ที่ช่วยชี้ทางสว่างที่ปลายอุโมงค์ให้ครับ
ขอบพระคุณจากใจจริงครับ

[bank9597]

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

ที่ H2 คีย์ =IF(D2="","00:00"*1,MAX(0,$H$1-D2)) คัดลอกลงมา
ที่ I2 คีย์ =IF(E2="","00:00"*1,IF(E2*1<$I$1,$I$1-E2,MAX(0,E2-$I$1))) คัดลอกลงมา

ขอบพระคุณท่าน bank9597 มากครับ
แต่พอลองเอามาใช้งานแล้ว ที่ช่อง I12 จะคำนวนผิดพลาดครับ ผมเลยเลองนั่งไล่สูตรอีกรอบ สรุปคือต้องแก้ตรงนี้ครับ
เนื่องจากช่อง E12 เป็นการออกก่อนเวลา 18:00 จึงไม่ได้คำนวนค่าล่วงเวลา ผมเลยลองแก้สูตรดังนี้ครับ
ที่ I12 คีย์ =IF(E12="","00:00"*1,IF(E12*1<$I$1,"00:00"*1,MAX(0,E12-$I$1)))
หมายความว่า ที่ช่อง I12 ถ้าช่อง E12 ว่าง ให้แสดงค่าของผลคูณ 00:00*1 (นั่นก็คือ 0 นั่นเอง) แต่ถ้าไม่ว่าง ให้เข้าสู่ตร IF ต่อไปคือ
ถ้าช่อง E12*1 (คือค่าของ E12 นั่นแหละ) น้อยกว่า ช่อง I1 ให้แสดงค่าของผลคูณ 00:00*1 (นั่นก็คือ 0 นั่นเอง) แต่ถ้าE12*1ไม่น้อยกว่า I1 ให้เปรียบเทียบและหาแสดงค่าที่มากที่สุดระหว่าง 0 และ ช่อง E12-I1

ตอนนี้ผลออกมา ใช้งานได้ถูกต้องแล้วครับ ผมขอขอบพระคุณท่านอาจารย์ที่มอบweb ที่แสนดีนี้แก่ชาวโลก
ขอบคุณท่าน Bank9597 ที่ช่วยชี้ทางสว่างที่ปลายอุโมงค์ให้ครับ
ขอบพระคุณจากใจจริงครับ

อ.คนควนและผม แสดงความยินดีด้วยครับ สามารถอธิบายสูตรได้ นับว่าเก่งแล้วครับ