I'm trying to convert a string of numbers from a data dump in the yyyymm
format to a usable date. I'll be referencing it from another sheet and will
need it in the mmm-yy format in one section and in another mm-dd-yy.

The tough part is that it is a rolling 19 months of data and will change
every month. 01Month being the oldest and in the second month it drops off
and 02Month becomes the new 01Month. Additionally it is updated daily so the
19Month line is a "to-date" date in the yyyymmdd format.

I want to convert the data dump format into somethign usable.

Right now for the non-current months this works:

Constant Fr.Data Dump Convert to date (day 1 is dd)
01Month 200509 =DATE(LEFT(G3,4),RIGHT(G3,LEN(G3)-4),1)

The problem is that for the current month (ie 20070221 = Feb 21, 2007) the
conversion is 05/01/25. Obviously its wrong because of the logic, but how do
I write the last equation to get it to become Feb 21, 2007 on the current and
Feb 01, 2007 for the 18th month (using the first day as the dd)?

I tried this and it doesn't work.
=DATE(LEFT(G3,4),RIGHT(G3,LEN(G3)-4),IF(RIGHT(G21,
LEN(G21)-6)=0,1,RIGHT(G21, LEN(G21)-6)))

You can use the MID function to get the month:
=DATE(LEFT(G3,4),MID(G3,5,2),IF(LEN(G3)=6,1,RIGHT( G3,2)))

James wrote:
I'm trying to convert a string of numbers from a data dump in the yyyymm
format to a usable date. I'll be referencing it from another sheet and will
need it in the mmm-yy format in one section and in another mm-dd-yy.

The tough part is that it is a rolling 19 months of data and will change
every month. 01Month being the oldest and in the second month it drops off
and 02Month becomes the new 01Month. Additionally it is updated daily so the
19Month line is a "to-date" date in the yyyymmdd format.

I want to convert the data dump format into somethign usable.

Right now for the non-current months this works:

Constant Fr.Data Dump Convert to date (day 1 is dd)
01Month 200509 =DATE(LEFT(G3,4),RIGHT(G3,LEN(G3)-4),1)

The problem is that for the current month (ie 20070221 = Feb 21, 2007) the
conversion is 05/01/25. Obviously its wrong because of the logic, but how do
I write the last equation to get it to become Feb 21, 2007 on the current and
Feb 01, 2007 for the 18th month (using the first day as the dd)?

I tried this and it doesn't work.
=DATE(LEFT(G3,4),RIGHT(G3,LEN(G3)-4),IF(RIGHT(G21,
LEN(G21)-6)=0,1,RIGHT(G21, LEN(G21)-6)))


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

as Borat would say..."Niiice!"

Thank you. That worked perfectly.

"Debra Dalgleish" wrote:

You can use the MID function to get the month:
=DATE(LEFT(G3,4),MID(G3,5,2),IF(LEN(G3)=6,1,RIGHT( G3,2)))

James wrote:
I'm trying to convert a string of numbers from a data dump in the yyyymm
format to a usable date. I'll be referencing it from another sheet and will
need it in the mmm-yy format in one section and in another mm-dd-yy.

The tough part is that it is a rolling 19 months of data and will change
every month. 01Month being the oldest and in the second month it drops off
and 02Month becomes the new 01Month. Additionally it is updated daily so the
19Month line is a "to-date" date in the yyyymmdd format.

I want to convert the data dump format into somethign usable.

Right now for the non-current months this works:

Constant Fr.Data Dump Convert to date (day 1 is dd)
01Month 200509 =DATE(LEFT(G3,4),RIGHT(G3,LEN(G3)-4),1)

The problem is that for the current month (ie 20070221 = Feb 21, 2007) the
conversion is 05/01/25. Obviously its wrong because of the logic, but how do
I write the last equation to get it to become Feb 21, 2007 on the current and
Feb 01, 2007 for the 18th month (using the first day as the dd)?

I tried this and it doesn't work.
=DATE(LEFT(G3,4),RIGHT(G3,LEN(G3)-4),IF(RIGHT(G21,
LEN(G21)-6)=0,1,RIGHT(G21, LEN(G21)-6)))


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

You're welcome. High five!

James wrote:
as Borat would say..."Niiice!"

Thank you. That worked perfectly.

"Debra Dalgleish" wrote:


You can use the MID function to get the month:
=DATE(LEFT(G3,4),MID(G3,5,2),IF(LEN(G3)=6,1,RIGHT( G3,2)))

James wrote:

I'm trying to convert a string of numbers from a data dump in the yyyymm
format to a usable date. I'll be referencing it from another sheet and will
need it in the mmm-yy format in one section and in another mm-dd-yy.

The tough part is that it is a rolling 19 months of data and will change
every month. 01Month being the oldest and in the second month it drops off
and 02Month becomes the new 01Month. Additionally it is updated daily so the
19Month line is a "to-date" date in the yyyymmdd format.

I want to convert the data dump format into somethign usable.

Right now for the non-current months this works:

Constant Fr.Data Dump Convert to date (day 1 is dd)
01Month 200509 =DATE(LEFT(G3,4),RIGHT(G3,LEN(G3)-4),1)

The problem is that for the current month (ie 20070221 = Feb 21, 2007) the
conversion is 05/01/25. Obviously its wrong because of the logic, but how do
I write the last equation to get it to become Feb 21, 2007 on the current and
Feb 01, 2007 for the 18th month (using the first day as the dd)?

I tried this and it doesn't work.
=DATE(LEFT(G3,4),RIGHT(G3,LEN(G3)-4),IF(RIGHT(G21,
LEN(G21)-6)=0,1,RIGHT(G21, LEN(G21)-6)))


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html