Formating years.
 

have a csv file that in one column has years 89-99, 99-04 can I format that
column so that it changes those years to 1989-1999, 1999-2004

Formating years.
 

I believe that when you format the cells to the date format you just
need to enter yyyy. For a column that is all in 1900's you would enter
19yy as a custom date entry.
Hope this info helps.


--
DBsWifeLB
------------------------------------------------------------------------
DBsWifeLB's Profile: http://www.excelforum.com/member.php...o&userid=10802
View this thread: http://www.excelforum.com/showthread...hreadid=522288

Formating years.
 

You can add a new column is Excel with a formula like this:
=IF(LEFT(A1,1)="0","20",19) & LEFT(A1,2) & "-" & IF(MID(A1,4,1)="0","20",19)
& RIGHT(A1,2)
That hardcode the format in your values.
---

"jason2444" wrote:

have a csv file that in one column has years 89-99, 99-04 can I format that
column so that it changes those years to 1989-1999, 1999-2004

Formating years.
 

Changed the formula slightly to fit my file.
=IF(LEFT(E2,1)="0","20",19) & LEFT(E2,2) & "-" & IF(MID(E2,4,1)="0","20",19)

on a cell containing 99 - 06 it is coming up with 1999 - 20 how do I get it
to add the 06

"Miguel Zapico" wrote:

You can add a new column is Excel with a formula like this:
=IF(LEFT(A1,1)="0","20",19) & LEFT(A1,2) & "-" & IF(MID(A1,4,1)="0","20",19)
& RIGHT(A1,2)
That hardcode the format in your values.
---

"jason2444" wrote:

have a csv file that in one column has years 89-99, 99-04 can I format that
column so that it changes those years to 1989-1999, 1999-2004

Formating years.
 

Add "& RIGHT(E2,2)" to the end, it was trimmed by the mail.

"jason2444" wrote:

Changed the formula slightly to fit my file.
=IF(LEFT(E2,1)="0","20",19) & LEFT(E2,2) & "-" & IF(MID(E2,4,1)="0","20",19)

on a cell containing 99 - 06 it is coming up with 1999 - 20 how do I get it
to add the 06

"Miguel Zapico" wrote:

You can add a new column is Excel with a formula like this:
=IF(LEFT(A1,1)="0","20",19) & LEFT(A1,2) & "-" & IF(MID(A1,4,1)="0","20",19)
& RIGHT(A1,2)
That hardcode the format in your values.
---

"jason2444" wrote:

have a csv file that in one column has years 89-99, 99-04 can I format that
column so that it changes those years to 1989-1999, 1999-2004

Formating years.
 

one last question in some of the cells it 04+ or 03+ the + meaning to current
year. is there a what to make it display 2004 - 2006
2003 - 2006

"Miguel Zapico" wrote:

Add "& RIGHT(E2,2)" to the end, it was trimmed by the mail.

"jason2444" wrote:

Changed the formula slightly to fit my file.
=IF(LEFT(E2,1)="0","20",19) & LEFT(E2,2) & "-" & IF(MID(E2,4,1)="0","20",19)

on a cell containing 99 - 06 it is coming up with 1999 - 20 how do I get it
to add the 06

"Miguel Zapico" wrote:

You can add a new column is Excel with a formula like this:
=IF(LEFT(A1,1)="0","20",19) & LEFT(A1,2) & "-" & IF(MID(A1,4,1)="0","20",19)
& RIGHT(A1,2)
That hardcode the format in your values.
---

"jason2444" wrote:

have a csv file that in one column has years 89-99, 99-04 can I format that
column so that it changes those years to 1989-1999, 1999-2004