ไปยังเนื้อหาหลัก

วิธีคำนวณชั่วโมงการทำงานสุทธิระหว่างวันที่สองวันไม่รวมวันหยุดสุดสัปดาห์หรือวันหยุดใน Excel

ในหลาย ๆ บริษัท พนักงานจะได้รับค่าจ้างตามชั่วโมงการทำงาน ในการคำนวณชั่วโมงทำงานสุทธิในหนึ่งวันนั้นง่าย แต่จะคำนวณชั่วโมงสุทธิในช่วงวันที่ได้อย่างไร? สำหรับบทความนี้จะแนะนำสูตรในการคำนวณชั่วโมงการทำงานสุทธิระหว่างวันที่สองวันที่ไม่รวมวันหยุดสุดสัปดาห์และวันหยุดใน Excel

คำนวณวันทำงานไม่รวมวันหยุดสุดสัปดาห์

คำนวณชั่วโมงการทำงานไม่รวมวันหยุดสุดสัปดาห์ / วันหยุด

ลูกศรสีฟ้าฟองขวา คำนวณวันทำงานไม่รวมวันหยุดสุดสัปดาห์

ในส่วนนี้ขอแนะนำสูตรคำนวณวันทำงานระหว่างวันที่สองวันยกเว้นวันหยุดสุดสัปดาห์

1. เลือกเซลล์สองเซลล์ที่คุณจะป้อนเวลาวันที่เริ่มต้นและเวลาวันที่สิ้นสุดแล้วคลิกขวาเพื่อเลือก จัดรูปแบบเซลล์ สร้างเมนูบริบท ดูภาพหน้าจอ:
doc net ชั่วโมงการทำงาน 1

2 ใน จัดรูปแบบเซลล์ โต้ตอบคลิก จำนวน และเลือก ประเพณี รูปแบบ หมวดหมู่ รายการและป้อน m / d / yyyy h: mm เข้าไปใน ชนิดภาพเขียน กล่องข้อความในส่วนด้านขวา ดูภาพหน้าจอ:
doc net ชั่วโมงการทำงาน 2

3 คลิก OK. และป้อนเวลาวันที่เริ่มต้นและเวลาวันที่สิ้นสุดลงในเซลล์ทั้งสองแยกกัน ดูภาพหน้าจอ:
doc net ชั่วโมงการทำงาน 3

4. ในเซลล์ถัดจากสองเซลล์นี้เช่น C13 ให้ป้อนสูตรนี้ =NETWORKDAYS(A13,B13)-1-MOD(A13,1)+MOD(B13,1)และกด เข้าสู่ และคุณจะได้ผลลัพธ์ที่มีรูปแบบที่กำหนดเองเลือกเซลล์ผลลัพธ์แล้วคลิก หน้าแรก และไปที่แท็บ รูปแบบตัวเลข รายการเพื่อเลือกทั่วไปเพื่อจัดรูปแบบเป็นรูปแบบที่ถูกต้อง ดูภาพหน้าจอ:
doc net ชั่วโมงการทำงาน 4

ลูกศรสีฟ้าฟองขวา คำนวณชั่วโมงการทำงานไม่รวมวันหยุดสุดสัปดาห์ / วันหยุด

หากคุณต้องการคำนวณชั่วโมงการทำงานสุทธิโดยไม่รวมวันหยุดสุดสัปดาห์หรือวันหยุดคุณสามารถทำได้ดังต่อไปนี้:

คำนวณชั่วโมงการทำงานสุทธิไม่รวมวันหยุดสุดสัปดาห์

1. เลือกเซลล์สองเซลล์และจัดรูปแบบเป็นรูปแบบที่กำหนดเอง m / d / yyyy h: mm และป้อนวันที่เวลาเริ่มต้นและเวลาวันที่สิ้นสุด ดูภาพหน้าจอ:
doc net ชั่วโมงการทำงาน 5

doc net ชั่วโมงการทำงาน 6

2. และในเซลล์ถัดไปเช่น C2 ให้ป้อนสูตรนี้
=(NETWORKDAYS(A2,B2)-1)*("17:30"-"8:30")+IF(NETWORKDAYS(B2,B2),MEDIAN(MOD(B2,1),"17:30","8:30"),"17:30")-MEDIAN(NETWORKDAYS(A2,A2)*MOD(A2,1),"17:30","8:30"),
กด เข้าสู่ จากนั้นคุณจะได้รับสตริงตัวเลข ดูภาพหน้าจอ:
doc net ชั่วโมงการทำงาน 7

