Hoppa till huvudinnehåll

Hur beräknar man nettoarbetstid mellan två datum exklusive helger eller helgdagar i Excel?

I många företag betalas personalen efter arbetstid. Att beräkna nettot arbetstid på en dag är enkelt, men vad sägs om att beräkna nettotimmar i ett datumintervall? För den här artikeln introduceras formlerna för beräkning av nettoarbetstiden mellan två datum exklusive helger och helgdagar i Excel.

Beräkna arbetsdag exkluderar helger

Beräkna arbetstid exkluderar helger / helgdagar


pil blå höger bubbla Beräkna arbetsdag exkluderar helger

I den här delen introducerar jag formeln för att beräkna arbetsdag mellan två datumtider exklusive helger.

1. Välj två celler som du ska mata in startdatum och slutdatum och högerklicka för att välja Formatera celler bilda snabbmenyn. Se skärmdump:
doc netto arbetstid 1

2. I Formatera celler dialog, klicka Antal fliken och välj Custom bilda Kategori lista och ange m / d / åååå h: mm i Typ textruta i höger avsnitt. Se skärmdump:
doc netto arbetstid 2

3. klick OK. Och ange startdatum och slutdatum i de två cellerna separat. Se skärmdump:
doc netto arbetstid 3

4. I cellen bredvid dessa två celler anger C13 till exempel denna formel =NETWORKDAYS(A13,B13)-1-MOD(A13,1)+MOD(B13,1)och tryck på ange -knappen, så får du resultatet med anpassat format, markerar resultatcellen och klickar på Hem fliken och gå till nummer Format för att välja Allmänt för att formatera det som rätt format. Se skärmdump:
doc netto arbetstid 4


pil blå höger bubbla Beräkna arbetstid exkluderar helger / helgdagar

Om du vill beräkna nettoarbetstiden exklusive helger eller helgdagar kan du göra som nedan:

Beräkna netto arbetstid exkluderar helger

1. Välj två celler och formatera dem som anpassat format m / d / åååå h: mm och ange startdatum och slutdatum. Se skärmdump:
doc netto arbetstid 5

doc netto arbetstid 6

2. Och i nästa cell, C2 till exempel, ange denna formel,
=(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"),
tryck ange nyckel, då får du en siffersträng. Se skärmdump:
doc netto arbetstid 7

3. Högerklicka på nummersträngen och klicka Formatera celler från snabbmenyn och i Formatera celler dialogrutan, välj Custom formen Kategori lista under Numbefliken och ange den här [h]: mm i Skriv textruta. Se skärmdump:
doc netto arbetstid 8

4. klick OK. Nu räknas nettoarbetstiden mellan två datum exklusive helger.
doc netto arbetstid 9

Tips: I formeln är A2 startdatum, B2 är slutdatum, 8:30 och 17:30 är den allmänna starttiden och sluttiden för varje dag, du kan ändra dem efter behov.

Beräkna nettoarbetstiden exklusive helger och helgdagar

1. På samma sätt som ovan väljer du två celler och formaterar dem som anpassat format m / d / åååå h: mm, och ange startdatum och slutdatum.
doc netto arbetstid 10

2. Välj en tom cell och ange semesterdatum i den, här har jag 3 helgdagar och jag skriver dem separat i H1: H3. Se skärmdump:
doc netto arbetstid 11

3. Välj en tom cell som placerar det räknade resultatet, t.ex. 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"),
och tryck ange nyckel, du får en siffersträng och formaterar den som anpassat format [h]: mm. Se skärmdump:
doc netto arbetstid 12

Tips: I formeln är A2 startdatum, B2 är slutdatum, 8:30 och 17:30 är den allmänna starttiden och sluttiden i varje dag, H1: H3 är semestercellerna, du kan ändra dem som du behöver.

Lägg enkelt till dagar / år / månad / timmar / minuter / sekunder till en datatid i Excel

Om du antar att du har datumdataformatdata i en cell, och nu måste du lägga till ett antal dagar, år, månader, timmar, minuter eller sekunder till detta datum. Normalt är användning av formel den första metoden för alla Excel-användare, men det är svårt att komma ihåg alla formler. Med Kutools för ExcelÄr Hjälp för datum och tid verktyget kan du enkelt lägga till dagar, år, månader eller timmar, minuter eller sekunder till en datumtid. Dessutom kan du kalkulera datumskillnaden eller åldern baserat på en viss födelsedag utan att komma ihåg formeln alls. Klicka för en fullständig gratis provperiod om 30 dagar!
doc lägg till timme minut sekund
 
Kutools för Excel: med mer än 300 praktiska Excel-tillägg, gratis att prova utan begränsning på 30 dagar.

Bästa kontorsproduktivitetsverktyg

🤖 Kutools AI Aide: Revolutionera dataanalys baserat på: Intelligent utförande   |  Generera kod  |  Skapa anpassade formler  |  Analysera data och generera diagram  |  Anropa Kutools funktioner.
Populära funktioner: Hitta, markera eller identifiera dubbletter   |  Ta bort tomma rader   |  Kombinera kolumner eller celler utan att förlora data   |   Rund utan formel ...
Superuppslag: Flera kriterier VLookup    VLookup med flera värden  |   VSök över flera ark   |   Fuzzy Lookup ....
Avancerad rullgardinslista: Skapa snabbt en rullgardinslista   |  Beroende rullgardinslista   |  Flervals-rullgardinslista ....
Kolumnhanterare: Lägg till ett specifikt antal kolumner  |  Flytta kolumner  |  Växla synlighetsstatus för dolda kolumner  |  Jämför intervall och kolumner ...
Utvalda funktioner: Rutnätsfokus   |  Designvy   |   Stor formelbar    Arbetsbok & Bladhanterare   |  Resursbibliotek (Automatisk text)   |  Datumväljare   |  Kombinera arbetsblad   |  Kryptera/Dekryptera celler    Skicka e-postmeddelanden efter lista   |  Superfilter   |   Specialfilter (filtrera fet/kursiv/genomstruken...) ...
Topp 15 verktygssatser12 text verktyg (lägga till text, Ta bort tecken, ...)   |   50+ Diagram Typer (Gantt Chart, ...)   |   40+ Praktiskt Formler (Beräkna ålder baserat på födelsedag, ...)   |   19 Införande verktyg (Infoga QR-kod, Infoga bild från sökväg, ...)   |   12 Konvertering verktyg (Siffror till ord, Valutaväxling, ...)   |   7 Slå ihop och dela verktyg (Avancerade kombinera rader, Dela celler, ...)   |   ... och mer

Uppgradera dina Excel-färdigheter med Kutools för Excel och upplev effektivitet som aldrig förr. Kutools för Excel erbjuder över 300 avancerade funktioner för att öka produktiviteten och spara tid.  Klicka här för att få den funktion du behöver mest...

Beskrivning


Fliken Office ger ett flikgränssnitt till Office och gör ditt arbete mycket enklare

  • Aktivera flikredigering och läsning i Word, Excel, PowerPoint, Publisher, Access, Visio och Project.
  • Öppna och skapa flera dokument i nya flikar i samma fönster, snarare än i nya fönster.
  • Ökar din produktivitet med 50 % och minskar hundratals musklick för dig varje dag!
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