Certain Time Values in a .xlsx not Changing When I convert to a .csv

Question

I am trying to convert from an Excel spreadsheet (.xlsx) file to a .csv file. The software I am using is Excel 2007. The issue I am having is I have a column of date and time values for every hour of every day, yet for some reason every 24th hour does not convert from the .xlsx to the .csv.

For example, one section of the column of my .csv says this:

1) So how would I change the cell in row 25 to follow the same 24 hour date time format as the rest of the cells in the column?

2) And could anyone please explain why it is converting from a date time in the .xlsx to this number in the .csv in the first place?

3) And is there a formula I could apply to Excel so that the rest of the 24-hour cells are also converted from .xlsx to .csv correctly as well?

Thanks so much!

Answer 1

This looks like a bug, caused when the date and time field is an exact integer.

Date/time fields are stored as an integer number of days, with the time as a fraction of a day, so when the time is midnight there is no fraction, and the field is an integer.

It is not clear how your data were imported, but I can imagine that auto-formatting during import could be misinterpreted.

It appears that selecting the whole column and formatting in the required date and time format will work round whatever is causing the abnormality.