ทราบไหมครับว่าเอ็กเซลมี “ฟังก์ชั่นแห่งความลับ” (The Function of Secret) ด้วยนะ!
อารมณ์ประมาณ “ห้องแห่งความลับ” (The Chamber of Secret) ของแฮร์รี่ พอตเตอร์ ยังไงยังงั้น
ฮ้า! น่าสนใจใช่ไหมละ…
ฟังก์ชั่นที่ว่านั้น มีชื่อว่า
DATEDIF
ทำไมถึงได้ชื่อว่า ฟังก์ชั่นแห่งความลับ น่ะหรือครับ?
เพราะเป็นฟังก์ชั่นที่ไม่มีไกด์ขึ้นมา เวลาพิมพ์ชื่อฟังก์ชั่น
เช่น เวลาเราพิมพ์ =date ลงไปในฟอร์มูล่าบาร์ ฟังก์ชั่นที่ขึ้นไกด์ให้มีแค่
- DATE
- DATEVALUE
ไม่มี DATEDIF !
ต่อให้หาในลิสต์ฟังก์ชั่นเกี่ยวกับวัน-เวลา ก็ไม่มี DATEDIF
โดนข้ามไปเฉยเลย
เหมือนไม่อยู่ในสารบบ
ไม่มีวิธีการใช้ใน Help อีกต่างหาก!
ทำไมถึงไม่มีในลิสต์น่ะหรือครับ?
เพราะว่าเจ้า DATEDIF เป็นฟังก์ชั่นที่มี “บั๊ก”
ไมโครซอฟต์เค้าเลยไม่อยากเปิดให้ใช้แบบโจ๋งครึ่ม
ก่อนจะคุยว่า”บั๊ก” คืออะไร เรามาทำความรู้จักกับเจ้าฟังก์ชั่นนี้กันก่อนครับ
DATEDIF เป็นฟังก์ชั่นที่ใช้หา ระยะห่าง ระหว่าง วัน 2 วัน
โครงสร้างของฟังก์ชั่นคือ
=DATEDIF ( วันเริ่มต้น, วันสิ้นสุด, รูปแบบของผลลัพธ์ )
ผลลัพธ์ที่ได้คือ ระยะห่าง ระหว่าง วันเริ่มต้น และ วันสิ้นสุด
สามารถแสดง รูปแบบของผลลัพธ์ (Interval) เป็น วัน หรือ เดือน หรือ ปี ได้ทั้งหมด 6 รูปแบบ นั่นคือ
- y (year)
- m (month)
- d (day)
- ym (year-month)
- yd (year-day)
- 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 เฟสบุ๊คแฟนเพจ วิศวกรรีพอร์ต หรือคลิก ที่นี่
อย่าลืมแชร์ให้เพื่อนอ่านเพื่อเป็นกำลังใจให้คนเขียนด้วยนะครับ ^_^
แต่ถ้า 1/2/2016 – 1/3/2016 กลับได้ 29 วัน
เอ็กเซลจะคำนวณ yd โดยดูจาก ปีของ วันเริ่มต้น เป็นหลักครับ
ในเคสนี้ ปีของ วันเริ่มต้น คือปีอธิกสุรทิน และเป็นปีเดียวกันกับ วันสิ้นสุด จึงคำนวณถูกต้องครับ
เป็นคำถามที่ดีมากครับ คำตอบอยู่ในบทความใหม่ บทความนี้ครับ https://reportingengineer.com/2016/11/15/datedif-the-forbidden-function/
แล้ววันที่เป็นวันหยุด เช่น เสาร์-อาทิตย์ หรือวันหยุดนักขัตฤกษ์ สามารถหักออกได้ไม๊ครับ ในกรณีที่เราต้องเฉพาะวันทำการเท่านั้น พอจะมีการผูกสูตร เพิ่มเติม ไม๊ครับขอบคุณครับ
ใช้ฟังก์ชั่น NETWORKDAYS ครับ ใช้ง่าย และไม่ต้องผูกสูตรเพิ่มเติมครับ
ผมรบกวนสอบถามนิดหนึ่งครับ อยากให้ช่วยแนะนำ ระหว่าง IF กับ Datedif คือผมกำลังคำนวณ Incentive ภาพรวมทุกเดือน โดย มี Cell
พนักงาน
ประเภทพนักงานแบบ 1 ปี กับ 3 ปี
วันเริ่มงาน
วันสิ้นสุดสัญญา > =EDATE(C2,B2-1) ผมใช้สูตรนี้เพื่อดูวันสุดท้าย
จำนวนเงิน
ถ้าพนักงานแบบ 1 ปีทำครบจะให้ Rate คูณ 2 ในเดือนสุดท้าย
ถ้าพนักงานแบบ 3 ปีทำครบจะให้ Rate คูณ 2.5 ในเดือนสุดท้าย
พนักงานเริ่มงานไม่พร้อมกัน และ วันเริ่มภายในเดือนไม่แน่นอน
=IF($D2=G$1,IF($B2=36,$E2*3.5,$E2*2),$E2)
พยายามแล้วครับ แต่ $D2=G$1 ยังไงมันก็ถูกอยูดี เพราะ จะดูแค่เดือนแต่ Excel ดูวันว่าตรงกันเลย เลยคิดว่าต้องใช้ Datedif มาร่วมด้วยหรือเปล่าครับ
ประเด็นคืออยากเห็นยอด Forecast ของแต่ละเดือน ว่าเดือนไหนจะต้องจ่ายเพิ่มครับ
รบกวนลองส่งไฟล์ที่มีข้อมูลตัวอย่างให้ดูที่ nattawut@wkrpt.com ได้ไหมครับ ดูจากเงื่อนไขแล้วคิดว่าทำได้ครับ
ใส่สูตร DATEDIF แล้วอยากเรียงลำดับมากไปน้อยด้วยค่ะ จะทำยังไงได้บ้าง