ทราบไหมครับว่าเอ็กเซลมี “ฟังก์ชั่นแห่งความลับ” (The Function of Secret) ด้วยนะ!

อารมณ์ประมาณ “ห้องแห่งความลับ” (The Chamber of Secret) ของแฮร์รี่ พอตเตอร์ ยังไงยังงั้น

ฮ้า! น่าสนใจใช่ไหมละ…

ฟังก์ชั่นที่ว่านั้น มีชื่อว่า

DATEDIF

ทำไมถึงได้ชื่อว่า ฟังก์ชั่นแห่งความลับ น่ะหรือครับ?

เพราะเป็นฟังก์ชั่นที่ไม่มีไกด์ขึ้นมา เวลาพิมพ์ชื่อฟังก์ชั่น

เช่น เวลาเราพิมพ์ =date ลงไปในฟอร์มูล่าบาร์ ฟังก์ชั่นที่ขึ้นไกด์ให้มีแค่

• DATE
• DATEVALUE

ไม่มี DATEDIF !

ต่อให้หาในลิสต์ฟังก์ชั่นเกี่ยวกับวัน-เวลา ก็ไม่มี DATEDIF

โดนข้ามไปเฉยเลย

เหมือนไม่อยู่ในสารบบ

ไม่มีวิธีการใช้ใน Help อีกต่างหาก!

ทำไมถึงไม่มีในลิสต์น่ะหรือครับ?

เพราะว่าเจ้า DATEDIF เป็นฟังก์ชั่นที่มี “บั๊ก”

ไมโครซอฟต์เค้าเลยไม่อยากเปิดให้ใช้แบบโจ๋งครึ่ม

ก่อนจะคุยว่า”บั๊ก” คืออะไร เรามาทำความรู้จักกับเจ้าฟังก์ชั่นนี้กันก่อนครับ

DATEDIF เป็นฟังก์ชั่นที่ใช้หา ระยะห่าง ระหว่าง วัน 2 วัน

โครงสร้างของฟังก์ชั่นคือ

=DATEDIF ( วันเริ่มต้น,  วันสิ้นสุด,  รูปแบบของผลลัพธ์ )

ผลลัพธ์ที่ได้คือ ระยะห่าง ระหว่าง วันเริ่มต้น และ วันสิ้นสุด

สามารถแสดง รูปแบบของผลลัพธ์ (Interval) เป็น วัน หรือ เดือน หรือ ปี ได้ทั้งหมด 6 รูปแบบ นั่นคือ

1. y (year)
2. m (month)
3. d (day)
4. ym (year-month)
5. yd (year-day)
6. md (month-day)

แต่ละรูปแแบบ มีความหมายดังนี้

y แสดงผลต่างในรูปแบบ ปี ระหว่าง วันเริ่มต้น และ วันสิ้นสุด

m แสดงผลต่างในรูปแบบ เดือน ระหว่าง วันเริ่มต้น และ วันสิ้นสุด ถ้า วันเริ่มต้น และ วันสิ้นสุด อยู่ห่างกันมากกว่าหนึ่งปี จะได้ผลลัพธ์มากกว่า 12

d แสดงผลต่างในรูปแบบ วัน ระหว่าง วันเริ่มต้น และ วันสิ้นสุด ถ้า วันเริ่มต้น และ วันสิ้นสุด อยู่ห่างกันมากกว่าหนึ่งปี จะได้ผลลัพธ์มากกว่า 365 (เสมือนว่า จับ วันสิ้นสุด ลบด้วย วันเริ่มต้น นั่นเอง)

ym แสดงผลต่างในรูปแบบ เดือน ระหว่าง วันเริ่มต้น และ วันสิ้นสุด โดยคิดเสมือนว่า วันเริ่มต้น และ วันสิ้นสุด อยู่ในปีเดียวกัน เพราะฉะนั้น ผลลัพธ์จาก ym ไม่มีทางมากกว่า 11 (ถ้าได้ 12 จะโดนปัดเป็นปี)

yd แสดงผลต่างในรูปแบบ วัน ระหว่าง วันเริ่มต้น และ วันสิ้นสุด โดยคิดเสมือนว่า วันเริ่มต้น และ วันสิ้นสุด อยู่ในปีเดียวกัน เพราะฉะนั้น ผลลัพธ์จาก yd ไม่มีทางมากกว่า 364 (สำหรับปีที่มี 365 วัน หรือไม่มีทางมากกว่า 365 สำหรับปีที่มี 366 วัน)

md แสดงผลต่างในรูปแบบ วัน ระหว่าง วันเริ่มต้น และ วันสิ้นสุด โดยคิดเสมือนว่า วันเริ่มต้น และ วันสิ้นสุด อยู่ในเดือนเดียวกัน เพราะฉะนั้น ผลลัพธ์จาก yd ไม่มีทางมากกว่า 30 (สำหรับเดือนที่มี 31 วัน หรือไม่มีทางมากกว่า 29 สำหรับเดือนที่มี 30 วัน)

ดูเผืนๆ เหมือนเราจะใช้แค่ y, m, d ใช่ไหมครับ?

แล้ว ym, yd, md ใช้ในกรณีไหน?

คำตอบคือ ใช้ในกรณีที่ต้องการแสดงผลลัพธ์เป็น  x ปี y เดือน z วัน (หรือ z วัน y เดือน x ปี) ครับ

เช่น ต้องการแสดงผลลัพธ์ว่า ระยะห่างระหว่างวันที่ 1 มีนาคม 2013 (สมมติว่าพิมพ์ใส่เซลล์ A2) กับ วันที่ 2 พฤษภาคม 2016 (สมมติว่าพิมพ์ใส่เซลล์ B2) คือ

3 ปี 2 เดือน 1 วัน

เราต้องเขียนสูตรเป็นแบบนี้ครับ

=DATEDIF(A2,B2,”y”)&” years “
& DATEDIF(A2,B2,”ym”)&” months “
& DATEDIF(A2,B2,”md”)&” days”

ผลลัพธ์จาก DATEDIF(A2,B2,”y”)&” years “ คือ 3 years

ผลลัพธ์จาก DATEDIF(A2,B2,”ym”)&” months “ คือ 2 months

ผลลัพธ์จาก DATEDIF(A2,B2,”md”)&” days” คือ 1 days

พอจับมาเชื่อมกับด้วยเครื่องหมาย ampersand (&) จึงกลายเป็น

= “3 years ” & “2 months ” & “1 days”

(มีช่องว่างหลังคำว่า years และ คำว่า months เพื่อเว้นระยะห่างระหว่างคำว่า years และ เลขเดือน และ เว้นระยะห่างระหว่างคำว่า months และ เลขวัน)

= 3 years 2 months 1 days

ชาดาม!!

บางทีเราอาจเจอเคสซับซ้อนขึ้น เช่น ระยะห่างของ วันเริ่มต้น และ วันสิ้นสุด น้อยกว่า 1 ปี แต่ต้องการให้แสดงแค่เดือน และวัน เช่น

1 เดือน 29 วัน (ไม่มี 0 ปี ข้างหน้า)

หรือ ระยะห่างน้อยกว่า 1 เดือน ต้องการให้แสดงแค่วัน เช่น

14 วัน (ไม่มี 0 ปี 0 เดือน ข้างหน้า)

(เรื่องมากเนอะ ^^)

เราต้องนำฟังก์ชั่น IF มาช่วยประยุกต์ ในรูปแบบประมาณนี้ครับ

=IF(DATEDIF(A4,B4,”y”)=0,””,DATEDIF(A4,B4,”y”)&” years “)
& IF(DATEDIF(A4,B4,”ym”)=0,””,DATEDIF(A4,B4,”ym”)&” months “)
& DATEDIF(A4,B4,”md”)&” days”

สมมติว่า วันเริ่มต้นคือ วันที่ 1 มีนาคม 2016 และ วันสิ้นสุด คือวันที่ 15 มีนาคม 2016

ผลลัพธ์จาก IF ตัวที่ 1 หรือ (IF(DATEDIF(A4,B4,”y”)=0,””,DATEDIF(A4,B4,”y”)&” years “)) คือ ค่าว่าง (“”) เพราะระยะห่างของ 2 วันนี้ = 0 ปี

ผลลัพธ์จาก IF ตัวที่ 2 หรือ IF(DATEDIF(A4,B4,”ym”)=0,””,DATEDIF(A4,B4,”ym”)&” months “) คือ ค่าว่าง (“”) เพราะระยะห่างของ 2 วันนี้ = 0 เดือน

ผลลัพธ์จาก DATEDIF(A4,B4,”md”)&” days”  คือ 14 วัน เพราะใช้ รูปแแบบผลลัพธ์คือ md (แสดงผลต่างในรูปแบบ วัน ระหว่าง วันเริ่มต้น และ วันสิ้นสุด โดยคิดเสมือนว่า วันเริ่มต้น และ วันสิ้นสุด อยู่ในเดือนเดียวกัน)

เมื่อจับมาเชื่อมกับด้วยเครื่องหมาย ampersand (&) ผลลัพธ์ที่ได้จึงกลายเป็น

=”” & “” & 14 days

= 14 days

