Skip to main content

Conas uimhreacha a thiontú go bliain / mí / lá nó dáta in Excel?

Is féidir linn na laethanta idir dhá dháta in Excel a chomhaireamh go héasca, ach conas líon na laethanta a thaispeáint mar bhliain / mí / lá, mar shampla "1 bhliain 4 mhí 25 láAgus? Cad a tharlaíonn má dhéantar uimhir yyyymmdd a thiontú go gnáthdháta? Tá an t-alt seo ag caint ar uimhreacha a thiontú go bliain / mí / lá nó dátaí in Excel.

Tiontaigh uimhreacha (laethanta) go bliain / mí / lá

Tabharfaidh an modh seo foirmle isteach chun líon na laethanta a thiontú go bliain / mí / lá mar "1 bhliain 4 mhí 25 lá"in Excel. Déan mar a leanas le do thoil:

1. Roghnaigh cill bhán aschuirfidh tú toradh an tiontaithe, agus cuirfidh tú an fhoirmle seo a leanas isteach ann, agus brúigh an Iontráil eochair.

= SONRAÍ (0, A2, "y") & "blianta" & DATEDIF (0, A2, "ym") & "míonna" & DATEDIF (0, A2, "md") & "laethanta"

Nóta: Is é A2 an chillín le líon na laethanta a athróidh tú go bliain / mí / lá.

2. Coinnigh ort ag roghnú cill an tiontaithe, agus tarraing a Láimhseáil Líon isteach chuig an raon de réir mar is gá duit. Agus ansin feicfidh tú go ndéantar na huimhreacha go léir a thiontú go bliain / mí / lá. Féach an pictiúr:

Cliceáil amháin chun na foirmlí go léir a bhaint ach luachanna ríofa a choinneáil ó ilchealla


Kutools le haghaidh Excel - Supercharge Excel le níos mó ná 300 uirlisí riachtanacha. Bain sult as triail iomlán 30-lá SAOR IN AISCE gan aon chárta creidmheasa ag teastáil! Get sé anois

Tiontaigh uimhreacha (laethanta) go bliain / mí / lá le huirlis iontach

D’fhéadfadh sé go mbeadh sé deacair ar úsáideoirí áirithe foirmlí a chur de ghlanmheabhair chun líon na laethanta a thiontú go bliain / mí / laethanta in Excel. Molaim anseo Foirmle Helper Kutools le haghaidh Excel an comhrá a chríochnú go tapa gan na foirmlí pianmhara a mheabhrú.

Kutools le haghaidh Excel - Pacáilte le breis agus 300 uirlis riachtanach le haghaidh Excel. Bain sult as triail iomlán 30-lá SAOR IN AISCE gan aon chárta creidmheasa ag teastáil! Íoslódáil anois!

1. Roghnaigh an chill a gcuirfidh tú toradh an ríofa air, agus cliceáil Kutools > Cúntóir Foirmle > Cúntóir Foirmle chun an ghné a chumasú.

2. Sa dialóg Helper Formulas, roghnaigh le do thoil dáta ó na Cineál Foirmle liosta anuas, cliceáil chun aird a tharraingt Tiontaigh laethanta go lá míosa bliana sa Roghnaigh foirmle bosca liosta, ansin sonraigh an uimhirchill sa Uimhir bosca, agus ar deireadh cliceáil ar an Ok cnaipe.

Anois is é toradh an ríofa aschur sa chill roghnaithe. Más gá, is féidir leat Láimhseáil AutoFill na cille seo a tharraingt chun an fhoirmle seo a chóipeáil chuig cealla eile.

Tiontaigh uimhreacha ginearálta go dtí seo formáidiú an dáta réamhshocraithe

Uaireanta, déanann tú dátaí a chlóscríobh nó a ghreamú in Excel, ach taispeánann na dátaí mar 5 dhigit d’uimhreacha ginearálta. Sa chás seo, is féidir leat 5 dhigit d’uimhreacha ginearálta a thiontú go dátaí leis na modhanna seo a leanas:

Roghnaigh na huimhreacha 5 dhigit, agus cliceáil Baile > uimhir Formáid bosca> Dáta Gearr. Agus ansin feicfidh tú go ndéantar gach uimhir 5 dhigit roghnaithe a thiontú go dátaí le formáidiú dáta réamhshocraithe ag an am céanna.

Tiontaigh uimhreacha ginearálta go dtí seo le formáidiú dáta saincheaptha

Kutools le haghaidh Excel's Cuir Formáidiú Dáta i bhfeidhm is féidir le fóntais cabhrú leat uimhreacha 5 dhigit a thiontú go dátaí go tapa le formáidiú dáta saincheaptha.

