Convert int ccyymmdd to date(time)

[patf]

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

 

[Pete_UK]

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

 

[patf]

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

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]

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?

 

[Ron Rosenfeld]

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

 

[Pete_UK]

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

Pete

 

[Dave Peterson]

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]

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

 

[patf]

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

 

[Ron Rosenfeld]

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