Note: The other languages of the website are Google-translated. Back to English
Logáil isteach  \/ 
x
or
x
Cláraigh  \/ 
x

or

Conas dáta a thiontú go teaghrán uimhreacha nó formáid téacs in Excel?

Sa rang teagaisc seo, tabharfaidh mé isteach roinnt modhanna chun cásanna éagsúla a réiteach maidir le dáta a thiontú go formáid uimhreach nó téacs in Excel.
dáta doc le téacs uimhir 1


1. Tiontaigh an dáta go téacs

Soláthraíonn an chuid seo na modhanna maidir le dáta a thiontú go téacs in Excel.

Chun an dáta a thiontú go téacs, níl uait ach foirmle amháin.

Foirmle: = TEXT (dáta, "date_format")

Tagairt: dáta: an chillín leis an dáta ar mhaith leat a thiontú go téacs
dáta_format: an fhormáid is mian leat a thaispeáint tar éis an dáta a thiontú go téacs

I gcásanna thíos, inseoidh mé duit conas an fhoirmle seo a úsáid.

1.1 Tiontaigh an dáta go téacs i bhformáid mm / dd / bbbb

Mar shampla, an dáta tiontaithe i gCill A3 go mm / dd / bbbb, bain úsáid as an bhfoirmle
= TEXT (A3, "mm / dd / bbbb")
brúigh Iontráil eochair
dáta doc le téacs uimhir 2

Más mian leat an dáta i gCill A8 a thiontú go dd / mm / bbbb, bain úsáid as an bhfoirmle seo
= TEXT (A8, "dd / mm / bbbb")
brúigh Iontráil eochair
dáta doc le téacs uimhir 3

1.2 Tiontaigh an dáta go téacs i bhformáid mmddyyyy nó ddmmyyyy

Más mian leat an dáta i gCill A13 a thiontú go téacs, ach i bhformáid mmddyyyy, clóscríobh an fhoirmle seo
= TEXT (A13, "mmddyyyy")
brúigh Iontráil eochair
dáta doc le téacs uimhir 4

Más mian leat an dáta i gCill A18 a thiontú go téacs i bhformáid ddmmyyyy, clóscríobh an fhoirmle seo
= TEXT (A18, "ddmmyyyy")
brúigh Iontráil eochair
dáta doc le téacs uimhir 5

1.3 Tiontaigh an dáta go téacs i bhformáidí eile

I ndáiríre, cibé formáid téacs is mian leat an dáta a thiontú, clóscríobh an fhormáid atá uait idir luachana san fhoirmle.

Dáta (A3) 12 / 23 / 2019
Foirmle = TEXT (A3, "mm-dd-yyyy") = TEXT (A3, "mm / dd") = TEXT (A3, "dd") = TEXT (A3, "d / m / y")
Toradh 23-12 2019- 23 / 12 23 23 / 12 / 19

Nua!
uirlisí dáta

Réitíonn 16 fheidhm agus gné dáta nua tascanna 90% faoi phróiseáil dáta in Excel duit.

◆ Bliain / míonna / laethanta / seachtainí go dtí seo a dhealú nó a dhealú go dtí seo, cliceáil le haghaidh sonraí.

◆ Cuir nó dealraigh soicind / nóiméad / uaireanta go dtí seo, cliceáil le haghaidh tuilleadh.

◆ Líon deireadh seachtaine / laethanta seachtaine idir dhá dháta. cliceáil le haghaidh tuilleadh.

◆ Bain an t-am ón dáta. cliceáil le haghaidh sonraí.

◆ Am a thiontú go soicind deachúil / munites / uair an chloig. cliceáil le haghaidh sonraí.

◆ Tiontaigh formáidí ildhátaí go formáid dáta caighdeánach na SA. cliceáil le haghaidh sonraí.

Triail saor in aisce 30 lá le lánghníomhaíochtaí, 30 lá aisíocaíocht gan chúis.


2. Tiontaigh an dáta go dtí an uimhir