Kutools le haghaidh Excel - Pacáilte le breis agus 300 uirlis riachtanach le haghaidh Excel. Bain sult as triail iomlán 30-lá SAOR IN AISCE gan aon chárta creidmheasa ag teastáil! Íoslódáil anois!

1. Roghnaigh na 5 dhigit d’uimhreacha, agus cliceáil Kutools > déanta > Cuir Formáidiú Dáta i bhfeidhm.

2. Sa bhosca dialóige Formáidithe Dáta Iarratas a dhéanamh, roghnaigh an fhormáidiú dáta sonraithe a úsáidfidh tú sa Formáidiú dáta bosca, agus cliceáil ar an Ok cnaipe. Agus ansin feicfidh tú go ndéantar na huimhreacha go léir a thiontú go dátaí le formáidiú dáta sonraithe. Féach an pictiúr:

Cliceáil amháin chun uimhir yyyymmdd a thiontú go dtí seo

Uaireanta, b’fhéidir go bhfaighidh tú roinnt uimhreacha speisialta de yyyymmdd, mar shampla 20151215. Taispeánfaidh an chuid seo bealach éasca duit le cliceáil amháin chun na huimhreacha seo de yyyymmdd a thiontú go gnáthdhátaí ag an Tiontaigh go dtí seo fóntais Kutools le haghaidh Excel.

Kutools le haghaidh Excel - Pacáilte le breis agus 300 uirlis riachtanach le haghaidh Excel. Bain sult as triail iomlán 30-lá SAOR IN AISCE gan aon chárta creidmheasa ag teastáil! Íoslódáil anois!

Roghnaigh na cealla le huimhreacha speisialta de yyyymmdd, agus cliceáil Kutools > Ábhar > Tiontaigh go dtí seo. Féach an pictiúr:

Agus ansin feicfidh tú go ndéantar na huimhreacha roghnaithe go léir a chomhbheartú go gnáthdhátaí. Féach an pictiúr:

Cliceáil ar an Dún cnaipe chun an bosca dialóige Tiontaigh go Dáta a dhúnadh.

Taispeántas: tiontaigh uimhreacha go dtí seo in Excel


Kutools le haghaidh Excel: Breis is 300 uirlis láimhe ar do mhéar! Cuir tús le do thriail saor in aisce 30-lá gan aon srianta gné inniu. Íosluchtaigh Anois!

Earraí gaolmhara:

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 (24)
No ratings yet. Be the first to rate!
Kaan
Kaan
This comment was minimized by the moderator on the site
=YIL(B6)-YIL(A6) & " Yıl " & EĞER(AY(B6)>AY(A6);AY(B6)-AY(A6);AY(A6)-AY(B6)) & " Ay " & EĞER(GÜN(B6)>GÜN(A6);GÜN(B6)-GÜN(A6);GÜN(A6)-GÜN(B6)) & " Gün " bu şekil negatif işareti kaldırabilirsiniz:
Reply
0
0
Gurumoorthy
Gurumoorthy
This comment was minimized by the moderator on the site
Hi i have one doubt could you say please I want to return as 5.6 years in excel how i can write
Reply
0
0
Brent S.
Brent S.
This comment was minimized by the moderator on the site
I'm used the formula at the beginning of this topic to calculate how long my employees have worked so I can setup quarterly progress reviews with them. I have the number generated from a formula that gives me how many days from hire date until current date. What is the change in the formula to go from Hire date to the brake down of year/month/day?
Reply
0
0
Kay
Kay
This comment was minimized by the moderator on the site
In my situation, I wanted years and months if possible; if not, I wanted just months or just days. I ended up with:
=IF(cellcontainingdays="","",IF(DATEDIF(0,cellcontainingdays,"y")=0,"",CONCATENATE(DATEDIF(0,cellcontainingdays,"y")," years")) & IF(DATEDIF(0,cellcontainingdays,"ym")=0,"",IF(AND(DATEDIF(0,cellcontainingdays,"ym")<>0,DATEDIF(0,cellcontainingdays,"y")=0),CONCATENATE(DATEDIF(0,cellcontainingdays,"ym")," months"),CONCATENATE(", ",DATEDIF(0,cellcontainingdays,"ym")," months"))) & IF(DATEDIF(0,cellcontainingdays,"md")=0,"",IF(AND(DATEDIF(0,cellcontainingdays,"md")<>0,DATEDIF(0,cellcontainingdays,"y")=0,DATEDIF(0,cellcontainingdays,"ym")=0),CONCATENATE(DATEDIF(0,cellcontainingdays,"md")," days"),"")))
Reply
0
0
dreojs16
dreojs16
This comment was minimized by the moderator on the site
=IF(DATEDIF(0;A1;"y")=0;"";DATEDIF(0;A1;"y")&" year(s), ") & IF(DATEDIF(0;A1;"ym")=0;"";DATEDIF(0;A1;"ym")&" month(s), ") & IF(DATEDIF(0;A1;"md")=0;"";DATEDIF(0;A1;"md")&" day(s)")
Reply
Report
0
0
Ahmed
Ahmed
This comment was minimized by the moderator on the site
How to handle negative numbers?

