Skip to main content

Conas líon na laethanta / uaireanta gnó / oibre a chur le dáta in Excel?

B’fhéidir go mbeidh ort roinnt laethanta nó uaireanta gnó a chur le dáta d’fhonn a bheith ar an eolas faoin am cruinn a chríochnóidh tú an tasc le linn an ama oibre. San Airteagal seo, taispeánfaimid modhanna duit chun líon na laethanta nó na n-uaireanta gnó / oibre a chur le dáta in Excel.

Cuir líon na laethanta gnó le dáta leis an bhfoirmle

Cuir líon na n-uaireanta gnó le dáta leis an bhfoirmle

Cuir líon na laethanta gnó le dáta leis an bhfoirmle

Aimsíonn an dáta soláthair 2016/1/5 ar chill A2, más mian leat 12 lá a chur leis ach laethanta oibre san áireamh gan deireadh seachtaine, déan mar a leanas le do thoil.

1. Roghnaigh cill bhán, cuir isteach an fhoirmle = OBAIR (A2,12) isteach sa Bar Foirmle, agus ansin brúigh an Iontráil eochair. Féach an pictiúr:

Ansin gheobhaidh tú an dáta tar éis 12 lá oibre a chur leis.

nótaí:

1. San fhoirmle, is é A2 an chill ina bhfuil an dáta ar mhaith leat laethanta oibre a chur leis, is é 12 an líon laethanta oibre a chuirfidh tú leis an dáta. Athraigh iad de réir mar is gá duit.

2. Leis an bhfoirmle thuas, gheobhaidh tú an toradh gan deireadh seachtaine a áireamh. Ach d’fhéadfadh roinnt laethanta saoire a bheith ann tar éis a ríomh. Más mian leat deireadh seachtaine agus laethanta saoire a eisiamh, cuir an fhoirmle seo i bhfeidhm le do thoil = OBAIR (A2, B2, C2).

doc laethanta uaireanta oibre a chur le dáta 1

San fhoirmle seo, tá an dáta a chuirfidh tú laethanta oibre leis in A2, tá líon na laethanta oibre i B2, agus aimsíonn C2 dáta na saoire.

3. Tar éis duit na foirmlí a chur i bhfeidhm, má fhaigheann tú uimhir 5 dhigit, déan formáid na cille a thiontú go formáid dáta.

Cuir líon na n-uaireanta gnó le dáta leis an bhfoirmle

Má cheaptar go bhfuil dáta agus am tosaigh agat, líon na n-uaireanta oibre is gá a chur leis, amanna tosaigh agus deiridh d’uaireanta oibre agus an saoire a theastaíonn uait a eisiamh mar atá thíos an pictiúr a thaispeántar. Chun líon na n-uaireanta gnó a chur leis an dáta, déan mar a leanas le do thoil.

doc laethanta uaireanta oibre a chur le dáta 1

1. Roghnaigh cill bhán (deir cill C2), cuir an fhoirmle seo a leanas isteach sa bharra foirmle, agus ansin brúigh an Iontráil eochair.

=WORKDAY(A2,INT(B2/8)+IF(TIME(HOUR(A2),MINUTE(A2),SECOND(A2))+TIME(MOD(B2,8),MOD(MOD(B2,8),1)*60,0)>
$F$2,1,0),$G$2:$G$2)+IF(TIME(HOUR(A2),MINUTE(A2),SECOND(A2))+TIME(MOD(B2,8),MOD(MOD(B2,8),1)*60,0)>$F$2,$E$2
+TIME(HOUR(A2),MINUTE(A2),SECOND(A2))+TIME(MOD(B2,8),MOD(MOD(B2,8),1)*60,0)-$F$2,TIME(HOUR(A2),MINUTE(A2),SECOND(A2))
+ AM (MOD (B2,8), MOD (MOD (B2,8), 1) * 60,0))

Ansin is féidir leat an toradh a fheiceáil mar atá thíos an pictiúr a thaispeántar.