Sa chuid seo, cuirim modhanna ar fáil maidir le dáta a thiontú go huimhir i bhformáid 5 dhigit i bhformáid mmddyyyy.

2.1 Tiontaigh an dáta go huimhir i bhformáid 5 dhigit

Más mian leat dáta a thiontú go huimhir i bhformáid 5 dhigit, déan mar a leanas iad:

1. Cliceáil ar dheis ag an gcill ina bhfuil an dáta a theastaíonn uait a thiontú go huimhir, agus sa roghchlár cliceáil ar dheis, roghnaigh Cealla Formáid coitianta.
dáta doc le téacs uimhir 6

2. I Cealla Formáid dialóg , faoi Uimhir cluaisín , roghnaigh Go ginearálta ó phána na Catagóir.
dáta doc le téacs uimhir 7

3. cliceáil OK. Tiontaíodh an dáta i gcill roghnaithe go sreangán uimhreacha i bhformáid mmddyyyy.
dáta doc le téacs uimhir 8

2.2 Tiontaigh an dáta go huimhir i bhformáid mmddyyyy nó ddmmyyyy

Más mian leat dáta a thiontú go teaghrán uimhreacha i bhformáid mmddyyyy nó ddmmyyyy, is féidir leat an fheidhm Cealla Formáid a chur i bhfeidhm freisin.

1. Cliceáil ar dheis ag an gcill ina bhfuil an dáta a theastaíonn uait a thiontú go huimhir, agus sa roghchlár cliceáil ar dheis, roghnaigh Cealla Formáid coitianta.

2. Sa Cealla Formáid dialóg, faoi Uimhir tab, roghnaigh An Chustaim ó na Catagóir pána, ansin téigh go dtí an chuid ar dheis, iontráil mmddyyyy isteach sa cineál Bosca téacs.
dáta doc le téacs uimhir 9

3. cliceáil OK. Tiontaíodh an dáta i gcill roghnaithe go sreangán uimhreacha i bhformáid mmddyyyy
dáta doc le téacs uimhir 10

Más mian leat dáta a thiontú go teaghrán uimhreacha i bhformáid eile, is féidir leat an liosta thíos a ghlacadh mar thagairt.

dáta 2 / 23 / 2019
Cill Formáid mar is gnách ndmmyyyy dhmmyy mbhbb yyyymmdd
Taispeáin 23022019 230219 022019 20190223

3. Tiontaigh an dáta go mí / lá / bliain nó formáidí dáta eile le cad a tharlaíonn

Más mian leat dáta go formáidí míosa, lae, bliana nó dáta eile a thiontú go tapa, beidh an Cuir Formáidiú Dáta i bhfeidhm fóntais de Kutools le haghaidh Excel beidh rogha mhaith le húsáid.

Slán a fhágáil le Spondylosis Láimhe Luiche agus Ceirbheacs Anois

Réitíonn 300 uirlis chun cinn de Kutools le haghaidh Excel tascanna Excel 80% i soicindí, tarraingíonn tú as na mílte cliceáil luch.

Déileáil go héasca le 1500 cás oibre, ní gá am a chur amú le réitigh a chuardach, bíodh go leor ama agat chun taitneamh a bhaint as do shaol.

Feabhas a chur ar tháirgiúlacht 80% do 110000+ duine ardéifeachtach gach lá, tú féin san áireamh ar ndóigh.

Gan a bheith cráite ag foirmlí pianmhara agus VBA a thuilleadh, tabhair suaimhneas oibre agus lúcháir oibre d’inchinn.

Triail saor in aisce 30 lá le gnéithe iomlána, airgead 30 lá ar ais gan chúiseanna.

Cruthaíonn Comhlacht Níos Fearr Saol Níos Fearr.

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

1. Roghnaigh na cealla ina bhfuil na dátaí is mian leat a thiontú go formáidí dáta eile.

2. cliceáil Kutools > déanta > Cuir Formáidiú Dáta i bhfeidhm.
dáta doc le téacs uimhir 11

3. Sa Cuir Formáidiú Dáta i bhfeidhm dialóg, roghnaigh an fhormáid dáta is mian leat a úsáid Formáidiú dáta idir an dá linn, tabharfar réamhamharc ceart ar an toradh tiontaithe réamhamharc pane.
dáta doc le téacs uimhir 12

4. cliceáil Ok, ansin athraíodh na dátaí a roghnaigh tú go formáid an dáta a roghnaigh tú.
dáta doc le téacs uimhir 13

Tiontaigh an dáta go formáidiú dáta Aonair iolraí le cad a tharlaíonn

formáidiú dáta a chur i bhfeidhm

Tuilleadh leideanna maidir le comhshó dáta


Íoslódáil comhad samplach

sampla


Uirlisí Táirgeachta Molta

Cluaisín Oifige - Brabhsáil Tabbed, Eagarthóireacht, Bainistiú Doiciméid In Microsoft Office 2019 - 2003 Agus Office 365


cluaisín oifige

Kutools for Excel - Comhcheanglaíonn Níos Mó ná 300 Ardfheidhmeanna agus Uirlisí do Microsoft Excel


cluaisín kutools
cluaisín kutoolsp
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Andreea · 21 days ago
    Hello. I have the date 26.01.2021 in the cell A1 and in another cell I have the formula: =RIGHT(A1,4) to take the year from that date, and it returns “4526” instead of “2021”. Do you know how to solve it? Thank you very much!
  • To post as a guest, your comment is unpublished.
    Laura · 5 months ago
    Hi, I want to convert month to number. For exp: 8 months (date format) ---> "8" as a number or value. I've change its format but I can't count it.
    Because I want to put it to math format, for exp: 8-2 = 6 (8 from the months). But the 8 doesn't work.
    Hope you get it. Thank you for helping!
  • To post as a guest, your comment is unpublished.
    Tom · 1 years ago
    Hi i have a date in "13/02/2020 11:42:09" format and i want in MM/DD/YYYY format
    • To post as a guest, your comment is unpublished.
      Sunny · 1 years ago
      Hi, just select the cell then apply the Text to Columns, in the Text to Columns, choose Delimiter > Space > Date(in the Date format, choose DMY) > Finish.
  • To post as a guest, your comment is unpublished.
    hexenest@gmail.com · 2 years ago
    There is another way, it returns exactly the numeric format that represents the date abbreviated by (/), it would be: '=Substitute(A1;"/";"")
  • To post as a guest, your comment is unpublished.
    Jdog · 2 years ago
    How do I do an IF formula to test which date is older i.e. auto date is 8/1/2019... a different cell is manual entry 8/3/2019. I want to check to confirm the entered date is not older than the auto populated date. something like =IF(((DATEVALUE(A2)>(DATEVALUE(G3)),1,0) DATE VALUE seems to be not considered a number so it fails with a value error.
    • To post as a guest, your comment is unpublished.
      Sunny · 2 years ago
      Hi, Jdog, this article may help you https://www.extendoffice.com/documents/excel/2739-excel-compare-dates-greater-than.html
  • To post as a guest, your comment is unpublished.
    gmsaez · 2 years ago
    1437620400 how can I convert this number in to a date in excel?
    • To post as a guest, your comment is unpublished.
      Sunny · 2 years ago
      Hi, gmsaez, is the number timestamp? If so, please use this formula =(((A1/60)/60)/24)+DATE(1970,1,1), A1 is the number, then format the result as date format. For more details, you can refer to this article https://www.extendoffice.com/documents/excel/2473-excel-timestamp-to-date.html#a3
  • To post as a guest, your comment is unpublished.
    Isma · 2 years ago
    These numbers 1532307209286 were converted by my partner to date 23-07-2018 00:53:29. Do you know how did it?
    • To post as a guest, your comment is unpublished.
      Sunny · 2 years ago
      Hi, Isma, your number is unix timestamp, right? If so, this article can help you. https://www.extendoffice.com/documents/excel/2473-excel-timestamp-to-date.html#a3
    • To post as a guest, your comment is unpublished.
      Tunq Dao · 2 years ago
      https://currentmillis.com/
  • To post as a guest, your comment is unpublished.
    JAVIER ALCANTARA MAR · 2 years ago
    I don't understood!!
  • To post as a guest, your comment is unpublished.
    mat · 2 years ago
    hello can you help me?

    i want to convert 20180107 to 07/01/2018
    • To post as a guest, your comment is unpublished.
      Sunny · 2 years ago
      Hello, mat, you can use this formula =DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))
  • To post as a guest, your comment is unpublished.
    jim · 3 years ago
    I am using a date 12 September 2018 I then convert it to 091218. I would like to use a concatenate function to add a 01 at the end, however when I do this the value keeps going to 43355. Is there any easy way to do this or is this even possible?? thanks in advance
    • To post as a guest, your comment is unpublished.
      Sunny · 3 years ago
      Sorry, Jim, I do not understand your problem, could you upload a screenshot to describe your question?
  • To post as a guest, your comment is unpublished.
    Kristina · 3 years ago
    Hello, I want to convert 1-Apr-20 to 20200401, how can i do it automatically? Thank you.
    • To post as a guest, your comment is unpublished.
      Sunny · 3 years ago
      Hello,Kristina, you just need to format the cells to yyyymmdd in Format cells dialog, more detail please go to https://www.extendoffice.com/documents/excel/4165-excel-convert-mm-dd-yyyy-to-yyyymmdd.html
  • To post as a guest, your comment is unpublished.
    Sasi · 3 years ago
    Hi,

    I want to convert a date like 23/05/2018 to 23052018. Any formula to do that automatically?

    Thank you
    • To post as a guest, your comment is unpublished.
      Kiran · 3 years ago
      =SUBSTITUTE(A1,"/","")
    • To post as a guest, your comment is unpublished.
      Sunny · 3 years ago
      Just use Find and Replace function, in findwhat textbox, type /, leave blank in replacewith textbox, to remove all / from the dates.
  • To post as a guest, your comment is unpublished.
    Lothian · 3 years ago
    Hi,
    I'm trying to convert dates formated as yyyy-mm-dd (2018-01-06) and when entering this function, it displays yyyy-01-dd. I can't make sense of it, can anyone help?
    • To post as a guest, your comment is unpublished.
      Alfador · 3 years ago
      This happened to me as well. I kept getting yyyy-00-dd. It's because I'm in a different country and had to use the local format. =TEXT(A1;"vvvv-kk-pp") worked fine for me once I figured that out. If you right-click: Format Cells: Custom, you should find examples that use the local year, month, and day formats.
    • To post as a guest, your comment is unpublished.
      Sunny · 3 years ago
      You can try to format the date cells as custom, and type yyyy-mm-dd into the Type textbox in Format Cells dialog. If you want to know more detail, this is an article talk about three ways to convert dates to yyyy-mm-dd. https://www.extendoffice.com/documents/excel/3289-excel-convert-date-to-yyyy-mm-dd-format.html
  • To post as a guest, your comment is unpublished.
    Ravindra Kamble · 4 years ago
    want to convert 15/08/2015 into word (Fifteenth August Two Thousand Fifteen) format by using function in excel 2007
    • To post as a guest, your comment is unpublished.
      Sunny · 4 years ago
      Hello, thanks for your leaving message. To convert date to word, here is a VBA code can help you, however, it is too long to splace here. You can go to this article to get the VBA. https://www.extendoffice.com/documents/excel/4694-excel-date-to-words.html
      Hope it does favor.
  • To post as a guest, your comment is unpublished.
    Ari Hayne-Keon · 4 years ago
    Hey I need to find an equation that lets me change the date to a decimal equation used in normal book work

    Eg

    Original Date 25/9/2003

    Starting point date on graph = 2000

    Date= 3+[(31+28+31+30+31+30+31+25)/365]

    = 3.74
    • To post as a guest, your comment is unpublished.
      Dee · 1 years ago
      Today is 7/1/2020. My formula is ="JENN"&TODAY()

      I get JENN44013

      Help