I have many large datasets that I am saving in .csv format, the input date values are in Julian format, but the final date format I want is dd/mm/yyyy hh:mm:ss. This part is simple, I just apply a custom date format as dd/mm/yyyy hh:mm:ss.
However, if I ever reopen the file in Excel and save it, despite already applying a custom format, it will then reinterpret any dates with days < 13 into a different format (m/d/yyyy h:mm) and interprets the rest as a general format. This is problematic because this means I can never edit the .csv in Excel again without re-doing the date column each time.
After saving the file the first time, the dates look like this in Notepad++:
23/09/2019 15:55:00
27/09/2019 15:48:00
10/10/2019 09:40:00
18/10/2019 12:00:00
Reopening and saving the file in Excel without changing anything, the dates change to the following in Notepad++:
23/09/2019 15:55:00
27/09/2019 15:48:00
10/10/2019 9:40
18/10/2019 12:00:00
I can't figure out why, but Excel seems to forget the format I save my date column in every time I reopen the .csv. If there are any workarounds a nudge in the right direction would be great, I've tried disabling autoformatting options but nothing has worked so far.
"Julian Format"? AFAIK, Julian dates cannot be converted to real Excel dates simply by formatting, whether using the classical definition or the more recent business related definition. So far as your question, do not Open csv files in Excel. Instead, Import them (best with Power Query) so you can tell Excel what the date format is in the csv file. CSV files, as @ScottCraner points out, do not contain any formatting information.