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

I've long known how to convert, in Excel, 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

Are you saying that the formula did not work with a valid ccyymmdd in
A2? Or are you saying that this gives you a number like 39500, and you
want this formatted to appear as a date?

Pete

On Jul 23, 11:31*pm, " wrote:
Hi, our legacy system keeps dates in integers in form ccyymmdd.

I've long known how to convert, in Excel, 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

On Jul 23, 4:02*pm, Pete_UK wrote:
Are you saying that the formula did not work with a valid ccyymmdd in
A2? Or are you saying that this gives you a number like 39500, and you
want this formatted to appear as a date?

Pete

On Jul 23, 11:31*pm, " wrote:

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

I've long known how to convert, in Excel, 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

Hi Pete,

Now the formula is _not_ what I want to do. It's an example of 'the
other direction'.

I want to have a ccyymmdd int sitting in a cell and then convert it to
the equivalent datetime in a different cell.

pat

It sure looks like it'll return a date (with no time) to me.

Did you remember to format the cell with the formula as a date?

" wrote:

On Jul 23, 4:02 pm, Pete_UK wrote:
Are you saying that the formula did not work with a valid ccyymmdd in
A2? Or are you saying that this gives you a number like 39500, and you
want this formatted to appear as a date?

Pete

On Jul 23, 11:31 pm, " wrote:

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

I've long known how to convert, in Excel, 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

Hi Pete,

Now the formula is _not_ what I want to do. It's an example of 'the
other direction'.

I want to have a ccyymmdd int sitting in a cell and then convert it to
the equivalent datetime in a different cell.

pat

--

Dave Peterson

Well, I'm sorry, Pat, but I don't understand what you are trying to
do, or what the problem is.

Pete

On Jul 24, 1:06*am, " wrote:


Hi Pete,

Now the formula is _not_ what I want to do. *It's an example of 'the
other direction'.

I want to have a ccyymmdd int sitting in a cell and then convert it to
the equivalent datetime in a different cell.

pat

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, in Excel, 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

What result are you getting? I obtain the expected result in Excel 2007?

Also, what do you mean by the "Format function". As far as I know, that is not
a worksheet function in any version of Excel. (It is present in VBA). If you
mean how to format a cell, one way is to right click on the cell and select
"format cells" from the drop-down menu.

--ron

I'm guessing that the OP was confused with the worksheet function =Text() and
VBA's Format.

But I'm not sure what the real problem is.

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, in Excel, 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

What result are you getting? I obtain the expected result in Excel 2007?

Also, what do you mean by the "Format function". As far as I know, that is not
a worksheet function in any version of Excel. (It is present in VBA). If you
mean how to format a cell, one way is to right click on the cell and select
"format cells" from the drop-down menu.

--ron

--

Dave Peterson

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, in Excel, 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 an Excel date 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

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

On Thu, 24 Jul 2008 07:33:38 -0700 (PDT), "
wrote:

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

Well, I'm glad my guess turned out to be helpful. Thanks for the feedback.
--ron