it gives #NUM! error.
Reply
0
0
kellytte
kellytte     Ahmed
This comment was minimized by the moderator on the site
Hi Ahmed,

what about changing the negative numbers to positive with ABS function, and then adding minus sign before the whole formula?
Reply
Report
0
0
Imran
Imran
This comment was minimized by the moderator on the site
I love you guys.
Reply
0
0
Kevin Hall
Kevin Hall
This comment was minimized by the moderator on the site
Is there a way to automatically omit 0 values? For example, if the formula returns "0 years, 7 months, 16 days", is there a way for it to show up as "7 months, 16 days" instead?
Reply
0
0
DK
DK     Kevin Hall
This comment was minimized by the moderator on the site
=IFERROR(IF(DATEDIF(0,M2,"y")>0,DATEDIF(0,M2,"y")&" Years ","")&IF(DATEDIF(0,M2,"ym")>0,DATEDIF(0,M2,"ym")&" Months ","")&IF(DATEDIF(0,M2,"md")>0,DATEDIF(0,M2,"md")&" Days",""),"")
Reply
0
0
dreojs16
dreojs16     Kevin Hall
This comment was minimized by the moderator on the site
=IF(DATEDIF(0;A1;"y")=0;"";DATEDIF(0;A1;"y")&" year(s), ") & IF(DATEDIF(0;A1;"ym")=0;"";DATEDIF(0;A1;"ym")&" month(s), ") & IF(DATEDIF(0;A1;"md")=0;"";DATEDIF(0;A1;"md")&" day(s)")
Reply
Report
0
0
DK
DK     dreojs16
This comment was minimized by the moderator on the site
=IFERROR(IF(DATEDIF(0,M2,"y")>0,DATEDIF(0,M2,"y")&" Years ","")&IF(DATEDIF(0,M2,"ym")>0,DATEDIF(0,M2,"ym")&" Months ","")&IF(DATEDIF(0,M2,"md")>0,DATEDIF(0,M2,"md")&" Days",""),"")
Reply
0
0
Mahmud Parvez Uzzal
Mahmud Parvez Uzzal
This comment was minimized by the moderator on the site
=CONCATENATE(IF(INT(A2/365)>0,CONCATENATE(INT(A2/365)," Years "),""),CONCATEN ATE(IF(INT(MOD( A2,365)/30)>0,C ONCATENATE(INT( MOD(A2,365)/30) ," Months "),""),CONCATEN ATE(IF(MOD(MOD( A2,365),30)>0,C ONCATENATE(MOD( MOD(A2,365),30) ," Days "),""),))) My formula is ok, When you will copy the formula it will contain some space in word CONCATENATE Like C ONCATENATE/CONCATEN ATE. Please correct the word concatenate. Then i think it will work.
Reply
0
0
Omer Yousif
Omer Yousif
This comment was minimized by the moderator on the site
hello, thanks for the feedback but it's still not working, can you please send me your email so i can show you the table am doing ?! maybe some mistake is there. my email is . now my final table will contain number of days, like for example after the calculations the result will be (123) which is number of days so i want to convert it into ( year , month , days ). or if you have a formula to calculate the defferance between two dates and show the results as ( x year , x month , x days ) that would be super. waiting for your feedback and thanks.
Reply
0
0
dreojs16
dreojs16     Omer Yousif
This comment was minimized by the moderator on the site
=IF(DATEDIF(A1;A2;"y")=0;"";DATEDIF(A1;A2;"y")&" year(s), ") & IF(DATEDIF(A1;A2;"ym")=0;"";DATEDIF(A1;A2;"ym")&" month(s), ") & IF(DATEDIF(A1;A2;"md")=0;"";DATEDIF(A1;A2;"md")&" day(s)")
Reply
Report
0
0
dreojs16
dreojs16     dreojs16
This comment was minimized by the moderator on the site
change every ; to ,
Reply
Report
0
0
Raven momo
Raven momo     Omer Yousif
This comment was minimized by the moderator on the site
And what table is that ,that you say your doing?
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