Skip to main content

Conas na huaireanta a oibríodh agus lúide am lóin in Excel a ríomh?

Mar fhostaí cuideachta, teastaíonn puncháil isteach agus puncháil amach gach lá oibre, agus is féidir an t-am iomlán oibre a chomhaireamh agus lúide am lóin an lae cabhrú le tuarastal a ríomh de réir an ama. Mar shampla, tá an taifead tinrimh laethúil seo a leanas agam ina bhfuil logáil isteach, logáil amach agus tús lóin, amanna deireadh lóin i mbileog oibre, anois, ba mhaith liom na huaireanta iomlána a oibrítear a ríomh ach lúide am lóin gach lá. Chun an post seo a chríochnú, is féidir liom roinnt foirmlí a thabhairt isteach duit.

ríomh uaireanta oibre 1

Ríomh na huaireanta a oibríodh agus lúide am lóin le foirmlí

mboilgeog cheart gorm saighead Ríomh na huaireanta a oibríodh agus lúide am lóin le foirmlí

Is féidir leis na foirmlí seo a leanas cabhrú leat am oibre a ríomh in aghaidh an lae bunaithe ar na taifid ama.

1. Iontráil an fhoirmle seo: =SUM((C2-B2)+(E2-D2))*24 isteach i gcill bhán in aice le do chealla taifead ama, F2, mar shampla, féach an pictiúr:

nótaí: San fhoirmle thuas: C2 an t-am tosaigh lóin, B2 an bhfuil an logáil isteach in am, E2 léiríonn sé an t-am logála amach agus D2 an t-am deiridh lóin. Féadfaidh tú na tagairtí a athrú bunaithe ar do riachtanas.

ríomh uaireanta oibre 2

2. Ansin tarraing an láimhseáil líonta síos go dtí na cealla a theastaíonn uait an fhoirmle seo a chur i bhfeidhm, agus ríomhtar na huaireanta iomlána go léir a oibrítear seachas an t-am lóin ag an am céanna. Féach an pictiúr:

ríomh uaireanta oibre 3

Leideanna: Má tá logáil isteach, taifead ama logála amach agus am lóin seasta, (mar uair an chloig agus tríocha nóiméad), is féidir leat an fhoirmle shimplí seo a chur i bhfeidhm: = D2-B2-C2 chun na huaireanta oibre in aghaidh an lae a ríomh. Féach an pictiúr:

ríomh uaireanta oibre 4

Uirlisí Táirgiúlachta Oifige is Fearr

🤖 Kutools AI Aide: anailís sonraí a réabhlóidiú bunaithe ar: Forghníomhú Chliste   |  Gin Cód  |  Cruthaigh Foirmlí Saincheaptha  |  Anailís a dhéanamh ar Sonraí agus Cairteacha a Ghin  |  Feidhmeanna Kutools a agairt...
Gnéithe Coitianta: Faigh, Aibhsigh nó Aithnigh Dúblaigh   |  Scrios Sraitheanna Bána   |  Comhcheangail Colúin nó Cealla gan Sonraí a Chailleadh   |   Babhta gan Foirmle ...
Cuardaigh Super: Ilchritéir VLookup  |   VLookup Illuachanna  |   VLookup Trasna Ilbhileoga   |   Amharc doiléir ....
Liosta anuas Casta: Go tapa Cruthaigh Liosta Anuas   |  Liosta anuas Cleithiúnach   |  Liosta Buail Isteach Ilroghnacha ....
Bainisteoir Colún: Cuir Líon Sonrach Colún leis  |  Colúin Bog  |  Scoránaigh Stádas Infheictheachta na gColún Ceilte  |  Déan comparáid idir Raonta & Colúin ...
Gnéithe Réadmhaoin: Fócas Eangaí   |  Amharc Dearaidh   |   Barra Mór na Foirmle   |  Leabhar Oibre & Bainisteoir Bileog   |  Leabharlann Acmhainní (Uaththéacs)   |  Piocálaí Dáta   |  Comhcheangail Bileoga Oibre   |  Criptigh/Díchriptigh Cealla   |  Seol Ríomhphost trí Liosta   |  Scagaire Super   |   Scagaire Speisialta (scagaire trom/iodálach/stailc tríd...) ...
Barr 15 Uirlisí:  12 Téacs uirlisí (Cuir Téacs, Bain Carachtair,...)   |   50 + Cairt cineálacha (Cairt Gantt,...)   |   40+ Praiticiúil Foirmlí (Ríomh aois bunaithe ar lá breithe,...)   |   19 Insertion uirlisí (Cuir isteach Cód QR, Ionsáigh Pictiúr ón gCosán,...)   |   12 Tiontú uirlisí (Uimhreacha le Focail, Comhshó Airgeadra,...)   |   7 Cumaisc & Scoilt uirlisí (Sraitheanna Comhcheangail Casta, Cealla Scoilt,...)   |   ... agus eile

