Page 1 of 1
สูตรรวม-ลบวันที่เเละเวลาไม่ตรง
Posted: Tue Sep 03, 2013 10:21 am
by 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 นาที(นับจากเวลาทำงานจริง)
ขอบคุณมากครับ
Re: สูตรรวม-ลบวันที่เเละเวลาไม่ตรง
Posted: Tue Sep 03, 2013 1:08 pm
by 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)))) คัดลอกลงมา
Re: สูตรรวม-ลบวันที่เเละเวลาไม่ตรง
Posted: Tue Sep 03, 2013 1:41 pm
by manager
1.ผมขอเปลี่ยนคำว่า"ผิด"เป็นค่าว่างๆครับ
2. S:S ต้องขอโทษครับที่ให้โจษน้อยเลยได้คำตอบที่ไม่ถูก
ขอโทษครับ
Re: สูตรรวม-ลบวันที่เเละเวลาไม่ตรง
Posted: Tue Sep 03, 2013 2:09 pm
by 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
Re: สูตรรวม-ลบวันที่เเละเวลาไม่ตรง
Posted: Tue Sep 03, 2013 2:13 pm
by 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))))) คัดลอกลงมา
Re: สูตรรวม-ลบวันที่เเละเวลาไม่ตรง
Posted: Tue Sep 03, 2013 4:43 pm
by manager
1.แปลกครับ หากกรอกเพิ่มอีก1ชั่วโมงใน L19 จะต้องเป็น 17:20:00 ครับ เเต่นี่เป็น 25:40:00 ผิดครับ
(เวลาจะกรอกตามจริงและเวลา-วันที่จะเปลี่ยนตลอดครับ)
Re: สูตรรวม-ลบวันที่เเละเวลาไม่ตรง
Posted: Tue Sep 03, 2013 7:02 pm
by bank9597
manager wrote: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))))) คัดลอกลงมาครับ
Re: สูตรรวม-ลบวันที่เเละเวลาไม่ตรง
Posted: Wed Sep 04, 2013 7:36 am
by manager
ผมลองเพิ่มเวลาและวันลงไปก็สามารถเป็นเวลาเครื่องหยุดเดินได้แล้ว แต่พอปรับให้ระยะห่างเป็นเพิ่มเดือนมากขึ้น คำตอบเวลาจะไม่ปรับตามเดือนที่ทิ้งระยะห่างให้แล้ว รู้สึกว่าสูตรจะได้แค่เดือนเดียว ใน S21
ขอปรับแก้เผื่อสูตรไปเลย 12 เดือนไม่ทราบว่าได้ไหมครับ
ขอบคุณครับ
Re: สูตรรวม-ลบวันที่เเละเวลาไม่ตรง
Posted: Wed Sep 04, 2013 12:15 pm
by bank9597
manager wrote:ผมลองเพิ่มเวลาและวันลงไปก็สามารถเป็นเวลาเครื่องหยุดเดินได้แล้ว แต่พอปรับให้ระยะห่างเป็นเพิ่มเดือนมากขึ้น คำตอบเวลาจะไม่ปรับตามเดือนที่ทิ้งระยะห่างให้แล้ว รู้สึกว่าสูตรจะได้แค่เดือนเดียว ใน 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))))) คัดลอกลงมา
ผมปรับไฟล์ใหม่มาให้ด้วยครับ ลองดูทั้งสองไฟล์
Re: สูตรรวม-ลบวันที่เเละเวลาไม่ตรง
Posted: Wed Sep 04, 2013 3:26 pm
by manager
ขอนับถือ
ขอบพระคุณยิ่ง
ขอบคุณมากครับ
Re: สูตรรวม-ลบวันที่เเละเวลาไม่ตรง
Posted: Wed Sep 04, 2013 6:02 pm
by manager
ผลลองแบบแยกคอลัมท์ออกมาช่วง COUNT($B25:$K25) ทำมาใหม่เจอปัญหาครับ
ไม่ทราบว่าเป็น ##### ได้อย่างไร ที่ Q25 แต่ลากลงไปก็ไม่เป็นอะไร
ผมลองเปลี่ยน ที่ B25 เป็น 4/8/2013 = Q25 8:00:00
ขอบคุณครับ
Re: สูตรรวม-ลบวันที่เเละเวลาไม่ตรง
Posted: Wed Sep 04, 2013 7:33 pm
by bank9597
เนื่องจากที่ K25 คุณได้คีย์เวลาไว้ 07:30 น. ซึ่งเป็นการเข้าก่อนเวลา ทำให้ไม่เข้าเงื่อนไขที่คุณได้ตั้งไว้ สูตรจึงทำงานผิดพลาดครับ
คุณต้องแก้ปัญหาเรื่องเงื่อนมาก่อนครับ ว่าตกลงแล้วการคีย์ข้อมูลนั้น มีโอกาสเป็นแบบไหนบ้าง
Re: สูตรรวม-ลบวันที่เเละเวลาไม่ตรง
Posted: Thu Sep 05, 2013 1:59 am
by 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 ลองดูตามแนบครับ
Re: สูตรรวม-ลบวันที่เเละเวลาไม่ตรง
Posted: Thu Sep 05, 2013 7:19 am
by manager
ขอบคุณมากเป็นอย่างยิ่ง
ขอคารวะ
