dating problems!

I recieve date information in text format...i.e. "01-JAN-2005", but would
like to convert it to an Excel format date.

I have seen it done before by using a function to split the text into parts
(getting excel to read the cell as DD-MMM-YYYY) and output the result using
DAY, MONTH, YEAR function. No idea how to do this though.

Thanks
J

=A1*1

and format as date.

HTH
Jason
Atlanta, GA

>-----Original Message-----
>
>I recieve date information in text format...i.e. "01-JAN-
2005", but would
>like to convert it to an Excel format date.
>
>I have seen it done before by using a function to split
the text into parts
>(getting excel to read the cell as DD-MMM-YYYY) and
output the result using
>DAY, MONTH, YEAR function. No idea how to do this though.
>
>Thanks
>J
>.
>

"01-JAN-2005"*1 returns #VALUE!

"Jason Morin" wrote:

> =A1*1
>
> and format as date.
>
> HTH
> Jason
> Atlanta, GA
>
> >-----Original Message-----
> >
> >I recieve date information in text format...i.e. "01-JAN-
> 2005", but would
> >like to convert it to an Excel format date.
> >
> >I have seen it done before by using a function to split
> the text into parts
> >(getting excel to read the cell as DD-MMM-YYYY) and
> output the result using
> >DAY, MONTH, YEAR function. No idea how to do this though.
> >
> >Thanks
> >J
> >.
> >
>

Jose,

The formula Jason provided fails because you have double quotes around your
string values.

Simply select your date values, and use Edit / Replace and replace double
quotes with nothing. Excel will then convert the values to dates.

HTH,
Bernie
MS Excel MVP

"Jose Mourinho" <[email protected]> wrote in message
news:[email protected]...
>
> I recieve date information in text format...i.e. "01-JAN-2005", but would
> like to convert it to an Excel format date.
>
> I have seen it done before by using a function to split the text into
parts
> (getting excel to read the cell as DD-MMM-YYYY) and output the result
using
> DAY, MONTH, YEAR function. No idea how to do this though.
>
> Thanks
> J

I didn't have the dates in brackets (just used "them" in the post) but it got
me thinking and I checked the sheets again. It turns out that I have a space
before the each date <SPACE>01-JAN-2005. Problem solved! Thanks for the help.

"Bernie Deitrick" wrote:

> Jose,
>
> The formula Jason provided fails because you have double quotes around your
> string values.
>
> Simply select your date values, and use Edit / Replace and replace double
> quotes with nothing. Excel will then convert the values to dates.
>
> HTH,
> Bernie
> MS Excel MVP
>
> "Jose Mourinho" <[email protected]> wrote in message
> news:[email protected]...
> >
> > I recieve date information in text format...i.e. "01-JAN-2005", but would
> > like to convert it to an Excel format date.
> >
> > I have seen it done before by using a function to split the text into
> parts
> > (getting excel to read the cell as DD-MMM-YYYY) and output the result
> using
> > DAY, MONTH, YEAR function. No idea how to do this though.
> >
> > Thanks
> > J
>
>
>