สูตรการคำนวนเวลาทำงาน นับจำนวนนาทีที่สายลงในช่องที่กำหนด

[sopasiri]

อ้างอิงจากกระทู้เก่าที่หายไป
ปัญหาคือสูตร ไม่นับนาทีรวมจริง สมมุติ ลงเวลา 10.32 แต่สูตรเอาค่าเวลาสายมาแค่ 32 นาที จริงๆที่ถูกต้องคือ 152 นาที (8.00ลบ10.32)
รบกวนช่วยดูว่าต้องปรับแก้อย่างไร ขอบคุณค่ะ

ในไฟล์ที่แนบมีตัวอย่างและสูตรพร้อมทั้งค่าที่ถูกต้อง

[snasui]

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

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

=LOOKUP(9.99999999999999E+307,CHOOSE({1,2},0,TEXT(INDEX(TIME!$B$2:$B$65,SMALL(IF(TIME!$E$2:$E$65="Clock In",IF(TIME!$C$2:$C$65=$B8,IF(TIME!$B$2:$B$65>"8:31"+0,ROW(TIME!$A$2:$A$65)-ROW(TIME!$A$2)+1))),COUNT($E$5:E$5)))-"8:00","[mm]")+0))

Ctrl+Shift+Enter

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

=LOOKUP(9.99999999999999E+307,CHOOSE({1,2},0,TEXT(INDEX(TIME!$F$2:$F$65,SMALL(IF(TIME!$E$2:$E$65="Clock In",IF(TIME!$C$2:$C$65=$B8,IF(TIME!$B$2:$B$65>"8:31"+0,ROW(TIME!$A$2:$A$65)-ROW(TIME!$A$2)+1))),COUNT($E$5:E$5))),"[mm]")+0))

Ctrl+Shift+Enter

[sopasiri]

ขอเปลี่ยนเงื่อนไขใหม่ดังนี้จะทำได้มั้ย ขออภัยที่เปลี่ยนเงื่อนไข จนตัวเองก็ งงๆ เหมือนกัน

ล่าสุดดังนี้..ต้องการเช็คมาสายตามเงื่อนไขที่กำหนด เช็คจากการลงเวลาทั้งหมดกรณีสายแต่ไม่เกิน 8.31น.

คือตั้งแต่ 8.01-8.30น.เท่านั้นแล้วนำจำนวนนาทีที่คำนวนได้ลงในช่อง1-3 ชุดแรก

แต่ถ้าเกิน 8.31น. ให้ นำจำนวนนาทีที่คำนวนได้ลงในช่อง 1-31 ตามลำดับ

มีตัวอย่างในไฟล์ที่แนบมาพร้อมคำตอบที่ถูกต้อง

[snasui]

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

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

[sopasiri]

ลองปรับดังนี้

LOOKUP(9.99999999999999E+307,CHOOSE({1,2},0,TEXT(INDEX(TIME!$B$2:$B$65,SMALL(IF(TIME!$E$2:$E$65="Clock In",IF(TIME!$C$2:$C$65=$B8,IF(TIME!$B$2:$B$65<"8:31"+0,ROW(TIME!$A$2:$A$65)-ROW(TIME!$A$2)+1))),COUNT($E$5:G$5)))-"8:00","[mm]")+0))


แต่ทำไม เวลาที่อยู่ในช่วง 8.00-8.31 (8.12 กลับข้ามไปไม่เช็ค)

[snasui]

ที่ถามว่าทำไม คำตอบคือสูตรไม่ถูกต้องครับ

จากที่เขียนมาเป็นการตรวจสอบเงื่อนไขเวลาที่น้อยกว่า 8:31 ทั้งหมด ถ้าต้องการเวลา 8:00-8:31 จะต้องเพิ่มเงื่อนไขเข้าไปอีกเป็นเช่นด้านล่าง

LOOKUP(9.99999999999999E+307,CHOOSE({1,2},0,TEXT(INDEX(TIME!$B$2:$B$65,SMALL(IF(TIME!$E$2:$E$65="Clock In",IF(TIME!$C$2:$C$65=$B8,If(TIME!$B$2:$B$65>"8:30"+0,IF(TIME!$B$2:$B$65<"8:31"+0,ROW(TIME!$A$2:$A$65)-ROW(TIME!$A$2)+1)))),COUNT($E$5:G$5)))-"8:00","[mm]")+0))

[sopasiri]

ได้ตรงที่ต้องการแล้วค่ะ

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

ขอบคุณมากค่ะ

[sopasiri]

อ้างถึงสตูร เซลส์ J7 การนับจำนวนครั้งที่มาสายและคำนวนผลรวมนาทีที่สาย กระทู้เก่าหาย
=SUM(IF(TIME!$E$2:$E$1232="Clock In",IF(TIME!$C$2:$C$1232=$D7,IF(TIME!$B$2:$B$1232<="8:30"+0,MINUTE(TIME!$B$2:$B$1232)))))

จากสูตรไม่ได้เช็คจากเวลาจริง เช่นลงเวลา 10:32 จะข้ามไปไม่นับ จริงแล้วตัองนับว่ามาสาย และ คำนวนเวลารวมออกมาคือ 2.32ชม(152 นาทีที่สาย)

อย่างนี้ต้องปรับอย่างไร

[snasui]

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

ที่เซลล์ J7 คีย์สูตร

=SUM(IF(TIME!$E$2:$E$50="Clock In",IF(TIME!$C$2:$C$50=$D7,IF(TIME!$B$2:$B$50>"8:31"+0,TEXT(TIME!$B$2:$B$50-"8:00","[mm]")+0))))

Ctrl+Shift+Enter

[treepam]

มีเรื่องอยากให้ช่วยเรื่องเดียวกันเลยแต่คนละแบบค่ะ คือ เวลาเข้างาน 07.30 อยาก เช็ตสายกี่นาทีค่ะ เช่น เวลาเข้า 7.30 มา 7.34 อยากให้แสดงว่า สาย 4 นาทีค่ะ

[bank9597]

เนื่องจากคุณ treepam ไม่ได้บอกว่าจะให้แสดงในช่องไหน อย่างไร

ผมเลยทำแบบง่ายๆมาให้ครับ

ที่ G1 คีย์ คำว่า สาย/นาที
ที่ G2 คีย์ =IF(D2="","",IF(D2<"07:30","",IF(D2>"07:30",D2-"07:30",""))) คัดลอกลงมาครับ

[treepam]

เรียนคุณ Gold พอดีเข้าไปลงแล้วมัน ได้คำตอบไม่ตรงค่ะ 7.48 มันจะสาย 18 นาที แต่มันออกว่า 0.0125

[bank9597]

ผมชื่อ bank9597 ครับ (เคือง อิอิ)

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

คลิ๊กที่หัวคอลัมน์ G แล้วไปจัดรูแบบข้อมูลเป็น "เวลา" ก็จะได้ผลลัพธ์ที่ต้องการ

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

[treepam]

ขอบคุณมากค่ะ พระคุณอย่างสูง bank9597 ครับ