Skip to main content

Conas an bhliain, an mhí agus an lá a bhaint nó a fháil ón liosta dáta in Excel?

Le haghaidh liosta dáta, an bhfuil a fhios agat conas an uimhir bliana, mí agus lá a bhaint nó a fháil? Féach an pictiúr thíos. San Airteagal seo, taispeánfaimid duit foirmlí maidir le huimhir bliana, míosa agus lae a fháil ar leithligh ó liosta dáta in Excel.

Sliocht / faigh an bhliain, an mhí agus an lá ón liosta dáta in Excel

Sliocht uimhir na bliana
Sliocht uimhir na míosa
Sliocht uimhir an lae

Sliocht / faigh an bhliain, an mhí agus an lá ón liosta dáta in Excel

Tóg thíos an liosta dáta mar shampla, más mian leat na huimhreacha bliana, míosa agus lae a fháil ón liosta seo, déan mar a leanas céim ar chéim.

Sliocht uimhir na bliana

1. Roghnaigh cill bhán chun uimhir na bliana a aimsiú, mar shampla cill B2.

2. Cóipeáil agus greamaigh an fhoirmle = BLIAIN (A2) isteach sa Bharra Foirmle, ansin brúigh Iontráil eochair. Tarraing an Láimhseáil Líon isteach go dtí an raon atá uait chun uimhir na bliana ar fad a fháil ón liosta.

Sliocht uimhir na míosa

Taispeánann an chuid seo duit an fhoirmle chun uimhir na míosa a fháil ón liosta.

1. Roghnaigh cill bán, foirmle a chóipeáil agus a ghreamú MÍ (A2) isteach sa Bharra Foirmle agus brúigh an Iontráil eochair.

2. Tarraing an Láimhseáil Líon isteach go dtí an raon atá uait.

Ansin gheobhaidh tú uimhreacha míosa an liosta dáta.

Sliocht uimhir an lae

Tá an fhoirmle uimhreacha lae a fháil chomh simplí leis na foirmlí thuas. Déan mar a leanas le do thoil.

Cóipeáil agus greamaigh foirmle LÁ (A2) isteach i gcill bhán D2 agus brúigh Iontráil eochair. Ansin tarraing an Láimhseáil Líon isteach go dtí an raon chun na huimhreacha lae go léir a bhaint den liosta dáta tagartha.

Anois baintear na huimhreacha bliana, míosa agus lae ón liosta dáta mar a thaispeántar an pictiúr thuas.

Athraigh formáidiú dáta go héasca sa raon dáta roghnaithe in Excel

Kutools le haghaidh Excel's Cuir Formáidiú Dáta i bhfeidhm cuidíonn fóntais leat gach formáid dáta a athrú go formáid dáta sonraithe i raon dáta roghnaithe in Excel.
Íoslódáil rian iomlán saor in aisce 30 lá de Kutools le haghaidh Excel anois!

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 (16)
No ratings yet. Be the first to rate!
CESAR22
CESAR22
This comment was minimized by the moderator on the site
EN MI CASO QUE TIENE 2022-07-01T00:00:01-06:00 PERO YO LO UNICO QUE NECESITO ES EL AÑO EL MES Y EL DIA

COMO PODRIA APLICAR A LA FORMULA
Reply
0
0
Danni
Danni     CESAR22
This comment was minimized by the moderator on the site
Hola Cesar22,

Lo que podes hacer es lo siguiente:
Si la informacion esta en A1
-=TEXT(LEFT(A1,10),"dd mm yyyy")

Esto saca la fecha de toda la otra informacion y te da el dia (dd), el mes (mm) y el año (yy).

Buena suerte
Reply
0
0
Ansonica Campher
Ansonica Campher
This comment was minimized by the moderator on the site
Hi, i have tried and these formulas do not seem to working for what i intend or understand. I need to extract the day and month from a cell.

Ex. 1955-03-08 (current data) to 8 Mar (required data)

Please could someone assist.
Reply
0
0
crystal
crystal     Ansonica Campher
This comment was minimized by the moderator on the site
Hi Ansonica Campher,
The following formula can do you a favor. Please give it a try.
=TEXT(A1,"dd mmm")
Reply
Report
0
0
Brittany
Brittany
This comment was minimized by the moderator on the site
HELP. I have a list of Payment dates, (8/1/16, 9/1/16, 10/1/16, + 30 years). How do I find the current month/year? Im trying to have my formula find the current month/year and grab the data from a different column from the row that the current month is in.
Reply
0
0
crystal
crystal     Brittany
This comment was minimized by the moderator on the site
Hi Brittany,
Supposing all dates are in column B and the current date is in F1. You need to create a helper column, enter the following formula into the first cell of that column (the cell should be in the same row as the first date in column B), press Enter to get the first result. Then drag its AutoFill Handle down to get the rest of the results.
If the result comes TRUE, it means that the date is the same month and year as the current date.
=MONTH(B1)&YEAR(B1)=MONTH($F$1)&YEAR($F$1)
Reply
Report
0
0
rheen
rheen
This comment was minimized by the moderator on the site
this website help me to find what ive been searching all this time.. many thanks..
Reply
0
0
frank
frank
This comment was minimized by the moderator on the site
This is stupid. Anyone who comes to this website wants the day of the month to populate into a cell! Anyone can plug in a random number into cell A2 and the click into cell B2 and insert the formula =A2. That's not what anyone is looking for that comes to this page. People want the cell to populate with the DAY OF THE MONTH!!!! This information and website is USELESS!!!
Reply
0
0
Charles
Charles     frank
This comment was minimized by the moderator on the site
You wanted the actual day of the week or month?? Day of the week would be...

=TEXT(B2,"dddd") for full day

=TEXT(B704,"ddd") for 3 letter day like Mon, Tue, Wed etc
Reply
0
0
Andrei
Andrei     Charles
This comment was minimized by the moderator on the site
That's amazing, thanks Charles, did not really seen this one coming.
Reply
0
0
Arun Kumar
Arun Kumar     Charles
This comment was minimized by the moderator on the site
i got what i need from this text function. Thank you
Reply
0
0
Khalysto
Khalysto     frank
This comment was minimized by the moderator on the site
Did you actually read the site? The formula =DAY(A2) would extract the day of the month from whatever date you had entered in cell A2. How is that not what you're asking for?
Reply
0
0
Andy
Andy     Khalysto
This comment was minimized by the moderator on the site
No, that would extract the day number, not the actual day. I believe frank means he wants to see MON, TUE, WED etc.
Reply
0
0
acat
acat
This comment was minimized by the moderator on the site
=MONTH(A2) drops the leading zero for single digit months. Having that cell formatted as Text does not solve. How to keep the leading zero?
Reply
0
0
Sonu Kumar Singh
Sonu Kumar Singh     acat
This comment was minimized by the moderator on the site
=text(A2, "mmm") for month name in abbreviations


=text(A2, "mmmm") for full month name
Reply
0
0
rheen
rheen     Sonu Kumar Singh
This comment was minimized by the moderator on the site
Thank u Sonu, u answered my question as well..
Reply
0
0
There are no comments posted here yet
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations