รบกวนช่วยเรื่องสูตรการหักเงินมาสายกับ OT หน่อยครับ

[anzacmiraio]

สวัสดีครับ ไม่เก่ง excel น่ะครับ พยายามศึกษาสูตรเองแล้วก็ยังทำไม่ได้ครับ เลยขอรบกวนด้วยครับ

ที่บริษัทออกระเบียบมาว่า ใครที่มาสายเกิน 15 นาที จะโดนหักเงินนาทีละ 1 บาท แต่ถ้า มาสายเกิน 30 นาทีจะโดนหักเงินนาทีละ 5 บาทครับ

เวลาเข้างานคือ 10.00 ดังนั้น ถ้ามาเข้างานเกิน 10.15 ไป ก็จะโดน นาทีละ 1 บาท แต่ถ้า มาเข้างานเกิน 10.30 จะโดนหักนาทีละ 5 บาทครับ

ส่วน OT ก็จ่ายชั่วโมงละ 60 บาทครับ เริ่มนับตั้งแต่ 19.30 แต่จะไม่จ่ายเกิน 4 ชั่วโมงซึ่งก็คือจะไม่จ่ายหลัง 23.00 ครับ

รบกวนด้วยนะครับ ขอบคุณมากครับ

[snasui]

ปรับ Format ของคอลัมน์ H:I ให้เป็นแบบอื่นที่ไม่ใช่ [m] เช่นปรับเป็น General จากนั้นคีย์สูตรตามด้านล่างครับ

1. F9 คิดเวลามาสาย
   =MAX(0,B9-"10:15")
   Enter
2. G9 คิดเวลา OT
   =MIN("23:00"+0,E9)-MAX(D9,"19:30"+0)
   Enter
3. H9 จำนวนเงินมาสาย
   =IF(F9<="0:15"+0,1,5)*TEXT(F9,"[m]")
   Enter
4. I9 จำนวนเงิน OT
   =TEXT(G9,"[h]")*$I$3
   Enter

[anzacmiraio]

ขอบคุณมากเลยครับ

ขอรบกวนถามเป็นความรู้หน่อยครับว่าตรง

H9 จำนวนเงินมาสาย
=IF(F9<="0:15"+0,1,5)*TEXT(F9,"[m]")

ค่าอันนี้เนี่ยสูตรตรงนี้ผมไม่เข้าใจว่ามันมีที่มายังไง เลยอยากขอรบกวนอธิบายให้ฟังเป็นความรู้หน่อยครับ แล้วถ้าสมมุติว่าผมจะ เปลี่ยนเงื่อนไข เป็น สายเกิน 30 นาที(หลัง10.30) หัก 1 บาทต่อนาที และสายเกิน 45 นาที (หลัง 10.45) หัก 3 บาท นี่ผมตรงแก้ไขตรงไหนยังไงบ้างหรือครับ

ขอบคุณมากครับ

[jelelite]

จากสูตรของอาจารย์เปลี่ยนตัวเลขในสูตรของอาจารย์ตามนี้ครับ

1. F9 คิดเวลามาสาย
=MAX(0,B9-"10:30")
2. H9 จำนวนเงินมาสาย
=IF(F9<="0:15"+0,1,3)*TEXT(F9,"[m]")

[snasui]

=IF(F9<="0:15"+0,1,5)*TEXT(F9,"[m]")

คำอธิบายตามด้านล่างครับ

"0:15" คือ 15 นาทีอยู่ในรูปแบบ Text
"0:15"+0 คือ 15 นาทีในรูปแบบของเวลา เนื่องจากนำ Text ที่เป็นตัวเลขเวลาไปบวกด้วย 0 ก็จะกลายเป็นตัวเลข (Number) ในรูปแบบเวลา

TEXT(F9,"[m]") คือตัวเลขนาทีในรูปแบบ Text ของ F9

สำหรับการแปลง Text ให้เป็น Number ดูเพิ่มเติมที่นี่ครับ wordpress/change-text-to-number/

[anzacmiraio]

ขอบคุณมากๆเลยครับ ผมสับสนในช่อง เวลา ot น่ะครับ ถ้าเกิดว่า ไม่มีการเข้างาน ot เลยเนี่ย (ไม่ได้ตอกบัตรเข้า ot) ค่าเวลา ot มันจะกลายเป็น 240 เลยน่ะครับ ไม่ทราบว่าตรงนี้จะแก้ไขได้อย่างไรครับ?

[snasui]

ใช้ If เข้าไปดักได้ครับ เช่นด้านล่างเป็นต้น

=If(E9=0,0,MIN("23:00"+0,E9)-MAX(D9,"19:30"+0))

[anzacmiraio]

ขอบคุณมากเลยครับ ยังมี งงๆ อยู่บ้างแต่จะไปศึกษาเพิ่มเติมนะครับ ^^

[anzacmiraio]

รบกวนถามอีกเรื่องครับ คือตอนนี้อยากเปลี่ยนเงื่อนไขของการจ่าย OT น่ะครับ เป็นทุกๆ 30 นาทีจ่าย 30 บาท น่ะครับ ไม่ทราบว่าต้องแก้ไขตรงจุดไหนหรือครับ ผมได้แนบไฟล์มาให้รบกวนช่วยดูด้วยครับ ขอบคุณมากครับ

[DhitiBank]

ที่ I9 ลองคีย์

=INT(TEXT(G9,"[m]")/30)*$I$4/2

Enter --> คัดลอกลงล่างครับ

[anzacmiraio]

ขอบคุณมากๆเลยครับผม ได้ความรู้ + ไอเดียมาเยอะเลยครับ ^^

[anzacmiraio]

รบกวนถามอีกทีครับ ตรง I40 ถ้าอยากให้ผลรวมไม่เกิน 1800 นี่ต้องใส่สูตรอะไรหรือครับ คืออยากให้ค่า OT ที่ต้องจ่ายในแต่ละเดือนรวมแล้ว ไม่เกิน 1800 บาทน่ะครับ

[jelelite]

ที่ I40 พิมพ์ตัวอักษรสีแดงเพิ่มตามนี้ครับ

=MIN(1800,SUM(I9:I39))