Format text 'dates' to real dates

[Guest]

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

 

[Ragdyer]

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.

 

[Franz Verga]

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

 

[Guest]

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

~Jacy

 

[Ragdyer]

Appreciate the feed-back.