The original numbers are being imported from another program by macros
already in place. So I have created a macro to organize that raw data into a
much nicer format more easily read by my clients. That column when imported
looks like that. The original program uses those numbers as a date, the
macro used for importing does not change them into a date, so I wanted to
incorporate a formula into my macro that would make the change to clean it
up. The change would occur for an entire column, not just one or two cells.
SO:

Change all number values in Column X into dates.

The somewhat confusing thing is that when imported the dates are just
numbers, so there are 3 numbers (406) for april 2006, but 4 numbers (1205)
for December 2005. If ALL numbers in that column had 4 value places then I
could simply do a format to a date. BUT, I couldn't figure out how to get it
to work out. SO here I am :) If you need any other input, let me know.
THANKS!!!

"Ron Coderre" wrote:

Questions/Comments:

1)How is the original number getting in the cell? Is it entered directly in
the cell? imported? Pasted in?

2)The examples you posted would not be interpreted by Excel as dates in the
way you want. Formatting, alone, would not solve that problem. Hence, the
formula approaches posted my me and the other contributors. (By the way, the
formulas would be entered on the worksheet and would reference the "date
number" that you want converted.)

3)Were you hoping to run a VBA program to change the number into a date?
(you didn't mention VBA in your original post) How many numbers do you need
converted to dates? For just one or two, running a program may be less
intuitive than using a formula....or just re-entering the value as a true
date.

4)Are there any other requirements that will impact the approach you would
use?

***********
Regards,
Ron

XL2002, WinXP


"bodhisatvaofboogie" wrote:

hmm...I'm confused a little. How are you wanting me to use that formula?
I'm trying to plug it in with a variety of methods with no success. How
would it look wihtin VBE when editing the code of a macro?

Or are you suggesting using it as a conditional format? IF so, I couldn't
get that to work either. break it down simple for me. I am still new to the
whole macro code world :) THANKS!!!

"Ron Coderre" wrote:

Try something like this:

For a number in A1 (eg 406 or 1205)

This formula uses Excel's default year calculation
B1: =DATEVALUE(INT(A1/100)&"/1/"&MOD(A1,100))
Format B1 as a date

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"bodhisatvaofboogie" wrote:

Is there an easy way to change numbers within a cell into dates with a
format, or formula within a macro? For example:

The Number in the cells are as follows:
406 which stands for April 2006
1205 Which stands for December 2005

I want to change those numbers that are there into a nicer looking date
format, like Apr-06 or something. THANKS!!!