แสดงผลวันที่เป็นช่วงเวลาแบบสัปดาห์

[kmb]

ผมได้ทำสูตรดังไฟล์ที่แนบ โดยค่าที่ต้องการคือ Period (คอลัมน์ G) ซึ่งต้องการแสดงค่าเฉพาะวันจันทร์ – วันศุกร์เท่านั้น วันเสาร์และอาทิตย์ให้ปัดไปเป็นสัปดาห์ถัดไป ตัวอย่างการนำไปใช้ เช่น ทำ Pivot Table แต่แสดงผลเป็นสัปดาห์ และต้องการให้เห็นช่วงเวลาด้วย

ทั้งนี้ค่าที่เอาไว้ link กับวันที่จากข้อมูลอื่นก็คือ Date (คอลัมน์ A) แต่ถ้าเป็นกรณีวันเสาร์และอาทิตย์ ผมให้บวกวันขึ้นไปตาม คอลัมน์ J แล้วค่อยนำวันที่หลังจากบวกแล้วมาหาที่ คอลัมน์ A อีกทีนึง เพื่อให้ได้ค่า Period เป็นของสัปดาห์ถัดไป

ขอคำแนะนำดังนี้ครับ

1. มีวิธีอื่นที่ดีกว่านี้แนะนำไหมครับ

2. ค่าที่ได้ก็ถูกต้องยกเว้นเฉพาะ สัปดาห์ระหว่างปี ค่าจะผิดพลาด

[snasui]

ลองตามนี้ครับ

ที่ G2 คีย์

=IF(WEEKDAY(A2,2)>5,"",TEXT(INDEX($A:$A,MATCH($D2,$D:$D,0)+(C2>"01")),"dd - ")&TEXT(LOOKUP(2,1/(WEEKDAY((INDEX($A:$A,MATCH($D2,$D:$D,0)):INDEX($A:$A,MATCH($D2,$D:$D))),2)<6),(INDEX($A:$A,MATCH($D2,$D:$D,0)):INDEX($A:$A,MATCH($D2,$D:$D)))),"dd mmm yy"))

Enter

ผมทำตัวอย่างไว้ให้ที่ K2 ในไฟล์แนบเพื่อจะได้ใช้เปรียบเทียบกับสูตรเดิมได้ครับ

[kmb]

ได้แล้วครับ ขอบคุณครับ

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

จาก

ที่ G2 คีย์

=IF(WEEKDAY(A2,2)>5,"",TEXT(INDEX($A:$A,MATCH($D2,$D:$D,0)+(C2>"01")),"dd - ")&TEXT(LOOKUP(2,1/(WEEKDAY((INDEX($A:$A,MATCH($D2,$D:$D,0)):INDEX($A:$A,MATCH($D2,$D:$D))),2)<6),(INDEX($A:$A,MATCH($D2,$D:$D,0)):INDEX($A:$A,MATCH($D2,$D:$D)))),"dd mmm yy"))

เป็น

=IF(WEEKDAY(A2,2)>5,"",IF(YEAR(VALUE($E2))=YEAR(VALUE($F2)),IF(MONTH(VALUE($E2))=MONTH(VALUE($F2)),TEXT(INDEX($A:$A,MATCH($D2,$D:$D,0)+(C2>"01")),"dd - ")&TEXT(LOOKUP(2,1/(WEEKDAY((INDEX($A:$A,MATCH($D2,$D:$D,0)):INDEX($A:$A,MATCH($D2,$D:$D))),2)<6),(INDEX($A:$A,MATCH($D2,$D:$D,0)):INDEX($A:$A,MATCH($D2,$D:$D)))),"dd mmm yy"),TEXT(INDEX($A:$A,MATCH($D2,$D:$D,0)+(C2>"01")),"dd mmm - ")&TEXT(LOOKUP(2,1/(WEEKDAY((INDEX($A:$A,MATCH($D2,$D:$D,0)):INDEX($A:$A,MATCH($D2,$D:$D))),2)<6),(INDEX($A:$A,MATCH($D2,$D:$D,0)):INDEX($A:$A,MATCH($D2,$D:$D)))),"dd mmm yy")),"recheck"))

