Page 1 of 1
ขอสูตรหาเวลาที่รอส่งสินค้าเกิน 4 ชม.
Posted: Thu Sep 13, 2018 10:59 am
by ILoveExcel
เรียน อาจารย์และผู้รู้ทุกท่านครับ
ผมมีเรื่องอยากรบกวนขอสูตรที่ใช้ในการคำนวณหาเวลาที่ต้องรอในการส่งสินค้าแต่ละวัน โดยเฉพาะกรณีที่เกิน 4 ชม. แรก
ให้แสดงคำตอบว่าเกินไปกี่ชั่วโมงครับ
หมายเหตุ: รายละเอียดตามเอกสารแนบครับ
ขอบคุณมากครับ
Re: ขอสูตรหาเวลาที่รอส่งสินค้าเกิน 4 ชม.
Posted: Thu Sep 13, 2018 11:53 am
by PayomKanboon
ที่ C2 =IF(A2>B2,1+B2,B2)-A2 Copy ลงล่าง
ที่ D2 =IF(C11<TIME(4,,),0,TEXT(C11-$G$1,"h.m"))&" ชั่วโมง" Copy ลงล่าง
Re: ขอสูตรหาเวลาที่รอส่งสินค้าเกิน 4 ชม.
Posted: Thu Sep 13, 2018 11:58 am
by PayomKanboon
ที่ D2 แก้ไขเป็น
=IF(C12<TIME(4,,),0,TEXT(C12-TIME(4,,),"h.m"))&" ชั่วโมง" Copy ลงล่างครับ
Re: ขอสูตรหาเวลาที่รอส่งสินค้าเกิน 4 ชม.
Posted: Thu Sep 13, 2018 12:23 pm
by PayomKanboon
แก้ไขอีกทีครับ
ที่ D2 =IF(C2<TIME(4,,),0,TEXT(C2-TIME(4,,),"h.m"))&" ชั่วโมง" Copy ลงล่างครับ
Re: ขอสูตรหาเวลาที่รอส่งสินค้าเกิน 4 ชม.
Posted: Thu Sep 13, 2018 12:28 pm
by Bo_ry
C2 =MOD(1+B2-A2,1)
D2 =MAX(0,F2-"4:00")
Re: ขอสูตรหาเวลาที่รอส่งสินค้าเกิน 4 ชม.
Posted: Thu Sep 13, 2018 12:56 pm
by ILoveExcel
ขอบคุณคุณ PayomKanboon และคุณ Bo_ry มากๆ ครับ ทดลองแล้วใช้ได้ทั้ง 2 สูตรเลยครับ
Re: ขอสูตรหาเวลาที่รอส่งสินค้าเกิน 4 ชม.
Posted: Thu Sep 13, 2018 4:50 pm
by ILoveExcel
รบกวนสอบถามอีกครั้งครับ จากคำถามด้านบน
กรณีถ้าได้คำตอบเกิน :30 ให้ปัดขึ้นเป็นชั่วโมงเต็ม เช่น 4:31 เป็น 5:00 ชม.
แต่ถ้าน้อยกว่า :30 ให้ปัดลง เช่น 4:29 เป็น 4:00 ชม.ต้องเขียนสูตรว่าอย่างไรครับ
ขอบคุณครับ
Re: ขอสูตรหาเวลาที่รอส่งสินค้าเกิน 4 ชม.
Posted: Thu Sep 13, 2018 6:39 pm
by PayomKanboon
ที่ D2=MROUND(MAX(0,O2-"4:00"),1/"24")
Re: ขอสูตรหาเวลาที่รอส่งสินค้าเกิน 4 ชม.
Posted: Thu Sep 13, 2018 6:44 pm
by PayomKanboon
แก้ไขครับเป็น
ที่ D2=MROUND(MAX(0,C2-"4:00"),1/"24")
Re: ขอสูตรหาเวลาที่รอส่งสินค้าเกิน 4 ชม.
Posted: Thu Sep 13, 2018 6:54 pm
by snasui
ใช้ 1/24 ได้เลย ไม่ต้องครอบด้วยฟันหนูครับ
หรืออีกแบบ
=CEILING(MAX(0,C2-"4:00"),1/24)
Re: ขอสูตรหาเวลาที่รอส่งสินค้าเกิน 4 ชม.
Posted: Thu Sep 13, 2018 7:03 pm
by PayomKanboon
ขอบคุณครับอาจารย์
Re: ขอสูตรหาเวลาที่รอส่งสินค้าเกิน 4 ชม.
Posted: Thu Sep 13, 2018 7:35 pm
by ILoveExcel
PayomKanboon wrote: Thu Sep 13, 2018 6:44 pm
แก้ไขครับเป็น
ที่ D2=MROUND(MAX(0,C2-"4:00"),1/"24")
ขอบคุณครับ สูตรได้ผลตามที่ต้องการแล้วครับ
Re: ขอสูตรหาเวลาที่รอส่งสินค้าเกิน 4 ชม.
Posted: Mon Sep 17, 2018 8:49 am
by ILoveExcel
ขออนุญาตรบกวนอีกครั้งครับ จากสูตรเดิม D2=MROUND(MAX(0,C2-"4:00"),1/"24") ต้องการเปลี่ยนเงื่อนไขเป็น
1. ถ้าไม่เกิน :30 ให้ปัดเป็น :30 เช่น 4:10 ให้ปัดเป็น 4:30
2. ถ้าเกิน :30 ให้ปัดเป็นเต็มชั่วโมง เช่น 4:31 ให้ปัดเป็น 5:00
ต้องเขียนสูตรอย่างไรครับ
ขอบคุณมากครับ
Re: ขอสูตรหาเวลาที่รอส่งสินค้าเกิน 4 ชม.
Posted: Mon Sep 17, 2018 9:49 am
by Bo_ry
=CEILING(MAX(0,C2-"4:00"),"0:30")
Re: ขอสูตรหาเวลาที่รอส่งสินค้าเกิน 4 ชม.
Posted: Mon Sep 17, 2018 10:31 am
by ILoveExcel
Bo_ry wrote: Mon Sep 17, 2018 9:49 am
=CEILING(MAX(0,C2-"4:00"),"0:30")
เรียน คุณ Bo_ry
ผมได้ทดลองนำสูตรไปใช้แล้ว แต่พบว่ามีบางรายการที่ได้คำตอบไม่ถูกครับ (รายการที่ highlight สีเหลือง)
รบกวนคุณ Bo_ry ช่วยดูให้หน่อยครับ
ขอบคุณมากครับ
Re: ขอสูตรหาเวลาที่รอส่งสินค้าเกิน 4 ชม.
Posted: Mon Sep 17, 2018 11:03 am
by Bo_ry
เวลาบางทีลบกันมีเศษเหลือเป็นเสี้ยววินาที ใช้ Ceiling ก็จะปัดขึ้นให้
เลยใช้ MROUND(MAX(0,C2-"4:00"),"0:01") ปัดเศษวินาทีออกก่อน แล้วค่อยปัดขึ้น CEILING(…,"0:30")
=CEILING(MROUND(MAX(0,C2-"4:00"),"0:01"),"0:30")
Re: ขอสูตรหาเวลาที่รอส่งสินค้าเกิน 4 ชม.
Posted: Mon Sep 17, 2018 11:17 am
by ILoveExcel
ได้แล้วครับ ขอบคุณมากๆ เลยครับ