Forum Discussion
Solved
Number to date no longer works
In the past, I could change a date displayed as a number in a csv file to a date using the text to columns method. If I chose data type and the appropriate format DMY, the numbers in the column were ...
- I found the solution. Now it is: Select data, select text to columns, select fixed width, click next 2x and choose the date format in which the data is presented. Then click on finish.
This is different from what I did before. Then it was: Select data, select text to columns and choose a separator (like tab). Then I could choose the date format in the desired view. But that no longer works.
csv store only values not formats. So, when you open csv on excel then excel automatically changes data to its default formats. What do you mean by "It no longer works"? Please explain little more.
- I receive files with the date fields as number yyyymmdd. I want to change that to date with format dd-mm-yyyy. But excel now has a problem with the date recognition.
For example if I want to change a cell with 20220923 to date with format 23-09-2022 and I choose date with ctrl+1 the examplefield shows crosses.
I can now only convert with a formula like =DATE(LEFT(E3,4),MID(E3,5,2),RIGHT(E3,2))- I found the solution. Now it is: Select data, select text to columns, select fixed width, click next 2x and choose the date format in which the data is presented. Then click on finish.
This is different from what I did before. Then it was: Select data, select text to columns and choose a separator (like tab). Then I could choose the date format in the desired view. But that no longer works.