Convert int ccyymmdd to date(time)

  • Thread starter Thread starter patf
  • Start date Start date
P

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
 
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
 
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
Click to expand...

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?
 
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
Click to expand...

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
 
Well, I'm sorry, Pat, but I don't understand what you are trying to
do, or what the problem is.

Pete
 
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.
 
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
Click to expand...

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
 
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
Click to expand...

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
 
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
Click to expand...

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