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.