Excel Date Formatting Issue

Question

Hello,

I upload .xlsx workbooks to a database that has very specific date formatting requirements (CKAN database). The dates need to be formatted as "yyyy-mm-dd hh:mm:ss" . Using a 24 hour clock.

I have a sheet where all of the dates are formatted correctly, except for one. While it appears to look the same as the rest of the column (1997-10-02 16:00:00), the content of the cell actually contains "1997-10-02 4:00 PM" which is causing the entire column to be interpreted by the database as text rather than as a timestamp.

I have tried using Format Painter, manually entering "16:00:00, deleting the content and reentering it. The Custom format is set to ' yyyy"-"mm"-"dd hh":"mm:ss ' just like every other cell in the column, but no matter what I do, the cell reverts to storing the time as 4:00 PM.

Using Microsoft Office Standard 2019.

Any ideas for how to fix this?

Answer 1

Thanks OssieMac,

The format with the quotes included is one of the presets in my list of Custom date formats, and is what all of the other cells in the column are using (not sure why).

Using the double quotes does work. However, I am wondering if you are mistaking a previously inserted custom format which then shows up as one of the available formats in the list. Alternatively, maybe you are in a country where it is necessary to use the quotes.

However. have you entered the actual date again because if you enter an alpha character O instead of the numeral zero then it will show up as text with the PM.

Answer 2

Thanks OssieMac,

The format with the quotes included is one of the presets in my list of Custom date formats, and is what all of the other cells in the column are using (not sure why).

I tried removing the quotes and it didn't change anything.

I also tried adding a new row, copying in a value from another cell and changing the date/time to the desired date, and it still added the PM...

Answer 3

I don't understand why you are using the custom number format as ' yyyy"-"mm"-"dd hh":"mm:ss '.

The custom number format should be as follows without any double or single quotes.

yyyy-mm-dd hh:mm:ss

Interesting part is that you have not got the double quotes around the last colon between the mm:ss