3. คลิกขวาที่สตริงตัวเลขแล้วคลิก จัดรูปแบบเซลล์ จากเมนูบริบทและใน จัดรูปแบบเซลล์ ใหเลือก ประเพณี ฟอร์ม หมวดหมู่ รายการภายใต้ number แล้วป้อนสิ่งนี้ [h]: มม ลงใน Type textbox ดูภาพหน้าจอ:
doc net ชั่วโมงการทำงาน 8

4 คลิก OK. ตอนนี้นับชั่วโมงทำงานสุทธิระหว่างวันที่สองวันที่ไม่รวมวันหยุดสุดสัปดาห์
doc net ชั่วโมงการทำงาน 9

ปลาย: ในสูตร A2 คือเวลาวันที่เริ่มต้น B2 คือเวลาวันที่สิ้นสุด 8:30 น. และ 17:30 น. เป็นเวลาเริ่มต้นและเวลาสิ้นสุดทั่วไปในแต่ละวันคุณสามารถเปลี่ยนแปลงได้ตามต้องการ

คำนวณชั่วโมงการทำงานสุทธิไม่รวมวันหยุดสุดสัปดาห์และวันหยุด

1. เช่นเดียวกับด้านบนให้เลือกสองเซลล์และจัดรูปแบบเป็นรูปแบบที่กำหนดเอง m / d / yyyy h: mmและป้อนวันที่เวลาเริ่มต้นและเวลาวันที่สิ้นสุด
doc net ชั่วโมงการทำงาน 10

2. เลือกเซลล์ว่างและป้อนวันหยุดลงในเซลล์ที่นี่ฉันมีวันหยุด 3 วันและฉันพิมพ์แยกกันใน H1: H3 ดูภาพหน้าจอ:
doc net ชั่วโมงการทำงาน 11

3. เลือกเซลล์ว่างซึ่งจะวางผลการนับเช่น C2
=(NETWORKDAYS.INTL(A2,B2,11,H$1:H$3)-1)*("17:30"-"8:30")+IF(NETWORKDAYS.INTL(B2,B2,11,H$1:H$3),MEDIAN(MOD(B2,1),"8:30","17:30"),"17:30")-MEDIAN(NETWORKDAYS.INTL(A2,A2,11,H$1:H$3)*MOD(A2,1),"8:30","17:30"),
และกด เข้าสู่ คุณจะได้รับสตริงตัวเลขและจัดรูปแบบเป็นรูปแบบที่กำหนดเอง [h]: มม. ดูภาพหน้าจอ:
doc net ชั่วโมงการทำงาน 12

ปลาย: ในสูตร A2 คือเวลาวันที่เริ่มต้น B2 คือเวลาวันที่สิ้นสุด 8:30 น. และ 17:30 น. เป็นเวลาเริ่มต้นและเวลาสิ้นสุดทั่วไปในแต่ละวัน H1: H3 คือเซลล์วันหยุดคุณสามารถเปลี่ยนได้ ตามที่คุณต้องการ

เพิ่มวัน / ปี / เดือน / ชั่วโมง / นาที / วินาทีลงในวันที่และเวลาใน Excel ได้อย่างง่ายดาย
สมมติว่าคุณมีข้อมูลรูปแบบวันที่เวลาในเซลล์และตอนนี้คุณต้องเพิ่มจำนวนวันปีเดือนชั่วโมงนาทีหรือวินาทีลงในวันที่นี้ โดยปกติการใช้สูตรเป็นวิธีแรกสำหรับผู้ใช้ Excel ทุกคน แต่ก็ยากที่จะจำสูตรทั้งหมด ด้วย Kutools สำหรับ Excel's ตัวช่วยวันที่และเวลา ยูทิลิตี้คุณสามารถเพิ่มวันปีเดือนหรือชั่วโมงนาทีหรือวินาทีเป็นเวลาวันที่ได้อย่างง่ายดายยิ่งไปกว่านั้นคุณสามารถคำนวณความแตกต่างของวันที่หรืออายุตามวันเกิดที่กำหนดโดยไม่ต้องจำสูตรเลย คลิกเพื่อทดลองใช้งานฟรีเต็มรูปแบบใน 30 วัน!
doc เพิ่มชั่วโมงนาทีวินาที
 
