Text To Date Formatting (2003)

Topic

New Reply

I am struggling with what should be a pretty simple task… I have a column (59,000 rows deep) that I have created a formula to display a day in mm/yyyy format. The results of the formula have been Copied / Paste Special, and now I am left with what LOOKS like the right info, but what I need is it converted to a DATE format.

When I change the format (right click, custom mm/yyyy) nothing happens. HOWEVER, if I go into the lines (F2) and hit enter… wa laaa the formatting is correct.

Can someone tell me if I have something in my options that I need to change to automatically accept the formatting without F2 and Entering on 59,000 lines.

Thank you,

Reply | Quote

Replies

Your values are probably text values that happen to look like dates. Try the following:
1) Select the cells (for example click in the first cell, then press End, then Shift+down arrow).
2) Activate the Visual Basic Editor (Alt+F11).
3) Activate the Immediate window (Ctrl+G).
4) Type Selection.Value = Selection.Value and press Enter.
5) Switch back to Excel (Alt+F11).

Reply | Quote

Hans,

Greatly appreciate your help, it worked perfectly

John

Reply | Quote