General discussion

Locked

Excel97 Date format converted to numbers

By Theresa.L.Richardson ·
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.

This conversation is currently closed to new comments.

7 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Excel97 Date format converted to numbers

by Sheryl V In reply to Excel97 Date format conve ...

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!

Collapse -

Excel97 Date format converted to numbers

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.

Collapse -

Excel97 Date format converted to numbers

by DKlippert In reply to Excel97 Date format conve ...

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

Collapse -

Excel97 Date format converted to numbers

Poster rated this answer

Collapse -

Excel97 Date format converted to numbers

by donq In reply to Excel97 Date format conve ...

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???

Collapse -

Excel97 Date format converted to numbers

Poster rated this answer

Collapse -

Excel97 Date format converted to numbers

This question was closed by the author

Back to Software Forum
7 total posts (Page 1 of 1)  

Related Discussions

Related Forums