Kutools for Excel: มีโปรแกรมเสริม Excel ที่มีประโยชน์มากกว่า 300 รายการให้ทดลองใช้ฟรีโดยไม่มีข้อ จำกัด ใน 30 วัน

สุดยอดเครื่องมือเพิ่มผลผลิตในสำนักงาน

🤖 Kutools AI ผู้ช่วย: ปฏิวัติการวิเคราะห์ข้อมูลโดยยึดตาม: การดำเนินการที่ชาญฉลาด   |  สร้างรหัส  |  สร้างสูตรที่กำหนดเอง  |  วิเคราะห์ข้อมูลและสร้างแผนภูมิ  |  เรียกใช้ฟังก์ชัน Kutools...
คุณสมบัติยอดนิยม: ค้นหา เน้น หรือระบุรายการที่ซ้ำกัน   |  ลบแถวว่าง   |  รวมคอลัมน์หรือเซลล์โดยไม่สูญเสียข้อมูล   |   รอบโดยไม่มีสูตร ...
การค้นหาขั้นสูง: VLookup หลายเกณฑ์  |   VLookup หลายค่า  |   VLookup ข้ามหลายแผ่น   |   การค้นหาที่ไม่ชัดเจน ....
รายการแบบเลื่อนลงขั้นสูง: สร้างรายการแบบหล่นลงอย่างรวดเร็ว   |  รายการแบบหล่นลงขึ้นอยู่กับ   |  เลือกหลายรายการแบบหล่นลง ....
ผู้จัดการคอลัมน์: เพิ่มจำนวนคอลัมน์เฉพาะ  |  ย้ายคอลัมน์  |  สลับสถานะการมองเห็นของคอลัมน์ที่ซ่อนอยู่  |  เปรียบเทียบช่วงและคอลัมน์ ...
คุณสมบัติเด่น: กริดโฟกัส   |  มุมมองการออกแบบ   |   บาร์สูตรใหญ่   |  สมุดงานและตัวจัดการชีต   |  ห้องสมุดทรัพยากร (ข้อความอัตโนมัติ)   |  เลือกวันที่   |  รวมแผ่นงาน   |  เข้ารหัส/ถอดรหัสเซลล์   |  ส่งอีเมลตามรายการ   |  ซุปเปอร์ฟิลเตอร์   |   ตัวกรองพิเศษ (กรองตัวหนา/ตัวเอียง/ขีดทับ...) ...
ชุดเครื่องมือ 15 อันดับแรก:  12 ข้อความ เครื่องมือ (เพิ่มข้อความ, ลบอักขระ, ... )   |   50 + แผนภูมิ ประเภท (แผนภูมิ Gantt, ... )   |   40+ ใช้งานได้จริง สูตร (คำนวณอายุตามวันเกิด, ... )   |   19 การแทรก เครื่องมือ (ใส่ QR Code, แทรกรูปภาพจากเส้นทาง, ... )   |   12 การแปลง เครื่องมือ (ตัวเลขเป็นคำ, การแปลงสกุลเงิน, ... )   |   7 ผสานและแยก เครื่องมือ (แถวรวมขั้นสูง, แยกเซลล์, ... )   |   ... และอื่น ๆ

เพิ่มพูนทักษะ Excel ของคุณด้วย Kutools สำหรับ Excel และสัมผัสประสิทธิภาพอย่างที่ไม่เคยมีมาก่อน Kutools สำหรับ Excel เสนอคุณสมบัติขั้นสูงมากกว่า 300 รายการเพื่อเพิ่มประสิทธิภาพและประหยัดเวลา  คลิกที่นี่เพื่อรับคุณสมบัติที่คุณต้องการมากที่สุด...

รายละเอียด

