Kidokezo: Lugha zingine ni Google-Ilitafsiriwa. Unaweza kutembelea English toleo la kiungo hiki.
Ingia
x
or
x
x
Daftari
x

or

Jinsi ya kuhesabu masaa uliofanywa na kupunguza muda wa chakula cha mchana katika Excel?

Kama mfanyakazi wa kampuni, tunahitaji punch na kupiga kila siku ya kazi, kuhesabu muda wa kazi na kupunguza muda wa chakula cha mchana wa siku inaweza kusaidia kuhesabu mshahara kulingana na wakati. Kwa mfano, nina rekodi ya mahudhurio ya kila siku ambayo inaingia, kuingia nje na chakula cha mchana kuanza, nyakati za mwisho za chakula cha mchana katika karatasi, sasa, nataka kuhesabu masaa ya jumla ya kazi lakini kupunguza muda wa chakula cha mchana kila siku. Ili kumaliza kazi hii, ninaweza kuanzisha baadhi ya formula kwa ajili yenu.

doc mahesabu masaa kazi 1

Masaa ya kufanya kazi na kupunguza wakati wa chakula cha mchana na fomu


Ongeza idadi ya miaka / miezi / siku / wiki / saa / dakika / sekunde hadi leo:

Kutools kwa Excel'S Aina huduma ina baadhi ya formula za kawaida, kama vile kuongeza miaka hadi sasa, kuhesabu umri kulingana na kuzaliwa na kadhalika. Kwa kanuni hizi za kawaida, unaweza kupata matokeo ambayo unataka bila kukumbuka aina yoyote. Soma zaidi juu ya kipengele hiki ...

doc-count-wiki-mbili tarehe3

Kutools kwa Excel: na zaidi ya 200 handy Excel inserts, huru kujaribu na hakuna kikomo katika siku 60. Pakua na jaribio la bure Sasa!


Mshale wa bluu wa kulia Masaa ya kufanya kazi na kupunguza wakati wa chakula cha mchana na fomu


Fomu zifuatazo zinaweza kukusaidia kuhesabu muda wa kazi kwa siku kulingana na rekodi za wakati.

1. Ingiza formula hii: =SUM((C2-B2)+(E2-D2))*24 katika seli tupu bila kando ya seli za rekodi zako, F2, kwa mfano, angalia skrini:

Kumbuka: Katika formula iliyo hapo juu: C2 ni wakati wa kuanza chakula cha mchana, B2 ni logi kwa wakati, E2 inaonyesha wakati wa nje na D2 ni wakati wa mwisho wa chakula cha mchana. Unaweza kubadilisha marejeleo kulingana na mahitaji yako.

doc mahesabu masaa kazi 2

2. Kisha gurudisha kushughulikia chini kwenye seli ambazo unataka kutumia fomu hii, na masaa yote ya kazi yaliyofanya kazi isipokuwa wakati wa chakula cha mchana ni mahesabu mara moja. Angalia skrini:

doc mahesabu masaa kazi 3

Tips: Ikiwa unapoingia, rekodi ya muda na rekodi ya muda wa chakula cha mchana, (kama saa moja na dakika thelathini), unaweza kutumia formula hii rahisi: = D2-B2-C2 ili kuhesabu masaa ya kazi kwa siku. Angalia skrini:

doc mahesabu masaa kazi 4



Zilizopendekeza Vifaa vya Uzalishaji

Tabia ya Ofisi

nyota ya dhahabu1 Kuleta tabo za Handy kwa Excel na programu nyingine za Ofisi, kama Chrome, Firefox na Internet Explorer mpya.

Kutools kwa Excel

nyota ya dhahabu1 Kushangaza! Ongeza uzalishaji wako katika dakika ya 5. Hauna haja ujuzi wowote maalum, ila saa mbili kila siku!

nyota ya dhahabu1 Features mpya ya 300 kwa Excel, Fanya Excel Rahisi Rahisi na Nguvu:

  • Unganisha Kiini / Rangi / nguzo bila kupoteza Data.
  • Kuchanganya na Kuunganisha Karatasi nyingi na Vitabu vya Kazi.
  • Linganisha Mipangilio, Nakili Ranges nyingi, Badilisha Nakala hadi Tarehe, Kitengo na Ubadilishaji wa Fedha.
  • Hesabu na Rangi, Vipindi vya Paging, Chagua cha Juu na cha Filter Super,
  • Chagua zaidi / Ingiza / Futa / Nakala / Fomu / Kiungo / Maoni / Vitabu vya Kazi / Vifungu vya Kazi ...

Screen shot ya Kutools kwa Excel

Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Pascal · 6 months ago
    @Sheena:

    Found something laying around that actually solves your question.

    Columns D, E and F are the Start time, End Time and Break.


    Have a table somewhere that explains the breaks that have to be calculated:

    from till Pause
    0:00 6:00 0:00
    6:00 9:00 0:30
    9:00 10:00 0:45

    Then use the following expresssion (note that my reference to the table are on a tab called Anhang):

    =IF(AND(D18=ʺʺ,E18=ʺʺ),ʺʺ,(E18+(D18>E18)-D18)-IF((E18+(D18>E18)-D18)>Anhang!$A$6,IF(F18>Anhang!$C$6,F18,Anhang!$C$6),IF((E18+(D18>E18)-D18)<=Anhang!$B$4,IF(F18>Anhang!$C$4,F18,Anhang!$C$4),IF(F18>Anhang!$C$5,F18,Anhang!$C$5))))
  • To post as a guest, your comment is unpublished.
    Sheena · 8 months ago
    I need help with a formula. So I'm creating schedules, I have a log in and log out time. If that time is over 6 hours there has to be a 30 minute lunch. I want a formula that will calculate that time worked including a lunch IF it's over 6 hours
    • To post as a guest, your comment is unpublished.
      Jonny · 8 months ago
      I too would like the answer to this exact situation!
    • To post as a guest, your comment is unpublished.
      Red · 8 months ago
      Did you get help on this question?
      • To post as a guest, your comment is unpublished.
        Justin · 7 months ago
        I would also like the answer to this. Can it be varied? Example: 12 hour shift workers get an hour break, as do 11 and 10 hour shit workers. However, if you work 8 or 9 hours you get 45 minutes deducted, 6 - 7 hours have 30 minutes deducted and 5 hours have 15 minutes deducted. Any hours worked below 5 do not incur a break.