hi,

i have values like:
DDDJun 08
in a column.

I want to know if there is a formula i can have in the next column to
convert these values to dates in the format like "Jun-08", with 08 being the
year 2008. So for example, if the value was TWIJun 09, it would be converted
to "Jun-09"

is there a formula i could use? or would i have to do this programatically?

thanks in advance,
geebee

You should be able to use this formula...

=--MID(REPLACE(A1,7,1," 1, "),4,12)

and format the cell to show mmm-yy.

--
Rick (MVP - Excel)


"geebee" (noSPAMs) wrote in message
...
hi,

i have values like:
DDDJun 08
in a column.

I want to know if there is a formula i can have in the next column to
convert these values to dates in the format like "Jun-08", with 08 being
the
year 2008. So for example, if the value was TWIJun 09, it would be
converted
to "Jun-09"

is there a formula i could use? or would i have to do this
programatically?

thanks in advance,
geebee

thanks. can you explain this formula you are using, so i can understand it?
it is working, but I just want to understand what you are doing?=.

thanks in advance,
geebee


"Rick Rothstein" wrote:

You should be able to use this formula...

=--MID(REPLACE(A1,7,1," 1, "),4,12)

and format the cell to show mmm-yy.

--
Rick (MVP - Excel)


"geebee" (noSPAMs) wrote in message
...
hi,

i have values like:
DDDJun 08
in a column.

I want to know if there is a formula i can have in the next column to
convert these values to dates in the format like "Jun-08", with 08 being
the
year 2008. So for example, if the value was TWIJun 09, it would be
converted
to "Jun-09"

is there a formula i could use? or would i have to do this
programatically?

thanks in advance,
geebee

The problem with an input like Jun 08 (what remains after removing the first
3 characters) is that Excel (if you allow it to interpret that as a date)
will assume the 08 is the day and automatically use the current year for the
date's year value. What I did with the REPLACE command is take this...

DDDJun 08

and convert it into this...

DDDJun 1, 08

then when I take the first three characters off (using the MID function),
I'm left with this...

Jun 1, 08

which is, except for the 2-digit year, a standard date format for the 1st of
the given month. The double minus sign in front of the MID function tells
Excel to multiple the value after it by minus one twice (-1*-1) which is the
same as multiplying by +1 (and multiplying anything by +1 doesn't change its
value)... we do this because Excel will convert a text number into a real
number when that text number is used in a numerical calculation. Dates,
underneath it all, are just Double values (formatted to look like a date)
where the whole number part represents the number of days from Excel's first
date (January 1, 1900) and the decimal part (if any) represents the time as
a fraction of a 24-hour time period. Now, Excel uses the Windows system
settings to decide what century to assign to the year 08, so internally, it
will treat the 08 as 2008 when the double minus sign forces Excel to treat
the text as numeric value (the Double value that it will format to look like
a date). Of course, you then applied the mmm-yy format to show only the
month-year that you wanted displayed.

--
Rick (MVP - Excel)


"geebee" (noSPAMs) wrote in message
...
thanks. can you explain this formula you are using, so i can understand
it?
it is working, but I just want to understand what you are doing?=.

thanks in advance,
geebee


"Rick Rothstein" wrote:

You should be able to use this formula...

=--MID(REPLACE(A1,7,1," 1, "),4,12)

and format the cell to show mmm-yy.

--
Rick (MVP - Excel)


"geebee" (noSPAMs) wrote in message
...
hi,

i have values like:
DDDJun 08
in a column.

I want to know if there is a formula i can have in the next column to
convert these values to dates in the format like "Jun-08", with 08
being
the
year 2008. So for example, if the value was TWIJun 09, it would be
converted
to "Jun-09"

is there a formula i could use? or would i have to do this
programatically?

thanks in advance,
geebee

thanks.. before, i had:

=DATEVALUE(MONTH(DATEVALUE(LEFT(RIGHT(C2,6),3) & "-" & RIGHT(C2,2))) &"/1/"&
YEAR(DATEVALUE(LEFT(RIGHT(C2,6),3) & "-" & RIGHT(C2,2))))

which did not work

"Rick Rothstein" wrote:

The problem with an input like Jun 08 (what remains after removing the first
3 characters) is that Excel (if you allow it to interpret that as a date)
will assume the 08 is the day and automatically use the current year for the
date's year value. What I did with the REPLACE command is take this...

DDDJun 08

and convert it into this...

DDDJun 1, 08

then when I take the first three characters off (using the MID function),
I'm left with this...