แท็บ Office นำอินเทอร์เฟซแบบแท็บมาที่ Office และทำให้งานของคุณง่ายขึ้นมาก

  • เปิดใช้งานการแก้ไขและอ่านแบบแท็บใน Word, Excel, PowerPoint, ผู้จัดพิมพ์, Access, Visio และโครงการ
  • เปิดและสร้างเอกสารหลายรายการในแท็บใหม่ของหน้าต่างเดียวกันแทนที่จะเป็นในหน้าต่างใหม่
  • เพิ่มประสิทธิภาพการทำงานของคุณ 50% และลดการคลิกเมาส์หลายร้อยครั้งให้คุณทุกวัน!
Comments (67)
No ratings yet. Be the first to rate!
Hugo Pereira
Hugo Pereira
This comment was minimized by the moderator on the site
This formula is very good. Does anyone know how I can convert this to SQL query?
Reply
0
0
Excel Formula
Excel Formula
This comment was minimized by the moderator on the site
it works
how to add lunch break?
Reply
0
0
Sanjeev
Sanjeev
This comment was minimized by the moderator on the site
This is very good, what if the shift time spans over 2 days (start time 17:00 to 02:00 next day)
Reply
0
0
Sameer
Sameer     Sanjeev
This comment was minimized by the moderator on the site
Hi
Have recieved any update regarding for your questions because I am also finding for same
Reply
0
0
JV
JV
This comment was minimized by the moderator on the site
Can anyone help me how the formula would be if the work hours are from 8:00 pm to 5:00 am (20:00 to 5:00)?
Reply
0
0
JV
JV     JV
This comment was minimized by the moderator on the site
Tried the same formula but it's showing negative values.
Reply
0
0
JV
JV     JV
This comment was minimized by the moderator on the site
Anyone need this formula but for graveyard or night shift schedule?
Reply
0
0
Sunny
Sunny
This comment was minimized by the moderator on the site
Why is the Median function used in this formula? what is calculating
Reply
0
0
Pooja .
Pooja .
This comment was minimized by the moderator on the site
Hey, thanks for this formula.

But can we apply this formula for same dates.

For ex,
if start date and time is
" 15/11/20 11:10AM" and end date and time is "15 /11/20 11:25AM"
Reply
0
0
Sunny
Sunny     Pooja .
This comment was minimized by the moderator on the site
Hi, Pooja, use formula (M1 is the start time,M2 is the end time)
=(NETWORKDAYS(M1,M2)-1)*("17:30"-"8:30")+IF(NETWORKDAYS(M2,M2),MEDIAN(MOD(M2,1),"17:30","8:30"),"17:30")-MEDIAN(NETWORKDAYS(M1,M1)*MOD(M1,1),"17:30","8:30")
and format the result cell as time.
Reply
Report
0
0
Pierre
Pierre
This comment was minimized by the moderator on the site
Hi, Sunny, thank you so much, this works perfectly now.
Reply
0
0
Pierre
Pierre
This comment was minimized by the moderator on the site
Hi the formula below works well with me to calculate the tame a task is taking from start to finish excluding a standard weekends of Saturday and Sunday off.
=(NETWORKDAYS(I7,J7)-1)*("18:00"-"8:30")+IF(NETWORKDAYS(J7,J7),MEDIAN(MOD(J7,1),"18:00","8:30"),"18:00")-MEDIAN(NETWORKDAYS(I7,I7)*MOD(I7,1),"18:00","8:30")

However, on Friday we would like to consider 3 working hours only (9:00-12:00), how can I insert it within the formula please? any idea?
Reply
0
0
Sunny
Sunny     Pierre
This comment was minimized by the moderator on the site
Hi, Pierre, I have modified the formula:
=((NETWORKDAYS(A1,B1)-1)*("18:00"-"8:30")+IF(NETWORKDAYS(B1,B1),MEDIAN(MOD(B1,1),"18:00","8:30"),"18:00")-MEDIAN(NETWORKDAYS(A1,A1)*MOD(A1,1),"18:00","8:30"))-INT((WEEKDAY($A$1- 6)-$A$1+$B1)/7)*(("18:00"-"8:30")-("12:00"-"9:00"))
Reply
Report
0
0
Pierre
Pierre     Sunny
This comment was minimized by the moderator on the site
Thank you Sunny, but honestly the updated formula did not give the results properly, not sure if it requires additional adjustment:
Monday - Thursday 8:30-18:00 (working hours)
Friday 9:00-12:00 (working hours)
Saturday - Sunday Off
thank you
Reply
0
0
Sunny
Sunny     Pierre
This comment was minimized by the moderator on the site
Hi, Pierre, I have tested the formula, it works for me. In the formula:
A1 is the start datetime, B1 is the end datetime, and both of the datetime cells are formated as mm/dd/yy hh:mm, then the result you need to format it as time format: 37:30:55.
https://www.extendoffice.com/images/stories/comments/sun-comment/doc-calculate-specific-work-hour.png
https://www.extendoffice.com/images/stories/comments/sun-comment/doc-calculate-specific-work-hour-2.png
Reply
Report
0
0
Nishanth
Nishanth     Sunny
This comment was minimized by the moderator on the site
hi sunny,

I have used the formulla and it is working well except for friday hald day calculation showing in negative hours. Kindly suggest

=((NETWORKDAYS(P9,R9,1)-1)*("15:00"-"07:00")+IF(NETWORKDAYS(R9,R9),MEDIAN(MOD(R9,1),"15:00","07:00"),"15:00")-MEDIAN(NETWORKDAYS(P9,P9)*MOD(P9,1),"15:00","07:00")-INT((WEEKDAY(P9-6)-P9+R9)/7)*(("15:00"-"7:00")-("11:30"-"7:00")))

Start time: 1/12/2024 11:51:02 AM
End Time: 1/12/2024 11:51:13 AM
Result: -3:30:00

Thanks
Nishanth
Reply
0
0
Veronika
Veronika
This comment was minimized by the moderator on the site
Dear all, I would like to ask you for help, I tried this formula for counting working hours between days (without weekends and holidays), but I receive the #Value! error.I formatted the cells as well.
 Start date in A2:  24.11.2021 11:05  <span style="letter-spacing: 0.2px; color: inherit; font-family: inherit; font-style: inherit; font-variant-ligatures: inherit; font-variant-caps: inherit; font-weight: inherit;">   1.12.2021 11:05</span>Workday start in C2:    6:00Workday end  in D2:  18:00Holidays in E2 till E10:
1/1/21
4/2/21
4/5/21
5/1/21
5/13/21
5/24/21
10/3/21
12/25/21
12/26/21
    
I used the following formula:   <span style="letter-spacing: 0.2px; color: inherit; font-family: inherit; font-style: inherit; font-variant-ligatures: inherit; font-variant-caps: inherit; font-weight: inherit;">#Value! error, can you please advise?</span>
Reply
0
0
sandeep
sandeep
This comment was minimized by the moderator on the site
Hello Sir,
My predecessor has set below formula.I am not getting  what is "Holidays[#All]", not getting where he has set this.
Please let me know.....


=ABS(IF([@Priority]<3,settings!$F$2-K3,(((NETWORKDAYS(K3,settings!$F$2,Holidays[#All]))*((settings!$B$3-settings!$B$2)*24)-IF(AND(NETWORKDAYS(K3,K3,settings!$D$2:settings!$D$33)>0,(K3-INT(K3))*24>(settings!$B$2-INT(settings!$B$2))*24,(K3-INT(K3))*24<(settings!$B$3-INT(settings!$B$3))*24),((K3-INT(K3))*24)-((settings!$B$2-INT(settings!$B$2))*24),0)-IF(AND(NETWORKDAYS(settings!$F$2,settings!$F$2,settings!$D$2:settings!$D$33)>0,(settings!$F$2-INT(settings!$F$2))*24<(settings!$B$3-INT(settings!$B$3))*24,(settings!$F$2-INT(settings!$F$2))*24>(settings!$B$2-INT(settings!$B$2))*24),((settings!$B$3-INT(settings!$B$3))*24)-((settings!$F$2-INT(settings!$F$2))*24),0)-IF(AND(NETWORKDAYS(K3,K3,settings!$D$2:settings!$D$33)>0,(K3-INT(K3))*24>(settings!$B$3-INT(settings!$B$3))*24),(settings!$B$3-settings!$B$2)*24,0)-IF(AND(NETWORKDAYS(settings!$F$2,settings!$F$2,settings!$D$2:settings!$D$33)>0,(settings!$F$2-INT(settings!$F$2))*24<(settings!$B$2-INT(settings!$B$2))*24),(settings!$B$3-settings!$B$2)*24,0))/24)))
Reply
Report
0
0
There are no comments posted here yet
Load More
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations