ฟังก์ชันทุกฟังก์ชันใน Excel สามารถหยิบจับมาใช้งานได้

แต่ไม่ใช่กับ DATEDIF ซึ่งนอกจากเป็นฟังก์ชันแห่งความลับแล้ว ยังถือเป็นฟังก์ชันต้องห้าม!

หลายคนรู้เท่าไม่ถึงการณ์ หยิบมาใช้โดยไม่ “คลายสะกด” ก็ต้องพบกับ “คำสาป” ทุกรายไป

บางรายถึงกับสูญสิ้นความไว้เนื้อเชื่อใจใน Excel เลยก็มี…

(แค่อ่าน ก็รู้สึกถึงความน่าสะพรึงกลัวแล้วใช่ไหมครับ ^^)

อิทธิฤทธิ์ “คำสาป” ที่ว่า ทำให้ผลลัพธ์การคำนวณผิด

ผิด เพราะได้ค่าไม่ตรงกับที่อยากให้เป็น และ/หรือ ดูไม่สมเหตุสมผล

(ใครไม่รู้ว่าฟังก์ชัน DATEDIF ใช้ทำอะไร แนะนำให้อ่านบทความเรื่อง Function แห่งความลับใน Excel ก่อนนะครับ ไม่งั้นตอนอ่านบทความนี้ คุณอาจโดนธาตุไฟเข้าแทรกได้)

เกิดขึ้นกับการใช้ รูปแบบของผลลัพธ์ เป็น md และเกิดกับบางกรณีท่านั้น!

คำถามคือ มันเกิดขึ้นกับกรณีไหนล่ะ?

เพื่อให้เข้าใจง่าย ขออธิบายด้วยการ ถาม-ตอบ แบบนี้ครับ

ถ้าถามว่า วันที่ 31 มกราคม 2016 กับวันที่ 31 พฤษภาคม 2016 ห่างกันกี่เดือน กี่วัน?

ทุกคนคงตอบว่า 4 เดือน (0 วัน) ใช่ไหมครับ

แต่ถ้าผมเปลี่ยนคำถามใหม่ เป็น วันที่ 31 มกราคม 2016 กับวันที่ 30 พฤษภาคม 2016 ห่างกันกี่เดือน กี่วัน?

คราวนี้เสียงจะเริ่มแตกเป็น 2 คำตอบ คือ

• 3 เดือน 30 วัน
• 3 เดือน 29 วัน

(คงไม่มีใครตอบ 3 เดือน 31 วันใช่ไหมครับ ^^)

เอ..แล้วคำตอบไหนจึงจะถูกล่ะ?

แล้วถ้าถามเอ็กเซลล่ะ เอ็กเซลจะคำนวณได้กี่วัน?

คำตอบคือ เอ็กเซลคำนวณได้ 3 เดือน 29 วัน!

ทำไมจึงเป็นเช่นนั้น?

ถ้าเฉลยตอนนี้จะไม่มัน ขอถามต่ออีก 2 คำถามก่อนนะครับ ^^

ถ้าถามว่า วันที่ 31 มกราคม 2016 กับวันที่ 30 สิงหาคม 2016 ห่างกันกี่เดือน กี่วัน?

คำตอบก็จะมี 2 คำตอบ เช่นกัน คือ

• 6 เดือน 30 วัน
• 6 เดือน 29 วัน

แล้วถ้าถามเอ็กเซลล่ะ เอ็กเซลจะคำนวณได้กี่วัน?

คำตอบคือ เอ็กเซลคำนวณได้ 6 เดือน 30 วัน!

จำนวน เดือน คงไม่ใช่ปัญหา ปัญหาคือจำนวน วัน

ถ้าถามใหม่เป็น วันที่ 31 มกราคม 2016 กับวันที่ 30 มีนาคม 2016 ห่างกันกี่เดือน กี่วัน?

คำตอบที่ได้ อาจมีถึง 4 คำตอบ คือ

• 1 เดือน 30 วัน
• 1 เดือน 29 วัน
• 1 เดือน 28 วัน
• 1 เดือน 27 วัน

แล้วถ้าถามเอ็กเซลล่ะ เอ็กเซลจะคำนวณได้กี่วัน?

คำตอบคือ 1 เดือน 28 วัน!

เริ่มจับ “หลัก” อะไรบางอย่างได้แล้วใช่ไหมครับ ^__^

เจ้า “หลัก” ที่ว่าคือ

