สูตรรวม-ลบวันที่เเละเวลาไม่ตรง

[manager]

เงื่อนไขที่ 1 R2
ให้สูตรบอกแบบเตือนเมื่อไม่เป็นไปตามความจริง ตอนกรอกลงเวลาและวันที่ผิด แต่ในความคิดผมเท่าที่มีจะเป็นสูตรได้ไหม? จึงถามมาก่อน เพราะถ้าเป็นในวันเดียวกัน คำตอบ 15:28:00 เป็นคำตอบที่ผิด
เงื่อนไขที่ 2 R2
คำตอบผิด(31:52:00) ที่ถูกคือ 16:20:00
ความหมายคือเครื่องหยุดเดินวันที่13/8/2013 เวลา 9:20:00 - ซ่อมจบ วันที่ 15/8/2013 เวลา9:00:00 หักเวลาที่ไม่เกี่ยวกับเวลาทำงานแล้ว
จากตารางด้างล่างดังนี้
วันที่13 เหลือ 7 ช.ม.
วันที่14 เหลือ 8.2 ช.ม.
วันที่15 เหลือ 1 ช.ม.
รวมเวลาเครื่องหยุดเดินทั้งหมด 16 ช.ม. 20 นาที(นับจากเวลาทำงานจริง)
ขอบคุณมากครับ

[bank9597]

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

ที่ S2 คีย์ =IF(($M2+$L2)-($J2+$K2)<0,"ผิด",SUM("16:20:00"-(K2),"08:20:00"-("16:20:00"-(L2)),"08:20:00"*DAY(($M2+$L2)-($J2+$K2)))) คัดลอกลงมา

[manager]

1.ผมขอเปลี่ยนคำว่า"ผิด"เป็นค่าว่างๆครับ
2. S:S ต้องขอโทษครับที่ให้โจษน้อยเลยได้คำตอบที่ไม่ถูก
ขอโทษครับ

[cruzz]

1.ผมเก็บข้อมูลแบบนี้ครับ 5/12/2013 8:00:00 กับ 5/12/2013 12:00:00
ที่ผลลัพท์นั้นผมสั่งลบกันธรรมดา เช่น A2-A1 แต่ปรับแต่งที่รูปแบบเซลล์ครับ
ให้แสดงจำนวน วัน ชั่วโมง และนาที เท่าที่ทำมามันคำนวณถูกครับ

2.ใช้ =Datedif(วันที่เริ่ม, วันที่สิ้นสุด, รูปแบบ) แต่อันนี้ผมไม่แน่ใจเรื่องเวลา
ลองอ่านเพิ่มเติมได้ที่ http://www.snasui.com/viewtopic.php?f=3&t=352

[bank9597]

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

ที่ s2 คีย์ =IF(($M2+$L2)-($J2+$K2)<0,"",IF(INT(($M2+$L2)-($J2+$K2))=0,$L2-$K2,SUM("16:20:00"-(K2),"08:20:00"-("16:20:00"-(L2)),"08:20:00"*DAY(($M2+$L2)-($J2+$K2))))) คัดลอกลงมา

[manager]

1.แปลกครับ หากกรอกเพิ่มอีก1ชั่วโมงใน L19 จะต้องเป็น 17:20:00 ครับ เเต่นี่เป็น 25:40:00 ผิดครับ
(เวลาจะกรอกตามจริงและเวลา-วันที่จะเปลี่ยนตลอดครับ)

[bank9597]

1.แปลกครับ หากกรอกเพิ่มอีก1ชั่วโมงใน L19 จะต้องเป็น 17:20:00 ครับ เเต่นี่เป็น 25:40:00 ผิดครับ
(เวลาจะกรอกตามจริงและเวลา-วันที่จะเปลี่ยนตลอดครับ)

ที่ S2 คีย์ IF(($M2+$L2)-($J2+$K2)<0,"",IF(INT(($M2+$L2)-($J2+$K2))=0,$L2-$K2,SUM("16:20:00"-(K2),"08:20:00"-("16:20:00"-(L2)),"08:20:00"*IF((DAY(($M2+$L2)-($J2+$K2))-1)=0,1,(DAY(($M2+$L2)-($J2+$K2))-1))))) คัดลอกลงมาครับ

