Hi I have several date fields created in an Excel file with the formatting of yyyy-dd-mm and yyyy-dd-mm hh:mm. I need to save as a CSV file but lose the formatting when re-opening the file. Any suggestions please to overcome this please. Thanks
6
-
2If you want to retrieve data from a CSV in a certain format (in your case your date), you'll need to import it versus just opening the file. You can start with Data -> Get & Transform Data -> From Text/CSV. This is basically a power query transformation.– gns100Commented Feb 29 at 16:01
-
1Can you add another column, interpreting those string fields as Excel date-time? Then when saved in CSV, perhaps Excel can store them in a format easily brought back.– DrMoishe PippikCommented Feb 29 at 16:17
-
3CSV has no context of an Excel date, which is what Excel, is using to display the time in a specific format. So you will have to write the date as a string to another column.– RamhoundCommented Feb 29 at 16:18
-
CSV is just values seperated by commas. you are lucky excel trys at all to treat it as a date instead of raw text.– Frank ThomasCommented Feb 29 at 18:58
-
The date and time in the CSV file should be saved as text in the system format. They will be read in the same format. Therefore, you need to change the format of these cells after loading CSV into Excel, or change the system format to the one you want in the spreadsheet.– MGonetCommented Feb 29 at 20:38
|
Show 1 more comment