Page 1 of 2
สร้างTime Sheetโดยลิ้งค์กับปฏิทิน และเปลี่ยนสีวัน
Posted: Tue Jan 29, 2019 12:34 pm
by 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
มีวิธีการอย่างไรคะ
ขอบพระคุณมากค่ะ
Re: สร้างTime Sheetโดยลิ้งค์กับปฏิทิน และเปลี่ยนสีวัน
Posted: Tue Jan 29, 2019 1:57 pm
by 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
Re: สร้างTime Sheetโดยลิ้งค์กับปฏิทิน และเปลี่ยนสีวัน
Posted: Tue Jan 29, 2019 2:08 pm
by 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))
Re: สร้างTime Sheetโดยลิ้งค์กับปฏิทิน และเปลี่ยนสีวัน
Posted: Tue Jan 29, 2019 3:10 pm
by peachy-berry
Supachok wrote: Tue Jan 29, 2019 1:57 pm
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
Re: สร้างTime Sheetโดยลิ้งค์กับปฏิทิน และเปลี่ยนสีวัน
Posted: Tue Jan 29, 2019 3:13 pm
by Supachok
ลองแนบไฟล์มาดูครับ
Re: สร้างTime Sheetโดยลิ้งค์กับปฏิทิน และเปลี่ยนสีวัน
Posted: Tue Jan 29, 2019 3:28 pm
by peachy-berry
Supachok wrote: Tue Jan 29, 2019 3:13 pm
ลองแนบไฟล์มาดูครับ
Re: สร้างTime Sheetโดยลิ้งค์กับปฏิทิน และเปลี่ยนสีวัน
Posted: Tue Jan 29, 2019 3:41 pm
by Supachok
ลองดูใน name manager จะเห็นดังภาพ
1.คือมี holiday 2 ตัว / ให้ลบตัวที่ไม่ใช้ออก
2.คือ scope define name ที่จะใช้งานต้องเลือก Workbook.
Re: สร้างTime Sheetโดยลิ้งค์กับปฏิทิน และเปลี่ยนสีวัน
Posted: Tue Jan 29, 2019 5:09 pm
by peachy-berry
Supachok wrote: Tue Jan 29, 2019 3:41 pm
ลองดูใน 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)
สาเหตุเกิดจากอะไรคะ
Re: สร้างTime Sheetโดยลิ้งค์กับปฏิทิน และเปลี่ยนสีวัน
Posted: Tue Jan 29, 2019 7:05 pm
by snasui
แนบไฟล์ล่าสุดมาด้วยจะได้ช่วยดูต่อไปจากนั้นครับ
Re: สร้างTime Sheetโดยลิ้งค์กับปฏิทิน และเปลี่ยนสีวัน
Posted: Wed Jan 30, 2019 8:44 am
by peachy-berry
snasui wrote: Tue Jan 29, 2019 7:05 pm
แนบไฟล์ล่าสุดมาด้วยจะได้ช่วยดูต่อไปจากนั้นครับ
แนบไฟล์เพื่อตรวจสอบเพิ่มเติมค่ะอาจารย์
Re: สร้างTime Sheetโดยลิ้งค์กับปฏิทิน และเปลี่ยนสีวัน
Posted: Wed Jan 30, 2019 9:03 am
by 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
Re: สร้างTime Sheetโดยลิ้งค์กับปฏิทิน และเปลี่ยนสีวัน
Posted: Wed Jan 30, 2019 9:52 am
by peachy-berry
Supachok wrote: Wed Jan 30, 2019 9:03 am
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
Re: สร้างTime Sheetโดยลิ้งค์กับปฏิทิน และเปลี่ยนสีวัน
Posted: Wed Jan 30, 2019 9:52 am
by peachy-berry
peachy-berry wrote: Wed Jan 30, 2019 9:52 am
Supachok wrote: Wed Jan 30, 2019 9:03 am
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
Re: สร้างTime Sheetโดยลิ้งค์กับปฏิทิน และเปลี่ยนสีวัน
Posted: Wed Jan 30, 2019 10:06 am
by Supachok
=networkday ผมไม่ได้ใช้เพราะจะทำให้ค่าแสดงทับกัน ส่วนตัวและไม่มีสูตรนั้น add เพิ่ม / แต่จริงๆแล้วใช้ได้ดี
ลองดูตามแนบ
Re: สร้างTime Sheetโดยลิ้งค์กับปฏิทิน และเปลี่ยนสีวัน
Posted: Wed Jan 30, 2019 11:13 am
by peachy-berry
Supachok wrote: Wed Jan 30, 2019 10:06 am
=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
และปรับฟอร์แมตสีวันหยุด แต่ระบบปรับให้ถึงเดือนกรกฏาคมเองค่ะ
ไม่ทราบว่าติดปัญหาอะไรคะ
รบกวนขอคำแนะนำด้วยค่ะ ขอบคุณมากค่ะ
Re: สร้างTime Sheetโดยลิ้งค์กับปฏิทิน และเปลี่ยนสีวัน
Posted: Wed Jan 30, 2019 11:47 am
by Supachok
peachy-berry wrote: Wed Jan 30, 2019 11:13 am
Supachok wrote: Wed Jan 30, 2019 10:06 am
=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 ในทุกๆเดือน
Re: สร้างTime Sheetโดยลิ้งค์กับปฏิทิน และเปลี่ยนสีวัน
Posted: Wed Jan 30, 2019 11:59 am
by 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)
Re: สร้างTime Sheetโดยลิ้งค์กับปฏิทิน และเปลี่ยนสีวัน
Posted: Wed Jan 30, 2019 2:02 pm
by peachy-berry
Supachok wrote: Wed Jan 30, 2019 11:59 am
ข้อ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 เข้าใจแล้วค่ะ ขอบคุณมากค่ะ
Re: สร้างTime Sheetโดยลิ้งค์กับปฏิทิน และเปลี่ยนสีวัน
Posted: Wed Jan 30, 2019 3:40 pm
by peachy-berry
peachy-berry wrote: Wed Jan 30, 2019 11:13 am
Supachok wrote: Wed Jan 30, 2019 10:06 am
=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 อยู่เลยค่ะ ไม่ทราบพอจะแนะนำได้ไหมคะ
Re: สร้างTime Sheetโดยลิ้งค์กับปฏิทิน และเปลี่ยนสีวัน
Posted: Wed Jan 30, 2019 4:39 pm
by 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