Preskoči na glavno vsebino

Kako pretvoriti ure v 8-urne delovne dni v Excelu?

Če imate seznam ur, ki jih morate pretvoriti v 8-urne delovne dni, da boste lahko hitro dobili skupne delovne dni zaposlenega. Kako bi se lahko hitro in enostavno spopadli s tem delom v Excelu?

Pretvorite ure v 8-urne delovne dni s formulo


Pretvorite ure v 8-urne delovne dni s formulo

Če želite urnik spremeniti v običajni delovni dan, lahko uporabite naslednje formule:

Vnesite to formulo: = QUOTIENT (A2,8) & "dni" & ((A2 / 8) -QUOTIENT (A2,8)) * 8 & "ur" v prazno celico, kjer želite dobiti rezultat, in nato povlecite ročico za polnjenje navzdol do celic, v katere želite izpolniti to formulo, in vse urne celice so pretvorjene v 8-urno obliko delovnih dni. Oglejte si posnetek zaslona:

Opombe: Če imate ure v formatu HH: MM: SS, lahko delovni dan dobite na naslednji način:

1. Vnesite to formulo: = A2 * 3 v prazno celico, da postavite rezultat, in nato povlecite ročico za polnjenje navzdol do celic, glejte posnetek zaslona:

2. Nato izberite celice formule in z desno miškino tipko izberite Oblikuj celice iz kontekstnega menija v Oblikuj celice pogovorno okno, kliknite po meri v Kategorija in vnesite 0.00 "dni" v tip besedilno polje, glej posnetek zaslona:

3. In nato kliknite OK , da zaprete pogovorno okno, in dobili boste rezultate, kot jih potrebujete, glejte posnetek zaslona:

Najboljša pisarniška orodja za produktivnost

Priljubljene funkcije: Poiščite, označite ali identificirajte dvojnike   |  Izbriši prazne vrstice   |  Združite stolpce ali celice brez izgube podatkov   |   Krog brez formule ...
Super iskanje: Več kriterijev VLookup    Multiple Value VLookup  |   VLookup na več listih   |   Nejasno iskanje ....
Napredni spustni seznam: Hitro ustvarite spustni seznam   |  Odvisni spustni seznam   |  Večkrat izberite spustni seznam ....
Upravitelj stolpcev: Dodajte določeno število stolpcev  |  Premakni stolpce  |  Preklop stanja vidnosti skritih stolpcev  |  Primerjaj obsege in stolpce ...
Predstavljene funkcije: Mrežni fokus   |  Pogled oblikovanja   |   Velika vrstica formule    Upravitelj delovnih zvezkov in listov   |  Knjižnica virov (Samodejno besedilo)   |  Izbirnik datuma   |  Združite delovne liste   |  Šifriranje/dešifriranje celic    Pošljite e-pošto po seznamu   |  Super filter   |   Poseben filter (filter krepko/ležeče/prečrtano ...) ...
15 najboljših kompletov orodij12 Besedilo Orodja (dodajanje besedila, Odstrani znake,...)   |   50 + Graf Vrste (Gantt Chart,...)   |   40+ Praktično Formule (Izračunajte starost glede na rojstni dan,...)   |   19 vstavljanje Orodja (Vstavite kodo QR, Vstavi sliko s poti,...)   |   12 Pretvorba Orodja (Številke v besede, Pretvorba valut,...)   |   7 Spoji in razdeli Orodja (Napredne kombinirane vrstice, Razdeljene celice,...)   |   ... in več

Napolnite svoje Excelove spretnosti s Kutools za Excel in izkusite učinkovitost kot še nikoli prej. Kutools za Excel ponuja več kot 300 naprednih funkcij za povečanje produktivnosti in prihranek časa.  Kliknite tukaj, če želite pridobiti funkcijo, ki jo najbolj potrebujete...

zavihek kte 201905


Kartica Office prinaša vmesnik z zavihki v Office in poenostavi vaše delo

  • Omogočite urejanje in branje z zavihki v Wordu, Excelu, PowerPointu, Publisher, Access, Visio in Project.
  • Odprite in ustvarite več dokumentov v novih zavihkih istega okna in ne v novih oknih.
  • Poveča vašo produktivnost za 50%in vsak dan zmanjša na stotine klikov miške za vas!
Comments (14)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
good morning

I was able to convert days and hours with the formula but I can't enter the minutes, could I send the formula containing days, hours and minutes?
thanks
This comment was minimized by the moderator on the site
ok.. got it... pls do not approve the comment. thanks.
This comment was minimized by the moderator on the site
i believe something is wrong... 400hrs cannot be 50 working days! Let me explain by using simple formula:
if 40 hours = 1 day, how many days are there in 400hrs?

40 = 1
400 = ?

then it is 400/40 = 10days
This comment was minimized by the moderator on the site
hi, how to sum up all hours in figures?
This comment was minimized by the moderator on the site
How do i calculate days from given hours and exclude weekends for this example above?
This comment was minimized by the moderator on the site
Hi, Sibu,
Maybe the folloewing formula can help you, please try, hope it can help you!
=FLOOR(A1*3,1)&" Days and "&TEXT(SUM(A1,-TIME(8,0,0)*FLOOR(A1*3,1))," h") & " Hours " & MINUTE(A1) & " Minutes "
This comment was minimized by the moderator on the site
Hi I want to do the opposite from 3 days 6 hours 10 minutes have the formula to get 30:10:45
This comment was minimized by the moderator on the site
thanks for this training. but what was looking for is to calculate how many hours been worked for using the start time and the end time.
i.e. start time end time total hours
8:30 5:00

I want you to please show me the formula to calculate the total hours I have worked for within this period of time.

Thanks
This comment was minimized by the moderator on the site
Hello,
To solve your problem, maybe the following article can help you, please try:
https://www.extendoffice.com/documents/excel/3825-excel-calculate-net-work-hours-between-two-dates-excluding-weekends.html
This comment was minimized by the moderator on the site
This formula works but if I want the work days and hours remaining, what is the formula for that?
This comment was minimized by the moderator on the site
What is the formula for vice-versa? As in if i want to convert 1 days, 08:45:23 into 32:45:23
This comment was minimized by the moderator on the site
HI There,

please con you help me with the Excel Formula to Calculate the Standard Net Hours

The Required NET Hours to Work is 7.5 hours per day, i want the net hours to be calculated automatically as 7.5 hours and also show variance if theres' any short hours.

Name Log In Log Out Gross Hours Extra Hours Net Hours
Benny Boli 8:47 16:33 0:00 0:00 0:00
This comment was minimized by the moderator on the site
Can u tell me what is 31.90 hours how many days is that
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