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:

screenshot of my .csv spreadsheet

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!

New contributor
Zain Raza is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
  • This looks like a bug, caused by the date and time field being an exact integer. If you select the whole column and format in the date and time format you want, does this correct it? – AFH Jul 12 at 15:02
  • Yeah, it does actually! – Zain Raza Jul 12 at 20:11
  • I'll submit it as an answer, so that if it is a bug people can see there is a work-round. – AFH Jul 12 at 21:16

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.

Your Answer

Zain Raza is a new contributor. Be nice, and check out our Code of Conduct.

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.