Software

Ask something
Discussion Locked

Excel97 Date format converted to numbers

0 helpful
7 replies

by theresa.l.richardson

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Excel 97 file should have DATE columns formated to mm/dd/yyyy. Also the file should have TIME columns formated as hh:mm (AM/PM).
The file is opening up with these columns converted to numbers.
When cell is selected, the edit box shows the correct format, but the cell still has numbers.
Also some colums will not center, even after format applied.
Have done Format/Cell/and choose correct date format, but it is not formatting.
Tried deleting cell and re-typing -nothing.
I would rather not have to delete colums as there are over 8 columns affected.

7 replies

by sheryl v

Is the file you are using imported/exported from some other program (AS400/MS Access)?

If so, we have had much difficulty applying formats etc. to such data. We have found quickest solution to be inserting column, or using new sheet and then using the formula =value(a1) for each of the cells in the problem column & then selecting all and then[copy]-[paste special]-[values]. Once this is done all formatting was good to go.

For files that were the same column structure & count I've written macros that loop down through each cell until it reaches a blank cell and then moves to the next column until the entire process is complete.
Hope this helps!

by theresa.l.richardson

The file was created fresh, and until about a week ago showed the correct format.
Open the file last week or so, and the cells were changed.

This worked -- Thank you for your help.
Tweazee.

by dklippert

I think Sheryl is on the right track. If the entry is text rather than numeric, the formatting wont be applied. On simple way to convert text to numbers is to enter the number 1 in an empty cell. Copy it. Select all of the bad columns. Go to Edit>Paste Special and choose multiply.
You may have to reapply the Date/Time formatting.
Good luck

by donq

Before trying anything else Format your (column) range as Short Date and/or Medium Time. Microsoft stores Date/Time values as a 32 integer value with everything to the right of the decimal being Time and everything to the left representing the Date.

You may already have what you need jus formatted incorrectly???

This discussion is closed to new replies.