สร้างTime Sheetโดยลิ้งค์กับปฏิทิน และเปลี่ยนสีวัน

[peachy-berry]

สวัสดีค่ะอาจารย์ ขอเรียนสอบถามเรื่องการสร้างTime Sheetโดยลิ้งค์กับปฏิทิน และเปลี่ยนสีวัน

1. เลือกเปลี่ยนเดือนแล้วให้วันหยุดเปลี่ยนเป็นสีแดง และระบุวันหยุดตรงช่อง Remarks มีวิธีการทำอย่างไรคะ

2.การแสดงผลในช่อง Actual Working Hours และOver/Less Working Hours

เมื่อใส่เวลาเข้า-ออก เวลาพักเบรค
ช่องActual Working Hours ถ้าทำงาน 8 ชั่วโมง = 8.00
ถ้าเกิน/น้อยกว่า 8 ชั่วโมง จำนวนที่เกินจะไปอยู่ที่ช่องOver/Less Working Hours

มีวิธีการอย่างไรคะ

3. สรุปจำนวนชั่วโมงแยกตามสัปดาห์
อยากให้ลิ้งก์กับลำดับสัปดาห์ในชีทCalendar2019
มีวิธีการอย่างไรคะ

ขอบพระคุณมากค่ะ

[Supachok]

1. เลือกเปลี่ยนเดือนแล้วให้วันหยุดเปลี่ยนเป็นสีแดง และระบุวันหยุดตรงช่อง Remarks มีวิธีการทำอย่างไรคะ

Conditional formatiing > New rule > use a formula > ใส่สูตร
=OR(WEEKDAY(B13,2)=6,WEEKDAY(B13,2)=7,B13=holiday)
เปลี่ยน format ตามต้องการ

อาจต้องไปปรับ define name ของ (holiday) ใน range ที่ให้มามันไม่ refer

[Bo_ry]

1.
B13 ลากลง
=IFERROR(--(A13&$AI$3&$AI$4),"")

Conditional formatting formula ของ =$A$13:$C$43
=NETWORKDAYS($B13,$B13,Holiday2019!$B$2:$B$19)=0

AF13 ลากลง
=IFERROR(INDEX(Holiday2019!$A$2:$A$19,MATCH(B13,Holiday2019!$B$2:$B$19,)),"")

2. ถ้าใส่ In Out time เป็น hh:mm แล้ว Break time เป็น เลข h
Z13 ลากลง
=(V13-T13)*24-X13
AC13 มั้ง
=MAX(0,Z13-8)

3.Z45 ลากไป AC45 ลากลง มั้ง
=SUMPRODUCT(Z$13:AB$43*(($B$13:$B$43-WEEKDAY($B$13:$B$43,2)-(("1jan"&$AI$4)-WEEKDAY("1jan"&$AI$4,2))+7)/7=WEEKNUM(B13)+ROWS(Z$45:Z45)-1))

[peachy-berry]

1. เลือกเปลี่ยนเดือนแล้วให้วันหยุดเปลี่ยนเป็นสีแดง และระบุวันหยุดตรงช่อง Remarks มีวิธีการทำอย่างไรคะ

Conditional formatiing > New rule > use a formula > ใส่สูตร
=OR(WEEKDAY(B13,2)=6,WEEKDAY(B13,2)=7,B13=holiday)
เปลี่ยน format ตามต้องการ

อาจต้องไปปรับ define name ของ (holiday) ใน range ที่ให้มามันไม่ refer

ลองลากคลุมช่องB13:B43 และใส่สูตรอย่างที่อาจารย์ว่า แต่มันไม่รีเฟอตามค่ะ ทั้งที่ไปตั้งชื่อDefine nameแล้วในชีท Holiday2019

[Supachok]

ลองแนบไฟล์มาดูครับ

[peachy-berry]

ลองแนบไฟล์มาดูครับ

[Supachok]

ลองดูใน name manager จะเห็นดังภาพ

1.คือมี holiday 2 ตัว / ให้ลบตัวที่ไม่ใช้ออก
2.คือ scope define name ที่จะใช้งานต้องเลือก Workbook.

[peachy-berry]

ลองดูใน name manager จะเห็นดังภาพ

1.คือมี holiday 2 ตัว / ให้ลบตัวที่ไม่ใช้ออก
2.คือ scope define name ที่จะใช้งานต้องเลือก Workbook.

อาจารย์คะ ลบholidayไปแล้ว 1 ตัว แต่เมื่อเปลี่ยนเดือนที่ช่องAI3 พบว่า..
1) วันที่ในช่อง A13:A43 ไม่เปลี่ยนสีตามไปด้วย แต่การแสดงผลในช่อง B13:B43 ถูกต้อง (รูป2)
2) Error ในช่อง Z45:Z50 และ AC45:AC50 บางเดือนแสดงผลถูกต้อง บางเดือน error ค่ะ(รูป1)