doc laethanta uaireanta oibre a chur le dáta 1

nótaí:

1. San fhoirmle, is é A2 an chill ina bhfuil an dáta, tá na huaireanta oibre a chuirfidh tú leis go dtí seo i B2, is é E2 agus F2 am tosaigh agus deireadh do chuid uaireanta oibre, agus is é $ G $ 2 an dáta saoire sonrach. Is féidir leat iad a athrú bunaithe ar do chuid riachtanas.

2. Má fhaigheann tú uimhir tar éis duit an fhoirmle seo a chur i bhfeidhm, athraigh formáid na cille go formáid dáta agus ama.

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 (22)
No ratings yet. Be the first to rate!
TA548
TA548
This comment was minimized by the moderator on the site
I have copy and pasted the formula below from the Add Number Of Business Hours To A Date With Formula step above. What is the equivalent formula where you could start with an end date and work backwards to the start date (instead of starting with the start date to find the end date).

=WORKDAY(A2,INT(B2/8)+IF(TIME(HOUR(A2),MINUTE(A2),SECOND(A2))+TIME(MOD(B2,8),MOD(MOD(B2,8),1)*60,0)>
$F$2,1,0),$G$2:$G$2)+IF(TIME(HOUR(A2),MINUTE(A2),SECOND(A2))+TIME(MOD(B2,8),MOD(MOD(B2,8),1)*60,0)>$F$2,$E$2
+TIME(HOUR(A2),MINUTE(A2),SECOND(A2))+TIME(MOD(B2,8),MOD(MOD(B2,8),1)*60,0)-$F$2,TIME(HOUR(A2),MINUTE(A2),SECOND(A2))
+TIME(MOD(B2,8),MOD(MOD(B2,8),1)*60,0))
Reply
Report
0
0
Joshua Player
Joshua Player
This comment was minimized by the moderator on the site
This should be a simple one for you guys but I am working in a dealership and trying to create a simple formula that will automatically calculate how many working days we have in a month... example (6 days a week, because we work every Saturday but we are closed every Sunday). Thanks in advance for your help.
Reply
0
0
crystal
crystal     Joshua Player
This comment was minimized by the moderator on the site
Hi Joshua Player,
For example, you need to count the number of working days in semptember including every Saturday. You need to do as follows:
1. Enter the start date and the end date of September in different cells, such as the start date 9/1/2022 in B1 and the last date 9/30/2022 in B2.
2. Apply one of the following formulas.
If you don't need to exclude holidays, apply this formula and press the Enter key to get the result:
=SUMPRODUCT(--(TEXT(ROW(INDIRECT(B1&":"&B2)),"dddd")<>"Sunday"))
If you need to exclude holidays (suppose the holiday dates listed in B3:C3), apply this formula and press the Ctrl + Shift + Enter keys to get the result
=SUMPRODUCT(--(TEXT(ROW(INDIRECT(B1&":"&B2)),"dddd")<>"Sunday"))-SUMPRODUCT(--IF((B3:C3>=B1)*(B3:C3<=B2),(TEXT(B3:C3,"dddd")<>"Sunday"), FALSE))
Reply
Report
0
0
Renato
Renato
This comment was minimized by the moderator on the site
Hello Everyone,

Add Number Of Business Hours To A Date With Formula

I am not an expert but it gives me an error, everytime I insert the formula on the C2 cell. I followed all the instructions.
I have already formated all cells to time and date but still get an error saying that the formula contains an error.
Can someone explain or upload or send me an excel file with the formula already prepared?
Thanks in advance
Reply
0
0
crystal
crystal     Renato
This comment was minimized by the moderator on the site
Hi Renato,
Can you provide a screenshot of the formula error you encountered? Here I upload an Excel workbook with the formula prepared. Please download it and have a try.
View Attachments (1 / 5)
Reply
Report
0
0
Renato
Renato     crystal
This comment was minimized by the moderator on the site
Hi Crystal,

Thank you so much! I have downloaded the excel workbook and now it works.
Thanks again!
Reply
0
0
S ABDUL JABBAR
S ABDUL JABBAR
This comment was minimized by the moderator on the site
Good afternoon Sir,

How to get end date and time if i have start day and time together and to add "n" working hours excluding non working hours.

example 1) if start date=02/06/2022 02:00 and to add 4:00 working hours and the working hours are from 08:00 to 18:00 and the non working hours to be excluded are of from 18:00 to 08:00 and the end date should be like 02/06/2022 12:00. what is the formula for this one sir.
2) if start date=02/06/2022 16:00 and to add 4:00 working hours and the working hours are from 08:00 to 18:00 the and the non working hours to be excluded are of from 18:00 to 08:00 and the end date should be like 03/06/2022 10:00. what is the formula for this one sir.

In CCC to be completed time also same formula given sir.

Can u please help me with the formula sir?
Reply
0
0
saadshakil
saadshakil
This comment was minimized by the moderator on the site
How Can I add hours with working date+time. For E.g, This is the time 20/10/21 8:00am and I need to add hours with in working days and I need to add 4 hours(assume), and there are working hours between 08:00 to 05:00 and there are no holidays. How to add?
Reply
Report
0
0
crystal
crystal     saadshakil
This comment was minimized by the moderator on the site
Hi, Supposing your case as shown in the screenshot below, to get a future date and time that falls within specified working hours, please try this formula:<div data-tag="code">=WORKDAY(A2,INT(B2/8)+IF(TIME(HOUR(A2),MINUTE(A2),SECOND(A2))+TIME(MOD(B2,8),MOD(MOD(B2,8),1)*60,0)>$E$2,1,0))+IF(TIME(HOUR(A2),MINUTE(A2),SECOND(A2))+TIME(MOD(B2,8),MOD(MOD(B2,8),1)*60,0)>$E$2,$D$2+TIME(HOUR(A2),MINUTE(A2),SECOND(A2))+TIME(MOD(B2,8),MOD(MOD(B2,8),1)*60,0)-$E$2,TIME(HOUR(A2),MINUTE(A2),SECOND(A2))+TIME(MOD(B2,8),MOD(MOD(B2,8),1)*60,0))
View Attachments (1 / 5)
Reply
Report
0
0
Caysen
Caysen
This comment was minimized by the moderator on the site
the formula adding work hours works mathwise, but it wont display the accurate time, just says 0:00. anyone know why?
Reply
0
0
harish
harish
This comment was minimized by the moderator on the site
can some help me to get a formula for below condition
I am unable to get the time , If i do =WORKDAY.INTL(A2,B2,1) ---Output is : Deal Date: 9/11/2018 0:00, Where the time is not coming up, As time should come with 18:34 PM

A2: Deal Date :9/7/2018 18:34 PM
B2: Days: 2
Output is :New Deal Date: 9/11/2018 0:00

