Convert int ccyymmdd to date(time)

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
Wed Jul 23 18:02:19 CDT 2008

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=A0pm, "p...@well.com" <p...@well.com> 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. =A0One
> example is:
>
> =3DDATE(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. =A0The FORMAT function, which might (?)
> have helped me, seems to have disappeared.
>
> thanx - pat

patf
Wed Jul 23 19:06:58 CDT 2008

On Jul 23, 4:02=A0pm, Pete_UK <pashu...@auditel.net> 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=A0pm, "p...@well.com" <p...@well.com> 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. =A0On=
e
> > example is:
>
> > =3DDATE(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. =A0The 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
Wed Jul 23 19:30:49 CDT 2008

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?

"patf@well.com" wrote:
>
> On Jul 23, 4:02 pm, Pete_UK <pashu...@auditel.net> 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, "p...@well.com" <p...@well.com> 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

Ron
Wed Jul 23 19:42:13 CDT 2008

On Wed, 23 Jul 2008 15:31:16 -0700 (PDT), "patf@well.com" <patf@well.com>
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

Pete_UK
Wed Jul 23 19:57:46 CDT 2008

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

>
> Hi Pete,
>
> Now the formula is _not_ what I want to do. =A0It'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
Wed Jul 23 21:19:09 CDT 2008

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), "patf@well.com" <patf@well.com>
> 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

Ron
Wed Jul 23 21:47:14 CDT 2008

On Wed, 23 Jul 2008 15:31:16 -0700 (PDT), "patf@well.com" <patf@well.com>
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

patf
Thu Jul 24 09:33:38 CDT 2008

On Jul 23, 7:47=A0pm, Ron Rosenfeld <ronrosenf...@nospam.org> wrote:
> On Wed, 23 Jul 2008 15:31:16 -0700 (PDT), "p...@well.com" <p...@well.com>
> 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. =A0One
> >example is:
>
> >=3DDATE(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. =A0The FORMAT function, which might (?)
> >have helped me, seems to have disappeared.
>
> >thanx - pat
>
> Perhaps I should also ask you what is a "ccyymmdd int" ? =A0Maybe I'm not
> understanding the significance of the " int".
>
> To convert anExceldate time into the ccyymmdd format, you can use the TEX=
T
> function:
>
> =3DTEXT(A1,"yyyymmdd")
>
> To make that an Integer, you could precede the formula with a double unar=
y.
>
> =3D--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).

=3D--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
Thu Jul 24 19:17:28 CDT 2008

On Thu, 24 Jul 2008 07:33:38 -0700 (PDT), "patf@well.com" <patf@well.com>
wrote:

>On Jul 23, 7:47 pm, Ron Rosenfeld <ronrosenf...@nospam.org> wrote:
>> On Wed, 23 Jul 2008 15:31:16 -0700 (PDT), "p...@well.com" <p...@well.com>
>> 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