Excel Date Problems 1

[mmck1964]

I import a date field from outr billing system that seems to be in a format that Excel cannot convert. From our system a January 2004 date looks like 104. A December 2003 date looks like 1203. Is there a way to get 104 to look like 01/2004 and 1203 to look like 12/03 in Excel?
Thanks!

 

[SkipVought]

Hi,

January 2004 is not a recognizable date format for Excel. Excel wants to see month, day and year.

to get 104 or 1203 to be "recognized" you need to convert into month/dat/year so

104 must become "1/1/2004" and
1203 must become "12/1/2003"

assuming that your systems default date format is m/d/yyyy and not d/m/yyyy or some other format.

so here's a formula that could work on values in column A

=DATE("20"&RIGHT(A1,2),IF(LEN(A1)=3,LEFT(A1,1),LEFT(A1,2)),1)

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[mmck1964]

What gets imported into Excel is a 104 or a 1203. Next month I may have 204 them 304 and so on. I need to cover these to 02/2004 and then 03/2004. It could even be 02/01/2004 ect. It really looks like an issue with dates like 1004 and 1104 and 1204.

 

[SkipVought]

the formula works on myy or mmyy

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[mmck1964]

I put the formula in and it did covert the A1 "104" to "1/1/2004" It does not convert a "1203" to "12/1/2003". Am I doing something wrong?

 

[mmck1964]

My bad!! I found my problem. I do have some dates that are like 0596 for May 1996. It coverts it to 05/01/2096. Any thoughts on thet??
Thanks for your help!!!

 

[SkipVought]

=DATE(IF(RIGHT(A1,2)>"50","19","20")&RIGHT(A1,2),IF(LEN(A1)=3,LEFT(A1,1),LEFT(A1,2)),1)

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884