Supercharge Do Scileanna Excel le Kutools le haghaidh Excel, agus Éifeachtúlacht Taithí Cosúil Ná Roimhe. Kutools le haghaidh Excel Tairiscintí Níos mó ná 300 Ardghnéithe chun Táirgiúlacht a Treisiú agus Sábháil Am.  Cliceáil anseo chun an ghné is mó a theastaíonn uait a fháil ...

Tuairisc

Tugann Tab Oifige comhéadan Tabbed chuig Office, agus Déan Do Obair i bhfad Níos Éasca

  • Cumasaigh eagarthóireacht agus léamh tabbed i Word, Excel, PowerPoint, Foilsitheoir, Rochtain, Visio agus Tionscadal.
  • Oscail agus cruthaigh cáipéisí iolracha i gcluaisíní nua den fhuinneog chéanna, seachas i bhfuinneoga nua.
  • Méadaíonn do tháirgiúlacht 50%, agus laghdaíonn sé na céadta cad a tharlaíonn nuair luch duit gach lá!
Comments (38)
No ratings yet. Be the first to rate!
gabby
gabby
This comment was minimized by the moderator on the site
hi, dont know if you'll be able to help me but for my payroll i pay employees by the hours of actual service not by the time they arrive/ leave. i have three categories 1 hr service, 1.5 hr service and 2 hr service. for each category i pay a different amount. i managed to put this data on excell but its counts how many times they completed each service. like 3x the 1 hr and 1x the 1.5 hr on one day or so on. but i also need it to calculate the total hrs they worked. currently it doesnt count the 0.5. any ideas? thanksss!!!
Reply
0
0
Arun
Arun
This comment was minimized by the moderator on the site
Hi,

How to Count (D71) CONDITIONALLY FORMATTED RED COLOR CELL only (D40:D70)?

Pls guide...

Thanks
Reply
0
0
Wally
Wally
This comment was minimized by the moderator on the site
A2 is my start time at 6pm and B2 is my endtime at 4:00 AM it gives me ###### and format gives me a negative number when its a 10 hour shift. How do I resolve this?
Reply
0
0
skyyang
skyyang     Wally
This comment was minimized by the moderator on the site
Hello, Wally,
To solve your problem, please apply the following formula:
=(B2-A2+(B2<A2))*24


Please have a try, hope it can help you!
Reply
Report
0
0
Friendly_Bald_Guy
Friendly_Bald_Guy
This comment was minimized by the moderator on the site
Hi there,
I have Cell G9 as Start Time (09:00:00) then F9 as Finish Time (18:00:00) and have a formula for working out the time worked (=IF(F9<E9,F9+1,F9)-E9) but would like to add a formula to calculate If time worked is more that 6 hours, to deduct 00:30:00 minutes for a break and calculate the new time.
Please advise what best formula to use for this?
Reply
0
0
skyyang
skyyang     Friendly_Bald_Guy
This comment was minimized by the moderator on the site
Hello,
To solve your problem, please apply the below formula:

