Skip to main content

Conas dátaí a iontráil go héasca gan slashes in Excel?

Generally, when we enter standard dates, we need to enter slashes to separate them, such as MM / DD / BBBB. But in some times, you may want to directly enter dates as sequence numbers, for instances, 01022016, and then the dates can be converted to standard dates formatting as 1/2/2016 as below screenshot shown. Here in this article, I introduce the tricks on easily entering standard dates without slashes in Excel.


mboilgeog cheart gorm saighead Enter dates without slashes with Text to Column

In Excel, you can apply the Text to Columns feature to quickly convert 8-digits sequence numbers to standard dates.

1. Select the 8 digits numbers and click Dáta > Téacs chuig na Colúin. Féach an pictiúr:
doc enter dates without slash 2

2. Sa céim 1 de Tiontaigh Téacs go Draoi na gColúnseiceáil Leithead seasta rogha, féach screenshots:
doc enter dates without slash 3

3. cliceáil Ar Aghaidh > Ar Aghaidh dul go dtí an céim 3 den Draoi, agus seiceáil dáta rogha i Formáid sonraí an cholúin alt, agus roghnaigh MDY from next drop down list, and specify a cell as destination. See screenshot:
doc enter dates without slash 4

4. cliceáil Críochnaigh. Now the sequence numbers have been converted to standard dates.
doc enter dates without slash 5

Leid: For correctly converting to dates, you can convert the numbers to text at first.


mboilgeog cheart gorm saighead Enter dates without slashes with Format Cells function

Also, you can apply the Format Cells feature to convert the sequence number to standard date format.

1. Select the numbers to right click, and select Cealla Formáid ón roghchlár comhthéacs. Féach an pictiúr:
doc enter dates without slash 6

2. Ansin sa Cealla Formáid dialóg, faoi Uimhir tab, cliceáil An Chustaim ó na Catagóir pane, and then go to Type textbox to enter ##"/"##"/"#### sa roinn cheart. Féach an pictiúr:
doc enter dates without slash 7

3. cliceáil OK. And the selected numbers have been converted to standard dates.
doc enter dates without slash 11


mboilgeog cheart gorm saighead Enter dates without slashes with formula

IMoreover, you can apply formulas to convert numbers to date format.

Select a blank cell next to the numbers you want to use, enter this formula =DATE(RIGHT(A9,4),LEFT(A9,IF(LEN(A9) = 8,2,1)),LEFT(RIGHT(A9,6),2)), A9 is the number you want to convert, drag the autofill handle over the cells you want to apply this formula, see screenshot:
doc enter dates without slash 9


mboilgeog cheart gorm saighead Enter dates without slashes with Kutools for Excel

Má tá tú Kutools le haghaidh Excel, is féidir leat a Tiontaigh go dtí seo utility to quickly convert multiple non-standard dates to standard dates.

Kutools le haghaidh Excel, le níos mó ná 300 feidhmeanna úsáideacha, déanann sé do phoist níos éasca. 

Tar éis suiteáil saor in aisce Kutools for Excel, déan mar atá thíos le do thoil:

Select the numbers you want to convert to dates, and click Kutools > Ábhar > Tiontaigh go dtí seo. Féach an pictiúr:
tiontaítear doc kutools go dtí seo 1
tiontaítear doc kutools go dtí seo 2

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 (7)
Rated 4.5 out of 5 · 1 ratings
This comment was minimized by the moderator on the site
excelentes opciones , pero en mi caso yo necesito que las personas involucradas en la captura siempre pongan el formato de fecha correcto les doy un ejemplo de como lo hacen, 080923, 08.09.23, y de esta forma la formula no los reconoce para hacer el calculo de la edad, y he estado buscando como hacer para que mis compañeros capturen bien la fecha, ya sea poniendo un tipo de formato preestablecido donde los obligue a capturar correctamente la fecha o algo con la validacion de datos.

espero me puedan ayudar, de antemano muchas gracias.
This comment was minimized by the moderator on the site
Thank you so much! That was very helpful. I used your 0#"/"##"/"#### to enter dates without slashes and display the 0 in front. Some of the images need to be enlarged to show the ". It was hard to tell if that was an " or an *.
Rated 4.5 out of 5
This comment was minimized by the moderator on the site
比如我在輸入0329就會是本年的3月29日,而不是2029年的3月1 日呢?請問應該如何設定呢?謝謝你。
This comment was minimized by the moderator on the site
Hi, you can use this formula:
=DATE(TEXT(TODAY(),"yyyy"),LEFT(A2,2),RIGHT(A2,2))

A2 is the cell contains the string you want to convert to date.
This comment was minimized by the moderator on the site
How do I format this for the whole column so any new data that is inputted it will automatically convert it to dd/mm/yyyy? the above explains if you already have data in can this be done for any additional data you would like to add?
This comment was minimized by the moderator on the site
Hi, Faatimah, if you want the datat input in the future will be automatically converted to dd/mm/yyyy, firstly, you need to type one data in the column, such as 05062022 in A1, and then select the whole column A, and press Ctrl + 1 to display the Format Cells dialog, type ##"/"##"/"#### into the Custom secitoon textbox and click OK. Now the data entered in column A will be auto converted to dd/mm/yyyy.
This comment was minimized by the moderator on the site
How do I get a leading zero for 1-9 using ##"/"##"/"####?
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations