Question. From the Oracle server, I upload information through the interface, in TXT (there is no other format), in order to load information into Excel, the dates are in the format "YYYY-MM-DD HH24: MI: SS", but when loading into your column on the sheet SOME dates change the format to "DD.MM.YYYY hh: mm: ss", due to the fact that there is an extra space between the date and time. Although the unloading of extra spaces is not. Tell me how to solve this problem so as not to further process this column?

enter image description here

  • due to the fact that between the date and time there is an extra space. Although the unloading of extra spaces is not. There is no space in the unloading, but it appears ... Do not you think that the phrase is somewhat weak-minded? when you load in your column on a sheet, SOME dates change the format to "DD.MM.YYYY hh: mm: ss", due to the fact that there is an extra space between the date and time. Try to find a relationship between the value and the fact of conversion - one space should not affect it so ... again, when downloading from Orakla, try to choose a textual date format such that there is no unnecessary disagreement. - Akina
  • Does the conversion take place when loading dates from txt to Excel ? What is the operation? Try manually opening txt in Excel . Same mistake? Some dates - what? Show an example of the source data and their same after unloading. - vikttur
  • @vikttur; Yes, from txt to Excel. I load via Data - From text - File selection - Delimited (|) - Done. When opening a flat file, everything is correct. There is no extra space. In tht the date looks like 2019-01-31 11:30:31 after 01/31/2019 11:30. , while other dates are in the right format - Andrey Zhuravlev
  • Those. does Excel have a different date format in one column? Strange ... Data - From text - File selection - With delimiters (|) - Done - before Finish go through two steps ( Next-Next ) and specify the Дата data format. Look at the opening field in Excel - are there exactly dates? Check: =ЕЧИСЛО(A1) If FALSE - there is no date in the cell, the text is there - vikttur
  • @vikttur, did so. Opposite the date: GMI. The formula shows TRUE, where there is a format that I do not need and with a space. 01/31/2019 11:30 AM I change the data format to YYYY-MM-DD hh: mm: ss, the dates become parity format. but with an extra space. - Andrey Zhuravlev

1 answer 1

No one bothers to show the data immediately. Would avoid unnecessary correspondence and waste of time, both his own and those who answer.

In vain you put the pluses where it is FALSE - there is no date there. Show the calendar in which there is December 51, 2018, January 48, 2019 ....

Normal dates are displayed in the format specified in Excel in the settings.

=============================

Excel open field to watch - are there exactly dates? Check:

 =ЕЧИСЛО(A1)

If FALSE - there is no date in the cell, there is text.

Verification is simpler (if alignment was not applied in the column before this): stretch the column to a sufficient width. Normal dates should be placed in the cell on the right, the text - on the left.

If the dates remain in text format, look for what is stopping - unnecessary characters, incorrect data ...

  • Zalet ..... That's what I did not notice. Thank you. Now everything is clear what happened to him. - Andrey Zhuravlev