Required Output is 9/11/2018 18:34 PM - Plz help me with the formula!
--------------------
9/7/2018 - Friday , I need to add 2 days excluding weekends, So if i add 2 days to 9/7/2018(Friday ) its Tuesday (9/11/2018) by removing the Sat & Sun.
Reply
0
0
crystal
crystal     harish
This comment was minimized by the moderator on the site
Hi Harish,
Sorry can't help you with that yet. Thanks for your comment.
Reply
Report
0
0
Hash Ann
Hash Ann
This comment was minimized by the moderator on the site
How to add a cell in the excel says that maximum hours per day (i.e., 8 hours) The time calculation to be bound by 8 hours per day. Please help me if we can have the spread sheet cap the calculation at 8.?
Reply
0
0
tiit.perve@gmail.com
This comment was minimized by the moderator on the site
How to get cells in excel formula (H6; H7; H8; H9)
Forma C2 = =WORKDAY(B2;MAX(ROUNDUP((E2-IF((COUNTIF($J$4:$J$91;INT(B2))=0)*(WEEKDAY(B2;2)<=5);IF(MOD(B2;1)>$H$3;0;IF(MOD(B2;1)>=$H$5;(H3-MOD(B2;1))*24;($H$3-MAX(MOD(B2;1);$H$2)-($H$5-MAX(MOD(B2;1);$H$4)))*24));0))/(($H$3-$H$2-($H$5-$H$4))*24);0);0);$J$4:$J$91)+$H$2+(IFERROR(CEILING(MOD((E2-IF((COUNTIF($J$4:$J$91;INT(B2))=0)*(WEEKDAY(B2;2)<=5);IF(MOD(B2;1)>$H$3;0;IF(MOD(B2;1)>=$H$5;($H$3-MOD(B2;1))*24;($H$3-MAX(MOD(B2;1);$H$2)-($H$5-MAX(MOD(B2;1);$H$4)))*24));0))/(($H$3-$H$2-($H$5-$H$4))*24);1)-0,00000001;0,00000001);1)*($H$3-$H$2-($H$5-$H$4))*24+(IFERROR(CEILING(MOD((E2-IF((COUNTIF($J$4:$J$91;INT(B2))=0)*(WEEKDAY(B2;2)<=5);IF(MOD(B2;1)>H3;0;IF(MOD(B2;1)>=$H$5;($H$3-MOD(B2;1))*24;($H$3-MAX(MOD(B2;1);$H$2)-($H$5-MAX(MOD(B2;1);$H$4)))*24));0))/(($H$3-$H$2-($H$5-$H$4))*24);1)-0,00000001;0,00000001);1)*($H$3-$H$2-($H$5-$H$4))*24>($H$4-$H$2)*24)*($H$5-$H$4)*24)/24

and C3 = =WORKDAY(B3;MAX(ROUNDUP((E3-IF((COUNTIF($J$4:$J$91;INT(B3))=0)*(WEEKDAY(B3;2)<=5);IF(MOD(B3;1)>$H$3;0;IF(MOD(B3;1)>=$H$5;(H4-MOD(B3;1))*24;($H$3-MAX(MOD(B3;1);$H$2)-($H$5-MAX(MOD(B3;1);$H$4)))*24));0))/(($H$3-$H$2-($H$5-$H$4))*24);0);0);$J$4:$J$91)+$H$2+(IFERROR(CEILING(MOD((E3-IF((COUNTIF($J$4:$J$91;INT(B3))=0)*(WEEKDAY(B3;2)<=5);IF(MOD(B3;1)>$H$3;0;IF(MOD(B3;1)>=$H$5;($H$3-MOD(B3;1))*24;($H$3-MAX(MOD(B3;1);$H$2)-($H$5-MAX(MOD(B3;1);$H$4)))*24));0))/(($H$3-$H$2-($H$5-$H$4))*24);1)-0,00000001;0,00000001);1)*($H$3-$H$2-($H$5-$H$4))*24+(IFERROR(CEILING(MOD((E3-IF((COUNTIF($J$4:$J$91;INT(B3))=0)*(WEEKDAY(B3;2)<=5);IF(MOD(B3;1)>H4;0;IF(MOD(B3;1)>=$H$5;($H$3-MOD(B3;1))*24;($H$3-MAX(MOD(B3;1);$H$2)-($H$5-MAX(MOD(B3;1);$H$4)))*24));0))/(($H$3-$H$2-($H$5-$H$4))*24);1)-0,00000001;0,00000001);1)*($H$3-$H$2-($H$5-$H$4))*24>($H$4-$H$2)*24)*($H$5-$H$4)*24)/24
But the correct value is in D2 and D3. How to get it?
Reply
Report
0
0
SHAHID
SHAHID
This comment was minimized by the moderator on the site
IF WE HAVE TO WRITE THE SAME FORMULA WITH WORKDAY.INTL THAN HOW TO WRITE IT
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