On Jul 23, 7:47*pm, Ron Rosenfeld wrote:
On Wed, 23 Jul 2008 15:31:16 -0700 (PDT), "
wrote:

Hi, our legacy system keeps dates in integers in form ccyymmdd.

I've long known how to convert, inExcel, a datetime to a number. *One
example is:

=DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2))

But looks as I may, I haven't found a way to convert a ccyymmdd int
into a datetime.

Oh also, I'm using Office 2007. *The FORMAT function, which might (?)
have helped me, seems to have disappeared.

thanx - pat

Perhaps I should also ask you what is a "ccyymmdd int" ? *Maybe I'm not
understanding the significance of the " int".

To convert anExceldate time into the ccyymmdd format, you can use the TEXT
function:

=TEXT(A1,"yyyymmdd")

To make that an Integer, you could precede the formula with a double unary.

=--TEXT(A1,"yyyymmdd")

Like others, I am confused as to what you want.
--ron

Geez now that I look again at my first two posts, I got it backwards.
No wonder (and apologies) for the confusion.

What I want is to convert a DATETIME to a NUMBER. (and the formula I
gave was saying: I know how to do it the reverse way, NUMBER to
DATETIME, but now I want DATETIME to NUMBER).

And you just gave me that Ron (just tested it).

=--TEXT(A1,"yyyymmdd").

The 'int' part is programmer-talk for integer. Or in excel NUMBER.
That is, I have a DATETIME and I want to convert it to an NUMBER. Of
format ccyymmdd ( or if you prefer, yyyymmdd ).

Anyway, thanx Ron.

Oh and the double unary operator - have never seen it before. Very
cool.

pat