เอ็กเซลจะกระโดดไปยังวันเดียวกันของเดือนสิ้นสุด แล้วเอาจำนวนวันที่แตกต่างกัน ระหว่าง วันเริ่มต้น และ วันสิ้นสุด หักออกจาก จำนวนวันของเดือนก่อนหน้าเดือนสิ้นสุด

กลับไปยังคำถาม ระยะห่างระหว่างวันที่ 31 มกราคม 2016 กับวันที่ 30 พฤษภาคม 2016

เอ็กเซลจะกระโดดไปยังวันเดียวกันของเดือนสิ้นสุด ซึ่งก็คือวันที่ 31 พฤษภาคม และถือว่าวันนั้นแตกต่างกัน (4 เดือน) 0 วัน

จำนวนวันที่แตกต่างกันของวันเริ่มต้น (31) และ วันสิ้นสุด (30) คือ 1

เดือนก่อนหน้าเดือนสิ้นสุด (พฤษภาคม) คือเดือน เมษายน ดังนั้น

จำนวนวันของเดือนก่อนหน้าเดือนสิ้นสุด = จำนวนวันของเดือนเมษายน = 30

จึงได้ จำนวนวัน เท่ากับ 29 (30-1)

กลับไปยังคำถามระยะห่างระหว่างวันที่ 31 มกราคม 2016 กับ วันที่ 30 สิงหาคม 2016

เอ็กเซลจะกระโดดไปยังวันเดียวกันของเดือนสิ้นสุด ซึ่งก็คือวันที่ 31 สิงหา และถือว่าวันนั้นแตกต่างกัน (7 เดือน) 0 วัน

จำนวนวันที่แตกต่างกันของวันเริ่มต้น (31) และ วันสิ้นสุด (30) คือ 1

เดือนก่อนหน้าเดือนสิ้นสุด (สิงหาคม) คือเดือน กรกฎาคม ดังนั้น

จำนวนวันของเดือนก่อนหน้าเดือนสิ้นสุด = จำนวนวันของเดือนกรกฎาคม = 31

จึงได้ จำนวนวัน เท่ากับ 30 (31-1)

กลับไปยังคำถามระยะห่างระหว่างวันที่ 31 มกราคม 2016 วันที่ 30 มีนาคม 2016

เอ็กเซลจะกระโดดไปยังวันเดียวกันของเดือนสิ้นสุด ซึ่งก็คือวันที่ 31 มีนาคม และถือว่าวันนั้นแตกต่างกัน (2 เดือน) 0 วัน

จำนวนวันที่แตกต่างกันของวันเริ่มต้น (31) และ วันสิ้นสุด (30) คือ 1

เดือนก่อนหน้าเดือนสิ้นสุด (มีนาคม) คือเดือน กุมภาพันธ์

แต่! เป็นเดือนกุมภาพันธ์ของปี 2016 ซึ่งเป็นปีที่เดือนกุมภาพันธ์มี 29 วัน (ปีอธิกสุรทิน) ดังนั้น

จำนวนวันของเดือนก่อนหน้าเดือนสิ้นสุด = จำนวนวันของเดือนกุมภาพันธ์ = 29

จึงได้ จำนวนวัน เท่ากับ 28 (29-1)

ดูๆแล้ว ขัดใจวัยรุ่นใช่ไหมครับ…

แต่เดี๋ยวก่อน ! (พูดเหมือน TV Direct ^^)

ถ้านำหลักการนี้มาพิสูจน์ โดยคำนวณกับทุกวันตลอดทั้งเดือน จะพบว่าเป็นคำตอบที่ถูกต้อง

เช่น ลองนำมาใช้กับเดือนสิงหาคม

แบบนี้ครับ

เป๊ะ! เลยใช่ไหมครับ

แต่..มันเป๊ะกับเดือนสิงหาคม เพราะ จำนวนวันของเดือนเริ่มต้น (มกราคม) เท่ากับ จำนวนวันของเดือนก่อนหน้าวันสิ้นสุด (กรกฎาคม)

หลักการนี้จะมีปัญหากับเคสที่จำนวนวันของเดือนเริ่มต้น กับ จำนวนวันเดือนก่อนหน้าวันสิ้นสุด แตกต่างกัน

โดยเฉพาะเคสที่วันสิ้นสุดอยู่ในเดือน มีนาคม เพราะเดือนก่อนหน้าคือเดือน กุมภาพันธ์!

เคสนี้จะมีปัญหาสุดๆ

