Format text 'dates' to real dates

Greetings,

I have a text file that I'm working with that includes 2 columns of dates in
the format yyyymmdd, however Excel sees these columns as text and therefore
any formatting I apply as far as date formats doesn't work. I tried custom
format and I get #####. Attempting to use any of the Date formats gets me no
change at all.

My end goal is to convert these two columns of 'dates' to display Julian Day
format, but in order for any of the formulas I've found to work, Excel must
see these as true dates.

Thanks,
~Jacy

You can use TTC (Text To Columns).

Select one of the columns and:
<Data> <TextToColumns> <Next> <Next>,

Then click on "Date" and make *sure* that YMD shows in the window,
Then <Finish>

You now have XL recognized dates, which you now can format any way you wish.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Jacy" <Jacy@discussions.microsoft.com> wrote in message
news:FC249F28-DD20-4F20-9F3F-15A2565FA964@microsoft.com...
> Greetings,
>
> I have a text file that I'm working with that includes 2 columns of dates
in
> the format yyyymmdd, however Excel sees these columns as text and
therefore
> any formatting I apply as far as date formats doesn't work. I tried custom
> format and I get #####. Attempting to use any of the Date formats gets me
no
> change at all.
>
> My end goal is to convert these two columns of 'dates' to display Julian
Day
> format, but in order for any of the formulas I've found to work, Excel
must
> see these as true dates.
>
> Thanks,
> ~Jacy

Jacy wrote:
> Greetings,
>
> I have a text file that I'm working with that includes 2 columns of
> dates in the format yyyymmdd, however Excel sees these columns as
> text and therefore any formatting I apply as far as date formats
> doesn't work. I tried custom format and I get #####. Attempting to
> use any of the Date formats gets me no change at all.
>
> My end goal is to convert these two columns of 'dates' to display
> Julian Day format, but in order for any of the formulas I've found to
> work, Excel must see these as true dates.
>
> Thanks,
> ~Jacy


Hi Jacy,
To convert your text dates to real dates you can use this formula:

=DATE(LEFT(D1,4),MID(D1,5,2),RIGHT(D1,2))

where in D1 is your text date, then you can drag along the rows...

--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

Thanks so much -- how simple was that?!?! [blush]

~Jacy

Appreciate the feed-back.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Jacy" <Jacy@discussions.microsoft.com> wrote in message
news:A9EAE448-310E-403D-93C2-5D1D69A7B2FD@microsoft.com...
> Thanks so much -- how simple was that?!?! [blush]
>
> ~Jacy
>