[manager]

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

[bank9597]

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

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

ที่ S2 คีย์ =IF(OR((($M2+$L2)-($J2+$K2))<0,COUNT($J2:$M2)=0),"",IF(INT(($M2+$L2)-($J2+$K2))=0,$L2-$K2,SUM("16:20:00"-($K2),"08:20:00"-("16:20:00"-($L2)),"08:20:00"*(IF((($M2)-($J2))=0,0,(($M2)-($J2))-1))))) คัดลอกลงมา


ผมปรับไฟล์ใหม่มาให้ด้วยครับ ลองดูทั้งสองไฟล์

[manager]

ขอนับถือ
ขอบพระคุณยิ่ง
ขอบคุณมากครับ

[manager]

ผลลองแบบแยกคอลัมท์ออกมาช่วง COUNT($B25:$K25) ทำมาใหม่เจอปัญหาครับ
ไม่ทราบว่าเป็น ##### ได้อย่างไร ที่ Q25 แต่ลากลงไปก็ไม่เป็นอะไร
ผมลองเปลี่ยน ที่ B25 เป็น 4/8/2013 = Q25 8:00:00
ขอบคุณครับ

[bank9597]

เนื่องจากที่ K25 คุณได้คีย์เวลาไว้ 07:30 น. ซึ่งเป็นการเข้าก่อนเวลา ทำให้ไม่เข้าเงื่อนไขที่คุณได้ตั้งไว้ สูตรจึงทำงานผิดพลาดครับ

คุณต้องแก้ปัญหาเรื่องเงื่อนมาก่อนครับ ว่าตกลงแล้วการคีย์ข้อมูลนั้น มีโอกาสเป็นแบบไหนบ้าง

[bank9597]

ผมลองคิดใหม่โดยเอาแต่เวลาทำการเท่านั้น คือ 8.00น. ถึง 17.00น. หักออก 40 นาที เหลือ 8.20 นาที/วัน

เนื่องจากคุณใช้ Excel2003 ไม่สามารถซ้อน IF ได้เกิน 7 ชั้น ผมเลยคิดสูตรแบบรวมและแยกสูตร ลองนำไปใช้ดูก่อนครับว่า จะใช้ได้ไหม

ที่ Q24 คีย์

Code: Select all

=IF(OR((($J24+$K24)-($B24+$C24))<0,COUNT($B24:$C24,$J24:$K24)=0),"",IF($B24=$J24,(IF(AND($K24>="12:00:00"+0,$K24<="12:40:00"+0),"12:00:00",IF($K24<"12:00:00"+0,$K24,$K24-"00:40:00"))-IF(AND($C24>="12:00:00"+0,$C24<="12:40:00"+0),"12:00:00",IF($C24<"12:00:00"+0,$C24,$C24-"00:40:00"))),SUM(IF(AND($C24>="08:00:00"+0,$C24<="12:00:00"+0),("12:00:00"-$C24)+"04:20:00",IF(AND($C24>="12:40:00"+0,$C24<="17:00:00"+0),"17:00:00"-$C24,IF(AND($C24>"12:00:00"+0,$C24<"12:40:00"+0),"04:20:00"+0,0))),IF(AND($K24>="08:00:00"+0,$K24<="12:00:00"+0),$K24-"08:00:00",IF(AND($K24>="12:40:00"+0,$K24<="17:00:00"+0),($K24-"12:40:00")+"04:00:00",IF(AND($K24>"12:00:00"+0,$K24<"12:40:00"+0),"04:00:00"+0,0))),"08:20:00"*(IF((($J24)-($B24))=0,0,(($J24)-($B24))-1)))))

คัดลอกลงมา

ส่วนวิธีที่ 2 ลองดูตามแนบครับ

[manager]

ขอบคุณมากเป็นอย่างยิ่ง
ขอคารวะ