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

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

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

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

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

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

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

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

2 ใน จัดรูปแบบเซลล์ โต้ตอบคลิก จำนวน และเลือก ประเพณี รูปแบบ Category รายการและป้อน 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)และกด เข้าสู่ และคุณจะได้ผลลัพธ์ที่มีรูปแบบที่กำหนดเองเลือกเซลล์ผลลัพธ์แล้วคลิก Home และไปที่แท็บ รูปแบบตัวเลข รายการเพื่อเลือกทั่วไปเพื่อจัดรูปแบบเป็นรูปแบบที่ถูกต้อง ดูภาพหน้าจอ:
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. คลิกขวาที่สตริงตัวเลขแล้วคลิก จัดรูปแบบเซลล์ จากเมนูบริบทและใน จัดรูปแบบเซลล์ ใหเลือก ประเพณี ฟอร์ม Category รายการภายใต้ 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 for Excel's ตัวช่วยวันที่และเวลา ยูทิลิตี้คุณสามารถเพิ่มวันปีเดือนหรือชั่วโมงนาทีหรือวินาทีเป็นเวลาวันที่ได้อย่างง่ายดายยิ่งไปกว่านั้นคุณสามารถคำนวณความแตกต่างของวันที่หรืออายุตามวันเกิดที่กำหนดโดยไม่ต้องจำสูตรเลย คลิกเพื่อทดลองใช้ฟรีเต็มรูปแบบใน 30 วัน!
doc เพิ่มชั่วโมงนาทีวินาที
 
Kutools for Excel: พร้อม Add-in ของ Excel ที่ใช้งานสะดวกกว่า 300 รายการ ทดลองใช้ฟรีโดยไม่มีข้อจำกัด 30 วัน

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

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

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

kte แท็บ 201905

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

  • เปิดใช้งานการแก้ไขและอ่านแบบแท็บใน Word, Excel, PowerPoint, ผู้จัดพิมพ์, Access, Visio และโครงการ
  • เปิดและสร้างเอกสารหลายรายการในแท็บใหม่ของหน้าต่างเดียวกันแทนที่จะเป็นในหน้าต่างใหม่
  • เพิ่มประสิทธิภาพการทำงานของคุณ 50% และลดการคลิกเมาส์หลายร้อยครั้งให้คุณทุกวัน!
Comments (63)
No ratings yet. Be the first to rate!
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
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
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
Sandeep
Sandeep
This comment was minimized by the moderator on the site
I have one formula for ageing previously done by my collogue, he is now not working with usPlease let us know how this formula is working....

=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
0
0
vishnu singh
vishnu singh
This comment was minimized by the moderator on the site
I want to calculate next working day on the basis of date and time.After 17:00, date should show next working day exclude Sunday and holiday
Example for dates and time
28-08-2021 16:59
28-08-2021 17:00
28-08-2021 17:20
29-08-2021 17:15

Holiday list
22-Aug-21
30-Aug-21
02-Oct-21
Reply
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