ขอคำแนะนำเพิ่มหน่อยครับ

1. ช่วยอธิบายตรงส่วนที่เป็น LOOKUP(2,1/(WEEKDAY((INDEX($A:$A,MATCH($D2,$D:$D,0)):INDEX($A:$A,MATCH($D2,$D:$D))),2)<6),(INDEX($A:$A,MATCH($D2,$D:$D,0)):INDEX($A:$A,MATCH($D2,$D:$D)))) หน่อยครับ

2. สัปดาห์ที่เป็นช่วงเวลาข้ามปี พอจะมีวิธีให้ได้ค่าที่ถูกต้องไหมครับ เช่น ที่ถูกของสัปดาห์นี้คือ 31 Dec 07 – 04 Jan 08 แต่ตอนนี้ในไฟล์เป็น 31 - 31 Dec 07 และ 01 - 04 Jan 08 ผมเข้าใจว่าเป็นเพราะว่า WEEKDAY() มันนับแค่เฉพาะในปีนั้น ๆ เท่านั้น

[snasui]

กรณีข้ามเดือนลองตามนี้ครับ

ที่ G2 คีย์

=IF(WEEKDAY(A2,2)>5,"",TEXT(INDEX($A:$A,MATCH($D2,$D:$D,0)+(C2>"01")),IF(MONTH(INDEX($A:$A,MATCH($D2,$D:$D,0)+1))<>MONTH(INDEX($A:$A,MATCH($D2,$D:$D)-1)),"dd mmm - ", "dd - "))&TEXT(LOOKUP(2,1/(WEEKDAY((INDEX($A:$A,MATCH($D2,$D:$D,0)):INDEX($A:$A,MATCH($D2,$D:$D))),2)<6),(INDEX($A:$A,MATCH($D2,$D:$D,0)):INDEX($A:$A,MATCH($D2,$D:$D)))),"dd mmm yy"))

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

ผมทำสูตรเปรียบเทียบไว้ที่ K2 เป็นต้นไป เพื่อสามารถเปรียบเทียบกับผลลัพธ์เดิม จะเห็นว่าบรรทัดที่ผมทำสีไว้นั้นให้ผลลัพธ์ที่ต่างกัน

ได้แล้วครับ ขอบคุณครับ
...
ขอคำแนะนำเพิ่มหน่อยครับ

1. ช่วยอธิบายตรงส่วนที่เป็น LOOKUP(2,1/(WEEKDAY((INDEX($A:$A,MATCH($D2,$D:$D,0)):INDEX($A:$A,MATCH($D2,$D:$D))),2)<6),(INDEX($A:$A,MATCH($D2,$D:$D,0)):INDEX($A:$A,MATCH($D2,$D:$D)))) หน่อยครับ

2. สัปดาห์ที่เป็นช่วงเวลาข้ามปี พอจะมีวิธีให้ได้ค่าที่ถูกต้องไหมครับ เช่น ที่ถูกของสัปดาห์นี้คือ 31 Dec 07 – 04 Jan 08 แต่ตอนนี้ในไฟล์เป็น 31 - 31 Dec 07 และ 01 - 04 Jan 08 ผมเข้าใจว่าเป็นเพราะว่า WEEKDAY() มันนับแค่เฉพาะในปีนั้น ๆ เท่านั้น

ตามข้อ 1 ขออธิบายคร่าว ๆ นะครับ เป็นการหาค่าสุดท้ายในช่วงข้อมูลครับ โดยเป็นการหาค่าสุดท้ายในช่วง (INDEX($A:$A,MATCH($D2,$D:$D,0)):INDEX($A:$A,MATCH($D2,$D:$D))) มีเงื่อนไขว่า เมื่อนำ 1 ตั้งหารด้วย (WEEKDAY((INDEX($A:$A,MATCH($D2,$D:$D,0)):INDEX($A:$A,MATCH($D2,$D:$D))),2)<6) แล้วเป็นค่าตัวเลข (ไม่ใช่ค่าผิดพลาด) ค่าตัวเลขสุดท้ายอยู่ที่ใดก็เอาค่าที่ตรงกันใน (INDEX($A:$A,MATCH($D2,$D:$D,0)):INDEX($A:$A,MATCH($D2,$D:$D))) มาแสดง

สูตร Index(...):Index(...) จะแสดงผลเป็นช่วงข้อมูลครับ

[kmb]

2. สัปดาห์ที่เป็นช่วงเวลาข้ามปี พอจะมีวิธีให้ได้ค่าที่ถูกต้องไหมครับ เช่น ที่ถูกของสัปดาห์นี้คือ 31 Dec 07 – 04 Jan 08 แต่ตอนนี้ในไฟล์เป็น 31 - 31 Dec 07 และ 01 - 04 Jan 08 ผมเข้าใจว่าเป็นเพราะว่า WEEKDAY() มันนับแค่เฉพาะในปีนั้น ๆ เท่านั้น

[/quote]

ขอบคุณมากครับ เข้าใจดีขึ้นแล้ว แต่ข้อนี้ล่ะครับ มีคำแนะนำไหมครับ

[snasui]

ผมเข้าใจว่าคีย์ไปเรียบร้อยแล้วแต่สงสัยมือไปโดนแบบคลุมแล้วลบไปโดยบังเอิญ

ตามข้อ 2. ลักษณะข้ามปีจะขึ้นกับตัวเลขในคอลัมน์ D คงต้องเพิ่มเงื่อนไขถึงจะสามารถ Run แบบที่ต้องการได้ หากมีเวลาจะช่วยดูให้อีกทีครับ

[kmb]

ขอบคุณครับ

[snasui]

ผมทำตัวอย่างมาให้กรณี Run แบบข้ามปี ซึ่งได้กระจายออกป็นคอลัมน์ K:N เพื่อให้เข้าใจได้ง่ายขึ้น ดูตัวอยางในไฟล์แนบครับ

[kmb]

ขอบคุณมากครับ จะลองศึกษาดูครับ

[kmb]

ขอสวัสดีปีใหม่นะครับ ขอให้มีความสุขมาก ๆ นะครับ

ขอสอบถามเพิ่มเติมครับ พอเอาไฟล์นี้ส่งทางอีเมล์ไปเครื่องที่ทำงาน เปิดไฟล์ผ่าน Outlook แล้วบางคอลัมน์เป็นภาษาไทยดังในรูปน่ะครับ

Date Format.JPG

แต่ถ้า save ไฟล์แล้วค่อยเปิดไม่เป็นไรคือเป็นภาษาอังกฤษปกติ
ไปดูที่ Regional Setting ก็เป็นตามรูปด้านล่าง ไม่เห็นมีอะไรปกติ

Regional 1.JPG

Regional 2.JPG

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

[snasui]

การเปิดผ่า่น Outlook โปรแกรมอาจจะจำค่าจากเครื่องที่ Save ไปก็ได้ครับ ถ้า Save ลงเครื่องก็จะมั่นใจได้ว่าใช้ Regional and Language ของเครื่องที่เปิด แต่หากว่าเครื่องที่ส่งไปก็ได้กำหนดค่าไว้เป็น English เหมือนกัน อันนี้ผมไม่สามารถตอบได้ครับ เพราะเห็นว่าน่าจะไม่ถูก Logic เนื่องจากค่าที่กำหนดไม่น่าจะถูกเปลี่ยนแปลงค่าใด ๆ ระหว่างการส่งผ่านข้อมูล

[kmb]

ไม่เป็นไรครับ เล่นเอางงมากเลย เครื่องที่บ้านผมใช้ Excel 2010 พอเปิดไฟล์นี้ผ่าน Outlook จากไฟล์ที่แนบจะได้ตามรูปล่าง

Date Format 01.jpg

พอกดตรง Enable editing มันกลายเป็นแบบนี้เลยครับ ไม่ต้อง copy แล้วค่อยเปิดจากเครื่องเหมือนเครื่องที่่ทำงาน

Date Format 02.jpg