สาเหตุเกิดจากอะไรคะ

[snasui]

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

[peachy-berry]

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

แนบไฟล์เพื่อตรวจสอบเพิ่มเติมค่ะอาจารย์

[Supachok]

1) วันที่ในช่อง A13:A43 ไม่เปลี่ยนสีตามไปด้วย แต่การแสดงผลในช่อง B13:B43 ถูกต้อง (รูป2)

แก้สูตรใน conditional formatting ตามนี้ครับ
=OR(WEEKDAY($B13,2)=6,WEEKDAY($B13,2)=7,$B13=Holiday2019!$B$2:$B$19)

Applied to
=$A$13:$C$43

[peachy-berry]

1) วันที่ในช่อง A13:A43 ไม่เปลี่ยนสีตามไปด้วย แต่การแสดงผลในช่อง B13:B43 ถูกต้อง (รูป2)

แก้สูตรใน conditional formatting ตามนี้ครับ
=OR(WEEKDAY($B13,2)=6,WEEKDAY($B13,2)=7,$B13=Holiday2019!$B$2:$B$19)

Applied to
=$A$13:$C$43

ได้ลองปรับตามแล้วค่ะ แต่ยังไม่เปลี่ยนแปลงค่ะ อยากเรียนถามว่า
Conditional formatting formula ของ =$A$13:$C$43 มี 2 เงื่อนไขหรอคะ
=NETWORKDAYS($B13,$B13,Holiday2019!$B$2:$B$19)=0

=OR(WEEKDAY($B13,2)=6,WEEKDAY($B13,2)=7,$B13=Holiday2019!$B$2:$B$19)

Applied to
=$A$13:$C$43

[peachy-berry]

1) วันที่ในช่อง A13:A43 ไม่เปลี่ยนสีตามไปด้วย แต่การแสดงผลในช่อง B13:B43 ถูกต้อง (รูป2)

แก้สูตรใน conditional formatting ตามนี้ครับ
=OR(WEEKDAY($B13,2)=6,WEEKDAY($B13,2)=7,$B13=Holiday2019!$B$2:$B$19)

Applied to
=$A$13:$C$43

ได้ลองปรับตามแล้วค่ะ แต่ยังไม่เปลี่ยนแปลงค่ะ อยากเรียนถามว่า
Conditional formatting formula ของ =$A$13:$C$43 มี 2 เงื่อนไขหรอคะ
=NETWORKDAYS($B13,$B13,Holiday2019!$B$2:$B$19)=0

=OR(WEEKDAY($B13,2)=6,WEEKDAY($B13,2)=7,$B13=Holiday2019!$B$2:$B$19)

Applied to
=$A$13:$C$43

[Supachok]

=networkday ผมไม่ได้ใช้เพราะจะทำให้ค่าแสดงทับกัน ส่วนตัวและไม่มีสูตรนั้น add เพิ่ม / แต่จริงๆแล้วใช้ได้ดี

ลองดูตามแนบ

[peachy-berry]

=networkday ผมไม่ได้ใช้เพราะจะทำให้ค่าแสดงทับกัน ส่วนตัวและไม่มีสูตรนั้น add เพิ่ม / แต่จริงๆแล้วใช้ได้ดี

ลองดูตามแนบ

เรื่องเปลี่ยนสีตามวันหยุดไม่มีปัญหาแล้วค่ะอาจารย์ ขอบคุณค่ะ
แต่ยังมีปัญหาคือ
1) Error ในช่อง Z45:Z50 และ AC45:AC50
1.1) Error ทั้งหมด
1.2) Error บางส่วน เช่นมี 5Week แต่แสดงผล 4 วีคเองค่ะ

สูตรที่ใช้ช่องZ45:Z51 และ AC45:AC50
=SUMPRODUCT(Z$13:AB$43*(($B$13:$B$43-WEEKDAY($B$13:$B$43,2)-(("1jan"&$AI$4)-WEEKDAY("1jan"&$AI$4,2))+7)/7=WEEKNUM(B13)+ROWS(Z$45:Z45)-1))

2) ในปีต่อๆปี สามารถเพิ่มช่องวันหยุดในชีทHoliday2019 ได้เลยไหมคะ

3) ชีทCalendar2019
ได้ใส่สูตรใสช่อง Conditional Formatting =VLOOKUP(B6,Holiday2019!$B$2:$B$19,1,0)
Applies to
=$B$6:$X$38
และปรับฟอร์แมตสีวันหยุด แต่ระบบปรับให้ถึงเดือนกรกฏาคมเองค่ะ
ไม่ทราบว่าติดปัญหาอะไรคะ
รบกวนขอคำแนะนำด้วยค่ะ ขอบคุณมากค่ะ

[Supachok]