ถ้าลองถามเอ็กเซลว่า วันที่ 31 มกราคม 2016 กับวันที่ 1 มีนาคม 2016 ห่างกันกี่เดือน กี่วัน?

เอ็กเซลจะตอบว่า ห่างกัน 1 เดือน -1 วัน !!

คุณไม่ได้อ่านผิดหรอกครับ เอ็กเซลตอบว่า

หนึ่งเดือน กับอีก ลบหนึ่งวัน !

ซึ่งเป็นตัวเลขที่เราคงใช้เป็นคำตอบไม่ได้ (ถ้าตอบแบบนี้ ซวยแน่นอน T_T)

ทำไมจึงเป็นเช่นนั้น?

เพราะเอ็กเซล จะกระโดดมายังวันเดียวกันของเดือนสิ้นสุด (31 มีนาคม) และถือว่าวันนั้นคือวันที่ 0

จากนั้นถอยกลับด้วยจำนวนวันของเดือนก่อนหน้าเดือนสิ้นสุด (29 วัน, เดือนกุมภาพันธ์ ปี 2016 มี 29 วัน)

ถอยกรูดเต็มที่ก็ได้แค่ 3 มีนาคม (ซึ่งถือว่าแตกต่างกัน 1 เดือน 1 วัน)

แต่เราต้องการวันที่ 1 มีนาคม จึงจำเป็นต้องถอยต่อ

ถ้าถอยไปถึงวันที่ 2 มีนาคม ถือว่าแตกต่างกัน (1 เดือน) 0 วัน

ทำไมเอ็กเซลจึงมอง 31 มกราคม 2016 ต่างกับ 2 มีนาคม 2016 (1 เดือน) 0 วัน?

เพราะในมุมมองของเอ็กเซล วันที่ 2 มีนาคม 2016 ก็คือวันที่ 31 กุมภาพันธ์ 2016

ถ้าต้องการหาระยะห่างถึงวันที่ 1 มีนาคม จริงๆแล้วต้องกระโดดไปยังวันที่ 31 กุมภาพันธ์ หรือก็คือวันที่ 2 มีนาคมก่อน แล้วค่อยถอยกลับไป 1 วัน

คำตอบที่ควรจะเป็น คือ

0 เดือน 30 วัน

แต่เดี๋ยวก่อน ! (TV Direct มาอีกแล้ว ^^)

วันสิ้นสุดอยู่ในเดือนมีนาคม 2016 (1 มีนาคม 2016) เดือนก่อนหน้าคือเดือน กุมภาพันธ์ ที่มี 29 วัน

การตอบว่า 0 เดือน 30 วัน มันแหม่งๆนะ

มันเกิน 29 วันแล้ว!

ควรจะปัด 30 วันให้เป็น 1 เดือนสิ

เอ็กเซลก็เลยปัดให้เป็น

1 เดือน -1 วัน

ฮ่วย!

ช่างฉลาดเสียนี่กระไร (ประชด)…

ถ้าลองถอยข้อมูลต่อ จะเห็นว่าตัวเลขดูสมเหตุสมผล

ถ้ามองอีกมุมนึง กรณีที่ วันที่ของวันสิ้นสุด น้อยกว่า วันที่ของวันเริ่มต้น เรามองเป็นการกระโดดไปยังเดือนก่อนหน้า แล้วค่อยวิ่งต่อก็ได้ เช่น

อยากรู้ว่า ระยะห่าง ระหว่าง วันที่ 31 มกราคม กับ วันที่ 7 กรกฎาคม คือ กี่เดือน กี่วัน?

ถ้าเรากระโดดไปยังวันที่ 31 กรกฎาคม ก่อน แล้วค่อยย้อนกลับมาวันที่ 7 กรกฎาคม อาจทำให้งง

เราก็กระโดดไปวันที่ 31 ของเดือนก่อนหน้าเดือนสิ้นสุดซะเลย

เดือนสิ้นสุดคือ กรกฎาคม เพราะฉะนั้น เดือนก่อนหน้าเดือนสิ้นสุดก็คือเดือน มิถุนายน

เราจึงกระโดดไปวันที่ 31 มิถุนายน

แต่วันที่ 31 มิถุนายน ไม่มีในปฏิทิน จึงต้องกระโดดเป็นวันที่ 1 กรกฎาคม แทน และนับเป็น 0

พอต้องการไปยังวันที่ 7 กรกฎาคม ก็เดินหน้าต่อไปอีก 6 วัน

