Skip i'r prif gynnwys

Sut i gyfrifo oriau gwaith net rhwng dau ddyddiad ac eithrio penwythnosau neu wyliau yn Excel?

Mewn llawer o gwmnïau, mae staff yn cael eu talu yn ôl oriau gwaith. Mae'n hawdd cyfrifo'r oriau gwaith net mewn diwrnod, ond beth am gyfrifo oriau net mewn ystod dyddiad? Ar gyfer hynny, mae'r erthygl hon, yn cyflwyno'r fformwlâu ar gyfrifo'r oriau gwaith net rhwng dau ddyddiad ac eithrio'r penwythnosau a gwyliau yn Excel.

Cyfrifwch ddiwrnod gwaith ac eithrio penwythnosau

Cyfrifwch oriau gwaith ac eithrio penwythnosau / gwyliau


swigen dde glas saeth Cyfrifwch ddiwrnod gwaith ac eithrio penwythnosau

Yn y rhan hon, rwy'n cyflwyno'r fformiwla i gyfrifo diwrnod gwaith rhwng dwy amser dyddiad ac eithrio'r penwythnosau.

1. Dewiswch ddwy gell y byddwch yn eu mewnbynnu amser dyddiad cychwyn ac amser gorffen, a chliciwch ar y dde i ddewis Celloedd Fformat ffurfio'r ddewislen cyd-destun. Gweler y screenshot:
oriau gwaith net doc 1

2. Yn y Celloedd Fformat deialog, cliciwch Nifer tab, a dewis Custom ffurfiwch y Categori rhestru, a nodi m / d / bbbb h: mm i mewn i'r math blwch testun yn yr adran dde. Gweler y screenshot:
oriau gwaith net doc 2

3. Cliciwch OK. A nodwch yr amser dyddiad cychwyn a'r amser dyddiad gorffen yn y ddwy gell ar wahân. Gweler y screenshot:
oriau gwaith net doc 3

4. Yn y gell wrth ymyl y ddwy gell hon, mae C13, er enghraifft, yn nodi'r fformiwla hon =NETWORKDAYS(A13,B13)-1-MOD(A13,1)+MOD(B13,1), a'r wasg Rhowch allwedd, a byddwch yn cael y canlyniad gyda fformat arfer, dewiswch y gell canlyniad, a chlicio Hafan tab, ac ewch i'r Fformat Rhif rhestr i ddewis Cyffredinol i'w fformatio fel y fformat cywir. Gweler y screenshot:
oriau gwaith net doc 4


swigen dde glas saeth Cyfrifwch oriau gwaith ac eithrio penwythnosau / gwyliau

Os ydych chi am gyfrifo'r oriau gwaith net ac eithrio penwythnosau neu wyliau, gallwch chi wneud fel bellow:

Cyfrifwch oriau gwaith net ac eithrio penwythnosau

1. Dewiswch ddwy gell a'u fformatio fel fformat arfer m / d / bbbb h: mm, a nodi'r amser dyddiad cychwyn a'r amser dyddiad gorffen. Gweler y screenshot:
oriau gwaith net doc 5

oriau gwaith net doc 6