=networkday ผมไม่ได้ใช้เพราะจะทำให้ค่าแสดงทับกัน ส่วนตัวและไม่มีสูตรนั้น add เพิ่ม / แต่จริงๆแล้วใช้ได้ดี

2) ในปีต่อๆปี สามารถเพิ่มช่องวันหยุดในชีทHoliday2019 ได้เลยไหมคะ

3) ชีทCalendar2019
ได้ใส่สูตรใสช่อง Conditional Formatting =VLOOKUP(B6,Holiday2019!$B$2:$B$19,1,0)
Applies to
=$B$6:$X$38
และปรับฟอร์แมตสีวันหยุด แต่ระบบปรับให้ถึงเดือนกรกฏาคมเองค่ะ
ไม่ทราบว่าติดปัญหาอะไรคะ
รบกวนขอคำแนะนำด้วยค่ะ ขอบคุณมากค่ะ

ข้อ 2 เพิ่มได้แต่ตั้งขยาย range ตามไปด้วยครับ
ข้อ3 ชีทCalendar2019 สูตรไม่ติดปัญหาอะไร

ให้แก้ที่ข้อมูล
1. M24 เป็นวันที่ 1/8/2019 ให้ปรับข้อมูลเป็นการคีย์ข้อมูลลงไปตรง
2. m24 = d28+1 ในทุกๆเดือน

[Supachok]

ข้อ3 ชีทCalendar2019 สูตรไม่ติดปัญหาอะไร

month 1,4,7,10 ในวันที่1 ของเดือน
=MAX($R6:$X11)+1

month 2,3,5,6,8,9,11,12 ในวันที่1 ของเดือน
=IF(F10<>"","",E10+1)

[peachy-berry]

ข้อ3 ชีทCalendar2019 สูตรไม่ติดปัญหาอะไร

month 1,4,7,10 ในวันที่1 ของเดือน
=MAX($R6:$X11)+1

month 2,3,5,6,8,9,11,12 ในวันที่1 ของเดือน
=IF(F10<>"","",E10+1)

ข้อ 2 และ 3 เข้าใจแล้วค่ะ ขอบคุณมากค่ะ

[peachy-berry]

=networkday ผมไม่ได้ใช้เพราะจะทำให้ค่าแสดงทับกัน ส่วนตัวและไม่มีสูตรนั้น add เพิ่ม / แต่จริงๆแล้วใช้ได้ดี

ลองดูตามแนบ

เรื่องเปลี่ยนสีตามวันหยุดไม่มีปัญหาแล้วค่ะอาจารย์ ขอบคุณค่ะ
แต่ยังมีปัญหาคือ
1) Error ในช่อง Z45:Z50 และ AC45:AC50
1.1) Error ทั้งหมด
1.2) Error บางส่วน เช่นมี 5Week แต่แสดงผล 4 วีคเองค่ะ

สูตรที่ใช้ช่องZ45:Z51 และ AC45:AC50
=SUMPRODUCT(Z$13:AB$43*(($B$13:$B$43-WEEKDAY($B$13:$B$43,2)-(("1jan"&$AI$4)-WEEKDAY("1jan"&$AI$4,2))+7)/7=WEEKNUM(B13)+ROWS(Z$45:Z45)-1))

2) ในปีต่อๆปี สามารถเพิ่มช่องวันหยุดในชีทHoliday2019 ได้เลยไหมคะ

3) ชีทCalendar2019
ได้ใส่สูตรใสช่อง Conditional Formatting =VLOOKUP(B6,Holiday2019!$B$2:$B$19,1,0)
Applies to
=$B$6:$X$38
และปรับฟอร์แมตสีวันหยุด แต่ระบบปรับให้ถึงเดือนกรกฏาคมเองค่ะ
ไม่ทราบว่าติดปัญหาอะไรคะ
รบกวนขอคำแนะนำด้วยค่ะ ขอบคุณมากค่ะ

อาจารย์คะ ข้อ 1) ยังติดerror อยู่เลยค่ะ ไม่ทราบพอจะแนะนำได้ไหมคะ

[Bo_ry]

ไม่ได้เป็นอาจารย์นะ

1)
Z45
=SUMPRODUCT(Z$13:Z$43*(((0&$B$13:$B$43)-WEEKDAY(0&$B$13:$B$43,2)-(("1jan"&$AI$4)-WEEKDAY("1jan"&$AI$4,2))+7)/7=WEEKNUM(B13)+ROWS(Z$45:Z45)-1))

2) ใช้ Name maneger holiday_list
เป็น dynamic range เพิ่มวันได้เลย
=Holiday2019!$B$2:INDEX(Holiday2019!$B:$B,COUNT(Holiday2019!$B:$B)-1)

3)
เปลี่ยน วันแรกของแต่ละเดือน ให้เป็นวันที่จริง เช่น 1/8/2019
ของเก่าเป็น 1