Convert int ccyymmdd to date(time)

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

patf@well.com - 23 Jul 2008 23:31 GMT

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

Reply to this Message

Pete_UK - 24 Jul 2008 00:02 GMT

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, "p...@well.com" <p...@well.com> wrote:

> Hi, our legacy system keeps dates in integers in form ccyymmdd.
>
[quoted text clipped - 10 lines]
>
> thanx - pat

Reply to this Message

patf@well.com - 24 Jul 2008 01:06 GMT

> 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
[quoted text clipped - 18 lines]
>
> > 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

Reply to this Message

Dave Peterson - 24 Jul 2008 01:30 GMT

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?

> > 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
[quoted text clipped - 28 lines]
>
> pat

Signature

Dave Peterson

Reply to this Message

Pete_UK - 24 Jul 2008 01:57 GMT

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, "p...@well.com" <p...@well.com> wrote:

> Hi Pete,
>
[quoted text clipped - 5 lines]
>
> pat

Reply to this Message

Ron Rosenfeld - 24 Jul 2008 01:42 GMT

>Hi, our legacy system keeps dates in integers in form ccyymmdd.
>
[quoted text clipped - 10 lines]
>
>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

Reply to this Message

Dave Peterson - 24 Jul 2008 03:19 GMT

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.
> >
[quoted text clipped - 19 lines]
>
> --ron

Signature

Dave Peterson

Reply to this Message

Ron Rosenfeld - 24 Jul 2008 03:47 GMT

>Hi, our legacy system keeps dates in integers in form ccyymmdd.
>
[quoted text clipped - 10 lines]
>
>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

Reply to this Message

patf@well.com - 24 Jul 2008 15:33 GMT

> On Wed, 23 Jul 2008 15:31:16 -0700 (PDT), "p...@well.com" <p...@well.com>
> wrote:
[quoted text clipped - 28 lines]
> 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

Reply to this Message

Ron Rosenfeld - 25 Jul 2008 01:17 GMT

>> On Wed, 23 Jul 2008 15:31:16 -0700 (PDT), "p...@well.com" <p...@well.com>
>> wrote:
[quoted text clipped - 50 lines]
>
>pat

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

Reply to this Message