=B2-A2-TIME(0,INT((B2-A2)/TIME(6,0,0))*30,0)

B2 is the End time, and A2 is the Start time.
Please try, hope it can help you!
Reply
Report
0
0
Аскар
Аскар
This comment was minimized by the moderator on the site
Добрый день! Подскажите, пожалуйста, у меня есть время прибытия и отбытия, мне нужно ввести формулу сколько на точке пробыл объект и минус 1 час обеда, тоесть если прибыл в 8:00 а отбыл 17:00 итого должно получиться 8 часов
Reply
0
0
skyyang
skyyang     Аскар
This comment was minimized by the moderator on the site
Hello, Аскар,

To solve your problem, please apply the below formula:=(B2-A2)-1/24
B2 is the End time, and A2 is the Start time.
Please try, hope it can help you!
Reply
Report
0
0
GioGio
GioGio
This comment was minimized by the moderator on the site
Ciao, sto cercano una formula o una macro che mi permetta di calcolare le ore di presenza partendo dalle timbrature ma allineate all'orario di lavoro standard.
Per esempio se ho l'orario di lavoro dalle 8:30 alle 12:15 con pausa pranzo e ripresa alle 13.15 fino alle 17:30 (8 ore di lavoro) ma le timbrature sono ad esempio:
Timbratura di Ingresso ore 8:10 con timbratura uscita pausa alle 12:16. timbratura di ingresso dopo la pausa alle 12:50 timbratura di uscita 17:35.

Sicuramente tu potrai aiutarmi. Grazie in anticipo
Reply
0
0
NG
NG
This comment was minimized by the moderator on the site
Thanks for the info on this website and for your help!!
I've followed the thread and have made my excelsheet. I use this formula =SUM((C5-B5)+(E5-D5)) to calculate the total working hours per day. I don't get the right info if I use =SUM((C5-B5)+(E5-D5))*24 as has been suggested in this thread. I'm using "Time format' instead of general. Even if I choose general I still get an odd number when I type in 8:00AM start and finish 5:30 PM.

Now I want to have the monthly working hours so I use =SUM(L5:L35), but instead of getting the total hours and minutes I get hours/minutes that aren't correct. For example if the total amount is supposed to be 150 hours and 32minutes I get a whole different number.

Note: I have the format in hh:mm

I hope you could help me out.
Reply
0
0
skyyang
skyyang     NG
This comment was minimized by the moderator on the site
Hi, NG,
I'm sorry for that this formula can't help you, You'd better to upload your Excel file here if you don't mind, so that we can check where the problem is?
Thank you!
Reply
Report
0
0
NG
NG     skyyang
This comment was minimized by the moderator on the site
Thanks for the reply. How/where do I upload my file? I don't see an attachment possibility.
Reply
0
0
skyyang
skyyang     NG
This comment was minimized by the moderator on the site
Hello, NG,
If there is no "Upload Attachment" box, you should register first, and then the "Upload Attachment" option will be appeared.
To register, please go to the top of the article, and click Resgister button to start.
https://www.extendoffice.com/images/stories/comments/comment-skyyang/doc-register.png
I'm sorry for the inconvenience.
Reply
Report
0
0
Ájucha
Ájucha
This comment was minimized by the moderator on the site
Začátek pracovní doby i konec se vždy liší, jak prosím automaticky odečíst 30 minut bez zadávání začátku a konce přestávky?

Děkuji
Reply
0
0
Ana
Ana
This comment was minimized by the moderator on the site
非常实用！很感激你的帮助！请教你如何计算公式得到员工的总工资总和。
Reply
0
0
Ana Zheng
Ana Zheng
This comment was minimized by the moderator on the site
Very useful! Thanks🤓
Reply
0
0
There are no comments posted here yet
Load More
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations