CSV file date format

%3CLINGO-SUB%20id%3D%22lingo-sub-2279840%22%20slang%3D%22de-DE%22%3ECSV%20file%20date%20format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2279840%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CSPAN%3EHello!%20We%20have%20a%20problem%20with%20a%20csv%20file.%20We%20have%20a%20column%20that%20needs%20the%20formatting%20tt.mm.jjjj%20hh%3Amm%20Currently%20it%20is%20tt.mm.jj%20hh%3Amm%20(the%20year%20only%20has%20two%20digits%20instead%20of%20four).%20If%20we%20change%20it%20in%20the%20custom%20format%20setting%20and%20saving%20the%20document%2C%20close%20it%20and%20open%20it%20again%20the%20formatting%20is%20the%20same%20as%20it%20was%20before%2C%20so%20again%20only%20with%20two%20digits%20instead%20of%20four.%20This%20is%20only%20the%20case%20for%20csv%20files%2C%20for%20xlsx%20files%20the%20custom%20formatting%20stays%20the%20same%20even%20after%20closing%20and%20opening%20it%20again%2C%20but%20we%20need%20the%20document%20in%20a%20csv%20format.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EIt%20would%20be%20great%20if%20someone%20could%20help%20me%20with%20that!%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2279840%22%20slang%3D%22de-DE%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2279866%22%20slang%3D%22en-US%22%3ERe%3A%20CSV%20file%20date%20format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2279866%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1032368%22%20target%3D%22_blank%22%3E%40ChristinaHeinloth%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECSV%20files%20can't%20store%20format%20information.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hello! We have a problem with a csv file. We have a column which needs the formatting tt.mm.jjjj hh:mm Currently it is tt.mm.jj hh:mm (the year only has two digits instead of four). If we change it in the custom format setting and save the document, close it and open it again the formatting is the same as it was before, so again only with two digits instead of four. This is only the case for csv files, for xlsx files the custom formatting stays the same even after closing and opening it again, but we need the document in a csv format.

 

It would be great if someone could help me with that!

4 Replies

@ChristinaHeinloth 

CSV files can't store format information.

Thank you for that hint. Do you know any other solution how to solve this problem?
An option might be to stop opening csv files with Excel and, instead, import the csv file to Excel using Power Query.

@ChristinaHeinloth 

Yes, CSV is actually text file. However, if open CSV in Excel, apply custom format to data, save file as CSV it shall save the data (as text) in the form of applied format. Another story is that if you open file again in Excel it could show you some data in your default locale format, but that's only visualization. You may check in Notepad that is the actual data.

So, depends on goals - to change the data or to show the data. If former it works. If latest it depends, when Excel format is most reliable.

See for example Formatting Dates for CSV Imports – Administration (echo360.com)