ชาดาม!!

นอกจากนี้ เรายังสามารถประยุกต์ใช้เพื่อหาอายุ (เช่น อายุของพนักงาน, อายุของสินค้าในสต็อก) ได้อีกด้วย

สูตรที่นำมาใช้ หน้าตาประมาณนี้ครับ

=DATEDIF(A3,TODAY(),”y”)&” years ”
& DATEDIF(A3,TODAY(),”ym”)&” months ”
& DATEDIF(A3,TODAY(),”md”)&” days”

สมมติว่า วันเริ่มต้นคือ วันที่ 14 มิถุนายน 1978 (A3) และ วันนี้ คือวันที่ 11 พฤศจิกายน 2016 ( TODAY() )

ผลลัพธ์จาก DATEDIF(A3,TODAY(),”y”)&” years “คือ 38 years

ผลลัพธ์จาก DATEDIF(A3,TODAY(),”ym”)&” months ” คือ 4 months

ผลลัพธ์จาก DATEDIF(A3,TODAY(),”md”)&” days”  คือ 28 days

เมื่อจับมาเชื่อมกับด้วย ampersand ผลลัพธ์จึงกลายเป็น

= .38 years ” & “4 months ” & 28 days

= 38 years 4 months 28 days

ชาดาม !!

…………………..

จะเห็นได้ว่า DATEDIF เป็นฟังก์ชั่นที่มีประโยชน์ และใช้ง่ายใช่ไหมครับ

แต่สิ่งที่ทำให้ DATEDIF เป็น ฟังก์ชั่นแห่งความลับ ก็คือ “บั๊ก” หรือ ความผิดพลาด จากการคำนวณ นั่นเอง!

ถ้าเป็นเอ็กเซลเวอร์ชั่นต่ำกว่า 2010 (เช่น 2007) ผลลัพธ์จาก DATEDIF มีโอกาสผิดพลาดสูงมาก จนไม่อาจนำไปใช้งานได้เลย

แต่ถ้าเป็นเอ็กเซลเวอร์ชั่นตั้งแต่ 2010 เป็นต้นไป ความผิดพลาดนั้นถูกแก้ไขไปเกือบหมดแล้ว แต่ยังมีหลงเหลืออยู่บ้าง

ที่เห็นแบบชัดเจนคือ ความผิดพลาดจากการใช้ รูปแบบผลลัพธ์ แบบ yd และ md

ถ้าใช้รูปแบบผลลัพธ์ yd (ผลต่างในรูปแบบ วัน ระหว่าง วันเริ่มต้น และ วันสิ้นสุด โดยคิดเสมือนว่า วันเริ่มต้น และ วันสิ้นสุด อยู่ในปีเดียวกัน) ของปีที่เดือนกุมภาพันธ์มี 29 วัน หรือก็คือปีอธิกสุรทิน ผลลัพธ์ที่ได้จะ “ผิด” ในแง่ของจิตวิทยา

เพื่อทำให้เห็นภาพ ขออธิบายด้วยเคสนี้ครับ

สมมติว่า วันเริ่มต้น คือวันที่ 1 กุมภาพันธ์ 2015 (จากภาพคือ A2)

วันสิ้นสุด คือวันที่ 1 มีนาคม 2016 (จากภาพคือ B2)

ซึ่งปี 2016 เป็นปีที่เดือนกุมภาพันธ์มี 29 วัน (สังเกตง่ายๆคือ 4 หาร 2016 ลงตัว)

ผลลัพธ์จาก

=DATEDIF(A2,B2,D6)

= 28

แทนที่จะเป็น 29

(เพราะเดือนกุมภาพันธ์ปี 2016 มี 29 วัน)

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

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

จากเคสนี้ ปีของ วันเริ่มต้น คือ 2015 ซึ่งเป็นปีที่เดือนกุมภาพันธ์มี 28 วัน ดังนั้น จึงคำนวณ yd โดยยึดว่าเดือนกุมภาพันธ์มี 28 วัน และไม่สนใจว่าปีของ วันสิ้นสุด จะเป็นปีอธิกสุรทินหรือไม่

ซึ่งก็คือ “บั๊ก” ในทางจิตวิทยา

ถ้ามองในมุมของเอ็กเซล เคสนี้จะตอบว่ามี 28 วัน ก็มิผิด

แต่ถ้ามองในอีกมุมนึง เคสนี้น่าจะตอบว่า 29 วัน

นี่อาจเป็นสาเหตุหนึ่งที่ทำให้ DATEDIF กลายเป็นฟังก์ชั่นแห่งความลับ

แต่ถ้าต้องการให้แสดงเคสนี้เป็น 29 วันล่ะ ทำยังไงดี?

