Date Conversion CCYYMMDD to MMDDYY 1

[mdabney]

I use the AS400 query function to dump information into Excel for massaging. Since the dreaded Y2K, our ERP solution added the century in front of the date. As in, today's date would copy as 1050214. I send these reports to other people who are having trouble with the dates. Is there an Excel formula that I could use to convert the dates?

 

[SkipVought]

Hi,

Today's date is
[tt]
01050214
[/tt]
where 01 is the CENTURY?

Would you represent the twentieth century as 00?

Assuming that then if your "date" is in A1...
[tt]
=DATE((IF(ISERROR(LEFT(A1,LEN(A1)-6)),0,LEFT(A1,LEN(A1)-6))*100+1900+IF(ISERROR(MID(A1,LEN(A1)-5,2)),MID(A1,LEN(A1)-4,1),MID(A1,LEN(A1)-5,2))),LEFT(RIGHT(A1,4),2),RIGHT(A1,2))
[/tt]


Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}

 

[TonyJollans]

Hi mdabney,

Assuming the dates are all numeric, I think this should work for you:

[blue][tt] =DATE(1900+FLOOR(MOD(A1,10^7),10^4)/10^4,FLOOR(MOD(A1,10^4),100)/100,MOD(A1,100))[/tt][/blue]


Skip,

I have seen that notation before and it's quite believable from an AS400.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[bluedragon2]

=DATE(MID(A1,LEN(A1)-5,2)+2000,MID(A1,LEN(A1)-3,2),RIGHT(A1,2))

should work for dates 2000 and after...

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[SkipVought]

Tony,

I like it! ==>*

Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}

 

[mdabney]

Thanks, everyone....