Jun 1, 08

which is, except for the 2-digit year, a standard date format for the 1st of
the given month. The double minus sign in front of the MID function tells
Excel to multiple the value after it by minus one twice (-1*-1) which is the
same as multiplying by +1 (and multiplying anything by +1 doesn't change its
value)... we do this because Excel will convert a text number into a real
number when that text number is used in a numerical calculation. Dates,
underneath it all, are just Double values (formatted to look like a date)
where the whole number part represents the number of days from Excel's first
date (January 1, 1900) and the decimal part (if any) represents the time as
a fraction of a 24-hour time period. Now, Excel uses the Windows system
settings to decide what century to assign to the year 08, so internally, it
will treat the 08 as 2008 when the double minus sign forces Excel to treat
the text as numeric value (the Double value that it will format to look like
a date). Of course, you then applied the mmm-yy format to show only the
month-year that you wanted displayed.

--
Rick (MVP - Excel)


"geebee" (noSPAMs) wrote in message
...
thanks. can you explain this formula you are using, so i can understand
it?
it is working, but I just want to understand what you are doing?=.

thanks in advance,
geebee


"Rick Rothstein" wrote:

You should be able to use this formula...

=--MID(REPLACE(A1,7,1," 1, "),4,12)

and format the cell to show mmm-yy.

--
Rick (MVP - Excel)


"geebee" (noSPAMs) wrote in message
...
hi,

i have values like:
DDDJun 08
in a column.

I want to know if there is a formula i can have in the next column to
convert these values to dates in the format like "Jun-08", with 08
being
the
year 2008. So for example, if the value was TWIJun 09, it would be
converted
to "Jun-09"

is there a formula i could use? or would i have to do this
programatically?

thanks in advance,
geebee

I'm not sure why that formula didn't work for you... it works fine in my
XL2003 worksheet. Of course the function I posted is much shorter and make
many less function calls, so it is more efficient, but your formula should
have worked for you to.

--
Rick (MVP - Excel)


"geebee" (noSPAMs) wrote in message
...
thanks.. before, i had:

=DATEVALUE(MONTH(DATEVALUE(LEFT(RIGHT(C2,6),3) & "-" & RIGHT(C2,2)))
&"/1/"&
YEAR(DATEVALUE(LEFT(RIGHT(C2,6),3) & "-" & RIGHT(C2,2))))

which did not work

"Rick Rothstein" wrote:

The problem with an input like Jun 08 (what remains after removing the
first
3 characters) is that Excel (if you allow it to interpret that as a date)
will assume the 08 is the day and automatically use the current year for
the
date's year value. What I did with the REPLACE command is take this...

DDDJun 08

and convert it into this...

DDDJun 1, 08

then when I take the first three characters off (using the MID function),
I'm left with this...

Jun 1, 08

which is, except for the 2-digit year, a standard date format for the 1st
of
the given month. The double minus sign in front of the MID function tells
Excel to multiple the value after it by minus one twice (-1*-1) which is
the
same as multiplying by +1 (and multiplying anything by +1 doesn't change
its
value)... we do this because Excel will convert a text number into a real
number when that text number is used in a numerical calculation. Dates,
underneath it all, are just Double values (formatted to look like a date)
where the whole number part represents the number of days from Excel's
first
date (January 1, 1900) and the decimal part (if any) represents the time
as
a fraction of a 24-hour time period. Now, Excel uses the Windows system
settings to decide what century to assign to the year 08, so internally,
it
will treat the 08 as 2008 when the double minus sign forces Excel to
treat
the text as numeric value (the Double value that it will format to look
like
a date). Of course, you then applied the mmm-yy format to show only the
month-year that you wanted displayed.

--
Rick (MVP - Excel)


"geebee" (noSPAMs) wrote in message
...
thanks. can you explain this formula you are using, so i can
understand
it?
it is working, but I just want to understand what you are doing?=.

thanks in advance,
geebee


"Rick Rothstein" wrote:

You should be able to use this formula...

=--MID(REPLACE(A1,7,1," 1, "),4,12)

and format the cell to show mmm-yy.

--
Rick (MVP - Excel)


"geebee" (noSPAMs) wrote in message
...
hi,

i have values like:
DDDJun 08
in a column.

I want to know if there is a formula i can have in the next column
to
convert these values to dates in the format like "Jun-08", with 08
being
the
year 2008. So for example, if the value was TWIJun 09, it would be
converted
to "Jun-09"

is there a formula i could use? or would i have to do this
programatically?

thanks in advance,
geebee