ถ้าต้องการแสดงผลลัพธ์เป็น 29 วัน เราต้องเขียนสูตรแบบนี้ครับ

=B2-EDATE(A2,DATEDIF(A2,B2,”y”)*12)

ถ้า A2 คือ วันที่ 1 กุมภาพันธ์ 2015

และ B2 คือ วันที่ 1 มีนาคม 2016

ผลลัพธ์จาก DATEDIF(A2,B2,”y”)  คือ 1

จับ 1 ไปเข้าสูตรต่อ กลายเป็น

1*12

= 12

จับ 12 เข้าไปในสูตรต่อ

EDATE(A2,12) 

ได้ผลลัพธ์เป็น 1 กุมภาพันธ์ 2016 

(ค่าจาก EDATE คือวันที่เดียวกัน แต่ห่างกัน x เดือน (ในที่นี้ ให้ x = 12))

จับค่าแทนในสูตรต่อ

= B2 – 1 กุมภาพันธ์ 2016

= 1 มีนาคม 2016 – 1 กุมภาพันธ์ 2016

= 29

ได้ค่าเป็น 29 ตามต้องการ !

นอกจากเคสที่ “yd” คำนวณ “ผิด” แล้ว มาดูเคส “md” (แสดงผลต่างในรูปแบบ วัน ระหว่าง วันเริ่มต้น และ วันสิ้นสุด โดยคิดเสมือนว่า วันเริ่มต้น และ วันสิ้นสุด อยู่ในเดือนเดียวกัน) กันบ้าง

ขออธิบายด้วยเคสนี้ครับ

สมมติว่า วันเริ่มต้น คือวันที่ 31 มกราคม 2016 (จากภาพคือ A2)

วันสิ้นสุด คือวันที่ 2 มีนาคม 2016 (จากภาพคือ B2)

ถ้าคำนวณ “md” ด้วยสูตร

=DATEDIF(A2,B2,”md”)

ได้ผลลัพธ์ = 0 วัน

แทนที่จะเป็น 2 วัน !

หรือถ้าคำนวณด้วยสูตร

=DATEDIF(A2,B2,”y”)&” years ”
& DATEDIF(A2,B2,”ym”)&” months ”
& DATEDIF(A2,B2,”md”)&” days”

จะได้คำตอบเป็น 0 years 1 months 0 days

แทนที่จะเป็น 0 years 1 months 2 days

ซะงั้นอ่ะ!

นี่จึงอาจเป็นอีกเหตุผลนึงที่ทำให้ DATEDIF กลายเป็น “ฟังก์ชั่นแห่งความลับ”

แล้วเคสนี้จะแก้ไขอย่างไรน่ะหรือครับ?

วิธีแก้ไขเคสนี้อาจซับซ้อนเล็กน้อยครับ อาจารย์สมเกียรติ ฟุ้งเกียรติ เคยให้แนวทางไว้ อ่านรายละเอียดได้จากลิงค์นี้ครับ

http://www.excelexperttraining.com/forums/showthread.php?t=12558&p=53432#post53432

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

แถมมีโอกาสผิดสูงอีกต่างหาก T_T

แต่ DATEDIF ก็เป็นฟังก์ชั่นที่มี “บั๊ก” ยิ่งถ้าเป็นเอ็กเซลเวอร์ชั่นต่ำกว่า 2010 (เช่น 2007) เรียกได้ว่า บั๊กกระจาย

เอ็กเซลเวอร์ชั่นตั้งแต่ 2010 เป็นต้นไป บั๊กเหล่านั้นถูกแก้ไขไปเยอะแล้ว แต่ยังคงต้องระวังเรื่องการคำนวณ yd และ md ของปีที่เดือนกุมภาพันธ์มี 29 วัน

เพราะมีโอกาสคำนวณผิดได้ 1-2 วัน

ถ้าคิดว่าผิดแค่นิดหน่อย ช่างมันเห๊อะ การใช้ DATEDIF เป็นวิธีที่ง่ายที่สุด

แต่ถ้าใช้ฟังก์ชั่น DATEDIF ไม่เป็น

งั้นต้องอ่านบทความนี้อีกรอบครับ ^__^

…………………………………..

สำหรับใครที่สนใจไฟล์ตัวอย่างของบทความนี้ สามารถดาวน์โหลดได้เลยครับ

datedif_thefunctionofsecret_161112

……………………………………

อ้างอิงข้อมูลจาก

http://www.cpearson.com/Excel/datedif.aspx

http://www.mrexcel.com/forum/excel-questions/518862-datedif-wrong-no-days.html

.

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

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