คำตอบที่ได้จากเอ็กเซลคือ

5 เดือน 6 วัน

แต่เดี๋ยวก่อน! (TV Direct มาบ่อยนะเนี่ย ^^)

เคสนี้ ถ้าคิดเองในหัว เราจะตอบว่า

5 เดือน 7 วัน

(31 มกราคม กับ 7 พฤษภาคม)

เท่ากับว่า เอ็กเซล คำนวณ “ผิด” ไป 1 วัน

ด้วยเคสที่คิดได้ -1 วัน หรือ เคสที่คิดผิดไป 1 วัน จึงทำให้ DATEDIF เป็นฟังก์ชันต้องห้าม

สาปให้ผลการคำนวณผิด

ถ้าลองสังเกตให้ดี “คำสาป” จะมีผลเฉพาะ รูปแบบของผลลัพธ์ เป็น md

และต้องมี 1ใน 2 เงื่อนไข นี้พ่วงด้วยเสมอ นั่นคือ

1. จำนวนวันของเดือนเริ่มต้น ไม่เท่ากับ จำนวนวันของเดือนก่อนหน้าเดือนสิ้นสุด
2. วันเริ่มต้น คือวันที่ 30 หรือ 31  (รวมถึงเคสที่ วันเริ่มต้นเป็นวันที่ 29 และวันสิ้นสุดอยู่ในเดือนมีนาคมที่ไม่ใช่ปีอธิกสุรทิน)

แต่ถ้าไม่ตรงกับ 2 เงื่อนไขนี้ เราสามารถ “คลายสะกด” ฟังก์ชันต้องห้ามนี้ มาใช้ประโยชน์ได้ ^__^

(จริงๆแล้ว รูปแบบของผลลัพธ์ yd ก็มีโอกาส “ผิด” 1 วัน อ่านรายละเอียดได้จากบทความ Function แห่งความลับใน Excel )

แถม DATEDIF เป็นฟังก์ชันที่ใช้ง่าย มีองค์ประกอบ (Argument) แค่ 3 อย่าง

มันช่างเย้ายวนให้คลายสะกดเสียจริง

เช่น ถ้าเจอคำถามว่า ระยะห่างระหว่างวันที่ 20 มกราคม กับ 19 เมษายน คือกี่เดือน กี่วัน?

ลองคำนวณในหัว จะได้ 2 เดือน 30 วัน

30 วัน เกิดจาก 11 + 19

(11 วันในเดือน มกราคม (31 มกราคม – 20 มกราคม) + 19 วัน ในเดือน เมษายน)

พอถามเอ็กเซล ก็คำนวณได้ 2 เดือน 30 วัน

พอดีเด๊ะ!

แม้ DATEDIF มีโอกาสคำนวณผิดพลาดได้ 1-2 วัน หรืออาจคำนวณได้ผลลัพธ์เป็น -1 วัน

ถ้าถือว่าความผิดพลาดนี้ อยู่ในเกณฑ์ยอมรับได้ DATEDIF เป็นฟังก์ชันที่มีประโยชน์มากๆ

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

นี่กระมังจึงเป็นเหตุผลที่หลายคนพยายามคลายสะกด

“รู้ว่าเสี่ยง แต่คงต้องขอลอง”

Big Ass เขาบอกไว้ ^^

อ้อ! ไม่แนะนำให้ใช้ DATEDIF กับเอ็กเซลเวอร์ชั่นต่ำกว่า 2010 นะครับ  (เช่น 2007)

เพราะ “คำสาป” เยอะมาก คลายสะกดไม่ไหวแน่นอน…

.

ใครสนใจไฟล์ที่ผมใช้ประกอบการเขียนบทความ สามารถดาวน์โหลดได้จากลิงค์นี้ครับ

datedif_theforbiddenfunction_161115

.

ขอบคุณไอเดียดีๆจาก

http://www.excelexperttraining.com/home/free-excel-e-learning/excel-articles-list/excel-for-general-users-articles/258-datedif-false-answer

.

หากคุณชอบคลิปนี้ สามารถอัพเดตคลิปหรือบทความใหม่ๆ โดยคลิก Like เฟสบุ๊คแฟนเพจ วิศวกรรีพอร์ต หรือคลิก ที่นี่

อย่าลืมแชร์ให้เพื่อนอ่านเพื่อเป็นกำลังใจให้คนเขียนด้วยนะครับ ^_^