Windows Forum

Ask something
Discussion Locked

reformatting time in Excel

0 helpful
5 replies

by wpmercer

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

hey All,

My boss has a proprietary program that when exported to Excel 97 returns time as 10:57A.M. There are over 400 records so changing every time cell (two of them per record) to something that she can do manipulations with is out of the question. I have been told at other forums that a macro can do this, I'm not so sure because s/he didn't elaborate.

5 replies

by dklippert

If I understand your question, the time is being returned as text. To convert it to a number, enter the number 1 in an empty cell. Copy it. Now go to the time cells and choose Edit>Paste Special Multiply. You may have to then go to Format>Cell and put it into the format you want.

by wpmercer

I can see why I would do the first part of the instructions, but I am still getting a #VALUE return. The Format > Cell does not have one for A.M. (with periods)

by dklippert

You'll probably also have to fix the layout as well. Use Edit>Replace to Find "A" and replace with a space and an A " A".
You'll also have to replace all of the periods. Excel only uses AM not A.M.
For other information, look at:

www.cpearson.com/excel/datetime.htm

by wpmercer

Thanks, so simple of an answer. When someone at the other forum suggested a macro, this will work great!

This discussion is closed to new replies.