Ide: Lòt lang yo se Google-tradui. Ou ka vizite a English vèsyon nan lyen sa a.
Ouvri yon sesyon
x
or
x
x
enskri
x

or

Kouman kalkile èdtan te travay ak mwens manje midi tan nan Excel?

Kòm yon anplwaye konpayi, nou bezwen ponpe nan ak kout pyen soti chak jou travay, konte total la travay tan ak mwens lè a manje midi nan yon jounen ka ede yo kalkile salè dapre tan an. Pou egzanp, mwen gen dosye sa a chak jou prezans ki gen ladan boutèy demi lit, louvri sesyon ak kòmanse manje midi, fwa fen semèn manje midi nan yon Fèy travay, kounye a, mwen vle kalkile èdtan total yo te travay, men mwens lè manje midi chak jou. Pou fini travay sa a, mwen ka prezante kèk fòmil pou ou.

doc kalkile travay èdtan 1

Kalkile èdtan te travay ak mwens manje midi tan ak fòmil


Ajoute yon kantite ane / mwa / jou / semèn / èdtan / minit / segonn nan dat:

Kutools pou Excel'S Fòmil sèvis piblik gen kèk fòmil souvan itilize, tankou ajoute ane nan dat, kalkile laj ki baze sou anivèsè nesans ak sou sa. Avèk fòmil sa yo abityèl, ou ka byen vit jwenn rezilta a ke ou vle san yo pa sonje nenpòt fòmil. Li plis sou karakteristik sa a ...

doc-count-weeks-two-dates3

Kutools pou Excel: ak plis pase 200 sou la men Excel ajoute-ins, gratis eseye ki pa gen okenn limit nan jou 60. Download ak gratis jijman Koulye a!


flèch ble jarèt dwat Kalkile èdtan te travay ak mwens manje midi tan ak fòmil


Fòmil sa yo ka ede ou kalkile tan travay yon jou ki baze sou dosye tan yo.

1. Antre nan fòmil sa a: =SUM((C2-B2)+(E2-D2))*24 nan yon selil vid bò kote selil dosye tan ou, F2, pou egzanp, gade D ':

nòt: Nan fòmil ki anwo la a: C2 se lè a kòmanse manje midi, B2 se boutèy la nan tan, E2 endike boutèy la soti tan ak D2 se tan nan fen manje midi. Ou ka chanje referans ki baze sou bezwen ou.

doc kalkile travay èdtan 2

2. Lè sa a, trennen manyen la ranpli desann nan selil yo ke ou vle aplike fòmil sa a, ak tout èdtan yo total ki travay eksepte lè a manje midi yo kalkile nan yon fwa. Al gade nan Ekran:

doc kalkile travay èdtan 3

Konsèy: Si gen ouvri sesyon an, ouvri dosye tan ak tan manje midi fiks, (tankou yon èdtan ak trant minit), ou ka aplike fòmil sa a ki senp: = D2-B2-C2 Pou kalkile èdtan yo travay nan yon jounen. Al gade nan Ekran:

doc kalkile travay èdtan 4



Rekòmande Zouti Productivity

Biwo Tab

lò star1 Pote onglè sou lamen pou Excel ak lòt biwo lojisyèl, jis tankou Chrome, Firefox ak nouvo Internet Explorer.

Kutools pou Excel

lò star1 Etonan! Ogmante pwodiktivite ou nan minit 5. Pa bezwen okenn ladrès espesyal, sove de zè de tan chak jou!

lò star1 300 karakteristik nouvo pou Excel, fè Excel anpil fasil ak pwisan:

  • Rantre selilè / ranje / Kolòn san yo pa pèdi done.
  • Konbine ak Konsolide Pliye Plizyè ak Manyèl.
  • Konpare Range, Kopi plizyè chif, Konvèti tèks nan dat, inite ak konvèsyon lajan.
  • Konte pa Koulè, Paging subtotals, Avanse Triye ak Super Filter,
  • Plis Chwazi / Insert / Efase / Tèks / Fòma / Link / Kòmantè / Workbooks / Worksheets Zouti ...

Piki ekran nan Kutools pou 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 · 7 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 · 9 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 · 8 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.