2. Ac yn y nesaf at gell, C2 er enghraifft, nodwch y fformiwla hon,
=(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"),
wasg Rhowch allwedd, yna cewch linyn rhif. Gweler y screenshot:
oriau gwaith net doc 7

3. Cliciwch ar y dde wrth y llinyn rhif, a chlicio Celloedd Fformat o'r ddewislen cyd-destun, ac i mewn Celloedd Fformat deialog, dewiswch Custom ffurflen Categori rhestr o dan Niferr tab, a nodwch hwn [h]: mm i mewn i flwch testun Math. Gweler y screenshot:
oriau gwaith net doc 8

4. Cliciwch OK. Nawr mae'r oriau gwaith net rhwng dau ddyddiad ac eithrio'r penwythnosau yn cael eu cyfrif.
oriau gwaith net doc 9

Tip: Yn y fformiwla, A2 yw'r amser dyddiad cychwyn, B2 yw'r amser dyddiad gorffen, 8:30 a 17:30 yw'r amser cychwyn cyffredinol a'r amser gorffen ym mhob diwrnod, gallwch eu newid yn ôl yr angen.

Cyfrifwch oriau gwaith net ac eithrio'r penwythnos a'r gwyliau

1. Fel yr un peth ag uchod, dewiswch ddwy gell a'u fformatio fel fformat arfer m / d / bbbb h: mm, a nodi'r amser dyddiad cychwyn a'r amser dyddiad gorffen.
oriau gwaith net doc 10

2. Dewiswch gell wag, a nodi'r dyddiad gwyliau ynddo, yma mae gen i 3 gwyliau ac rwy'n eu teipio ar wahân yn H1: H3. Gweler y screenshot:
oriau gwaith net doc 11

3. Dewiswch gell wag a fydd yn gosod y canlyniad a gyfrifwyd, C2 er enghraifft,
=(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"),
ac yn y wasg Rhowch allwedd, byddwch yn cael llinyn rhif, a'i fformatio fel fformat arferiad [h]: mm. Gweler y screenshot:
oriau gwaith net doc 12

Tip: Yn y fformiwla, A2 yw'r amser dyddiad cychwyn, B2 yw'r amser dyddiad gorffen, 8:30 a 17:30 yw'r amser cychwyn cyffredinol a'r amser gorffen ym mhob diwrnod, H1: H3 yw'r celloedd gwyliau, gallwch eu newid yn ôl yr angen.

Ychwanegwch ddyddiau / blynyddoedd / mis / oriau / munudau / eiliadau yn hawdd i amser yn Excel

Gan dybio bod gennych ddata fformat amser dyddiad mewn cell, ac yn awr mae angen i chi ychwanegu nifer o ddyddiau, blynyddoedd, misoedd, oriau, munudau neu eiliadau at y dyddiad hwn. Fel rheol, defnyddio fformiwla yw'r dull cyntaf ar gyfer holl ddefnyddwyr Excel, ond mae'n anodd cofio pob fformiwla. Gyda Kutools ar gyfer Excel'S Cynorthwyydd Dyddiad ac Amser cyfleustodau, gallwch chi ychwanegu diwrnodau, blynyddoedd, misoedd, neu oriau, munudau neu eiliadau yn hawdd at amser dyddiad, ar ben hynny, gallwch chi grynhoi'r gwahaniaeth dyddiad, neu'r oedran yn seiliedig ar ben-blwydd penodol heb gofio'r fformiwla o gwbl. Cliciwch ar gyfer treial llawn sylw am ddim mewn 30 diwrnod!
doc ychwanegu munud awr yn ail
 
Kutools ar gyfer Excel: gyda mwy na 300 o ychwanegion Excel defnyddiol, am ddim i geisio heb unrhyw gyfyngiad mewn 30 diwrnod.

Offer Cynhyrchiant Swyddfa Gorau

🤖 Kutools AI Aide: Chwyldro dadansoddi data yn seiliedig ar: Cyflawniad Deallus   |  Cynhyrchu Cod  |  Creu Fformiwlâu Personol  |  Dadansoddi Data a Chynhyrchu Siartiau  |  Invoke Swyddogaethau Kutools...
Nodweddion Poblogaidd: Darganfod, Amlygu neu Adnabod Dyblygiadau   |  Dileu Rhesi Gwag   |  Cyfuno Colofnau neu Gelloedd heb Colli Data   |   Rownd heb Fformiwla ...
Super-edrych: VLookup Meini Prawf Lluosog    VLookup Gwerth Lluosog  |   VLookup Ar Draws Taflenni Lluosog   |   Edrych Niwlog ....
Rhestr gwympo Uwch: Creu Rhestr Gollwng yn Gyflym   |  Rhestr Gollwng Dibynnol   |  Rhestr Gollwng Aml-ddewis ....
Rheolwr Colofn: Ychwanegu Nifer Penodol o Golofnau  |  Symud Colofnau  |  Toglo Statws Gwelededd Colofnau Cudd  |  Cymharwch Ystodau a Cholofnau ...
Nodweddion dan Sylw: Ffocws ar y Grid   |  Golwg Dylunio   |   Bar Fformiwla Mawr    Rheolwr Llyfr Gwaith a Thaflen   |  Llyfrgell Adnoddau (Testun Auto)   |  Dewiswr Dyddiad   |  Cyfuno Taflenni Gwaith   |  Amgryptio/Dadgryptio Celloedd    Anfon E-byst trwy Restr   |  Hidlo Super   |   Hidlo Arbennig (hidlo mewn print trwm/italig/strikethrough...) ...
15 Set Offer Gorau12 Testun offer (Ychwanegu Testun, Dileu Cymeriadau,...)   |   50 + Siart Mathau (Siart Gantt,...)   |   40+ Ymarferol Fformiwlâu (Cyfrifwch oedran yn seiliedig ar ben-blwydd,...)   |   19 mewnosod offer (Mewnosod Cod QR, Mewnosod Llun o'r Llwybr,...)   |   12 Trosi offer (Rhifau i Eiriau, Trosi arian cyfred,...)   |   7 Uno a Hollti offer (Rhesi Cyfuno Uwch, Celloedd Hollt,...)   |   ... a mwy

Supercharge Eich Sgiliau Excel gyda Kutools ar gyfer Excel, a Phrofiad Effeithlonrwydd Fel Erioed Erioed. Kutools ar gyfer Excel Yn Cynnig Dros 300 o Nodweddion Uwch i Hybu Cynhyrchiant ac Arbed Amser.  Cliciwch Yma i Gael Y Nodwedd Sydd Ei Angen Y Mwyaf...

Disgrifiad


Mae Office Tab yn dod â rhyngwyneb Tabbed i Office, ac yn Gwneud Eich Gwaith yn Haws o lawer

  • Galluogi golygu a darllen tabbed yn Word, Excel, PowerPoint, Cyhoeddwr, Mynediad, Visio a Phrosiect.
  • Agor a chreu dogfennau lluosog mewn tabiau newydd o'r un ffenestr, yn hytrach nag mewn ffenestri newydd.
  • Yn cynyddu eich cynhyrchiant 50%, ac yn lleihau cannoedd o gliciau llygoden i chi bob dydd!
Comments (67)
No ratings yet. Be the first to rate!
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?
This comment was minimized by the moderator on the site
it works
how to add lunch break?
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)
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
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)?
This comment was minimized by the moderator on the site
Tried the same formula but it's showing negative values.
This comment was minimized by the moderator on the site
Anyone need this formula but for graveyard or night shift schedule?
This comment was minimized by the moderator on the site
Why is the Median function used in this formula? what is calculating
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"
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.
This comment was minimized by the moderator on the site
Hi, Sunny, thank you so much, this works perfectly now.
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?
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"))
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
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
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
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>
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)))
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations