Excel Forum

แบบที่ 1
เลขที่ วันเริ่มต้น วันสิ้นสุด ทดสอบ
5102014745 01/10/2008 30/09/2021 01/10/2008

ข้อมูลตัวอย่างดังกล่าวตามข้างบน อยากได้สูตรที่สามารถจับค่า ตัวเลขจากตัวอย่างดังกล่าว แบบที่ 1 เลขที่ 51 เริ่มต้นคือปีเริ่มต้น 2551 ต้องการแปลงเป็นปี ค.ศ. ตามปีดังกล่าวก็คือปี 2008 ส่วนวันที่เริ่มต้น ต้องมากกว่าวันหมดอายุ 1 วัน ผลลัพธ์วันเริ่มต้นก็คือ 01/10/2008


แบบที่ 2
เลขที่ วันเริ่มต้น วันสิ้นสุด ทดสอบ
ว00015/2551 01/10/2008 30/09/2021 01/10/2008

ข้อมูลคล้ายตัวอย่างที่ 1 แต่เปลี่ยนเป็น 4 ตัวหลัง นั้นก็คือ 2551 อยากได้สูตรที่สามารถจับค่า ตัวเลขจากตัวอย่างดังกล่าว เลขที่ 2551 4 ตัวหลังท้ายคือปีเริ่มต้น 2551 ต้องการแปลงเป็นปี ค.ศ. ตามปีดังกล่าวก็คือปี 2008 ส่วนวันที่เริ่มต้น ต้องมากกว่าวันหมดอายุ 1 วัน ผลลัพธ์วันเริ่มต้นก็คือ 01/10/2008

อ่านแล้วยังไม่เข้าใจครับ ตัวอย่างผลลัพท์ที่ต้องการคืออะไรครับ ปัญหาอยู่ตรงไหน

ผมเขียนคำถามสลับกัน ผลลัพธ์ต้องการคือ อยากให้วันเริ่มต้น อ้างอิงจากวันหมดอายุ และปีเริ่มต้นอ้างอิงจากเลขที่
-------------------------------------------------------------------------------------------
เช่น 25/05/2017 คือวันหมดอายุ อยากให้ปีเริ่มต้นอ้างอิงจากเลขที่ 5802014745 และ วันที่เริ่มต้นอ้างอิงจากวันหมดอายุ 25/05/2017
-------------------------------------------------------------------------------------------
ปีเริ่มต้น = 5802014745 หมายถึงเริ่ม พ.ศ. ปี 2558 แปลง ค.ศ. ก็คือ 2015
วันเริ่มต้น = 25/05/2017 คือปีที่หมดอายุ หมายถึงวันเริ่มต้นต้องมากกว่าวันหมดอายุ 1 วัน ก็คือ 26/05
-------------------------------------------------------------------------------------------
ึความหมายผมคือ ผมอยากได้สูตรที่สามารถ จับปีเริ่มต้นจากเลขที่ใบอนุญาตได้ ด้วยเขียนเป็นภาษาพูดดังนี้ เลขที่ 5802014745 หากพบว่า ตัวเลข 2 ตัวแรก 58 แปลง ค.ศ. คือ 2015 เป็นจริง ผลลัพธ์คือ วันที่เริ่มต้นต้องมากกว่า วันหมดอายุ 1 วัน และปี ค.ศ. ที่เริ่มต้นต้องเป็น 2015 ผลลัพธ์ก็คือ 26/05/2015

สรุปอีกรอบก็ได้
เลขที่ 5802014745 วันหมดอายุ 25/05/2017
วันเริ่มต้น ต้องการให้เวลาหลังจากใส่เลขที่และวันหมดอายุเรียบร้อยแล้ว วันเริ่มต้นสามารถกำหนดได้เองโดยไม่ต้องแก้ไขด้วยตนเอง

ไม่แน่ใจว่าใช่ที่ต้องการหรือไม่นะครับ
E6 =IF(LEFT(A6,2)="51",(TEXT(C6+1,"dd-mm")&"-"&(("25"&LEFT(A6,2))-543))+0,"0") Enter
E13 =(TEXT(C13+1,"dd-mm")&"-"&RIGHT(A13,4)-543)+0 Enter

parakorn wrote: Wed Feb 07, 2018 3:09 pm ไม่แน่ใจว่าใช่ที่ต้องการหรือไม่นะครับ
E6 =IF(LEFT(A6,2)="51",(TEXT(C6+1,"dd-mm")&"-"&(("25"&LEFT(A6,2))-543))+0,"0") Enter
E13 =(TEXT(C13+1,"dd-mm")&"-"&RIGHT(A13,4)-543)+0 Enter

ตรงเปะเลยครับ ขอบคุณครับ

parakorn wrote: Wed Feb 07, 2018 3:09 pm ไม่แน่ใจว่าใช่ที่ต้องการหรือไม่นะครับ
E6 =IF(LEFT(A6,2)="51",(TEXT(C6+1,"dd-mm")&"-"&(("25"&LEFT(A6,2))-543))+0,"0") Enter
E13 =(TEXT(C13+1,"dd-mm")&"-"&RIGHT(A13,4)-543)+0 Enter

รบกวนสอบถามเพิ่มเติมครับ ถ้าต้องการให้ 2 สูตรนี้ร่วมกันมีรูปแบบ ต้องเขียนอย่างไรครับ ขอบคุณมากครับ

ประมาณนี้หรือเปล่าครับ
E6 =IF(LEFT(A6,2)="51",(TEXT(C6+1,"dd-mm")&"-"&(("25"&LEFT(A6,2))-543))+0,((TEXT(C6+1,"dd-mm")&"-"&RIGHT(A6,4)-543)+0)) Enter

parakorn wrote: Thu Feb 08, 2018 10:00 am ประมาณนี้หรือเปล่าครับ
E6 =IF(LEFT(A6,2)="51",(TEXT(C6+1,"dd-mm")&"-"&(("25"&LEFT(A6,2))-543))+0,((TEXT(C6+1,"dd-mm")&"-"&RIGHT(A6,4)-543)+0)) Enter

ได้ครับขอบคุณครับ ผมขอคำแนะนำอีกนิดนึงครับ น่าจะนอกคำถามนี้แล้ว กรณี IF(LEFT(A6,2)="51" ต้องเขียนทุกปี พอมีวิธีลดจำนวนสูตรลงไหมครับ ประมาณว่าเริ่มตั้งจำนวนที่ 29 สุดที่ 70 รบกวนอีกครั้งขอคำแนะนำเพิ่มเติมครับ

ปรับเป็น
E6 =IF(OR(LEFT(A6,2)>=29,LEFT(A6,2)<=70),(TEXT(C6+1,"dd-mm")&"-"&(("25"&LEFT(A6,2))-543))+0,((TEXT(C6+1,"dd-mm")&"-"&RIGHT(A6,4)-543)+0)) Enter

parakorn wrote: Thu Feb 08, 2018 5:06 pm ปรับเป็น
E6 =IF(OR(LEFT(A6,2)>=29,LEFT(A6,2)<=70),(TEXT(C6+1,"dd-mm")&"-"&(("25"&LEFT(A6,2))-543))+0,((TEXT(C6+1,"dd-mm")&"-"&RIGHT(A6,4)-543)+0)) Enter

แบบที่ 2 เลขที่ ว00015/2556 ติด Error Value ครับ รบกวนอีกครั้งครับ

แนบไฟล์ล่าสุดมาด้วยจะได้สะดวกในการตอบต่อไปจากนั้นครับ

snasui wrote: Thu Feb 08, 2018 9:24 pm แนบไฟล์ล่าสุดมาด้วยจะได้สะดวกในการตอบต่อไปจากนั้นครับ

แนบแล้วครับ

ตัวอย่างสูตรที่เซลล์ E6 ครับ

=DATE(IF(ISNUMBER(LEFT(A6)+0),(25&LEFT(A6,2)),RIGHT(A6,4))-543,MONTH(C6),DAY(C6)+1)

Enter > Copy ลงด้านล่าง

การที่ข้อมูลมีความหลากหลายจำเป็นต้องแจ้งให้ครบถ้วนมาตั้งแต่แรก ผู้ตอบจะได้คิดทีเดียว

สำหรับสูตรนี้หากไม่ได้คำตอบกรุณาแจ้งวิธีคิดมาอย่างละเอียดสำหรับเซลล์นั้น ๆ ครับ

snasui wrote: Thu Feb 08, 2018 10:03 pm ตัวอย่างสูตรที่เซลล์ E6 ครับ

=DATE(IF(ISNUMBER(LEFT(A6)+0),(25&LEFT(A6,2)),RIGHT(A6,4))-543,MONTH(C6),DAY(C6)+1)

Enter > Copy ลงด้านล่าง

การที่ข้อมูลมีความหลากหลายจำเป็นต้องแจ้งให้ครบถ้วนมาตั้งแต่แรก ผู้ตอบจะได้คิดทีเดียว

สำหรับสูตรนี้หากไม่ได้คำตอบกรุณาแจ้งวิธีคิดมาอย่างละเอียดสำหรับเซลล์นั้น ๆ ครับ

คำตอบถูกต้องครับ ขอบคุณครับ ว่าแต่ A6+0 และ 25& นี้ความหมายของสูตรนี้คืออะไรครับไม่เข้าใจ

LEFT(A6)+0 แปลว่าให้ตัด A6 มา 1 อักขระ ตัดมาแล้วให้บวกด้วย 0 บวกเพื่อทำให้เป็นตัวเลข เนื่องการตัดอักขระออกมาจะเป็น Text ครับ

25&Left(Cell,2) คือนำ 25 ไปเชื่อมกับอักขระด้านซ้ายของ Cell อีก 2 ตัวเพื่อทำให้เป็น พ.ศ. เพื่อให้ง่ายต่อการแปลงเป็น ค.ศ. ซึ่งการแปลง พ.ศ. ให้เป็น ค.ศ. จะหักออกด้วย 543 ครับ

snasui wrote: Thu Feb 08, 2018 10:27 pm LEFT(A6)+0 แปลว่าให้ตัด A6 มา 1 อักขระ ตัดมาแล้วให้บวกด้วย 0 บวกเพื่อทำให้เป็นตัวเลข เนื่องการตัดอักขระออกมาจะเป็น Text ครับ

25&Left(Cell,2) คือนำ 25 ไปเชื่อมกับอักขระด้านซ้ายของ Cell อีก 2 ตัวเพื่อทำให้เป็น พ.ศ. เพื่อให้ง่ายต่อการแปลงเป็น ค.ศ. ซึ่งการแปลง พ.ศ. ให้เป็น ค.ศ. จะหักออกด้วย 543 ครับ

อ้อ ขอบคุณครับ