Converting Text Dates to Serial Numbers

[LPS]

Using Excel 2000 is there a function or process which will convert a text
date into an Excel serial number, so that the date can be used in subsequent
calculations?

Thank you,

 

[Mike H]

Hi,

This would be much easier if you had told us what your text date looks like.
You haven't so lets guess:-

Text string that represents a date in A1
20071211

In another cell enter
=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))

Format as date to see the date or as general to see the date serial number.

Mike

 

[LPS]

You are right... sorry, I should have included an example.. that's what
happens when you rush. Example is: 2006-Jan-03.

I will try your suggestion. Thanks very much.

 

[Arvi Laanemets]

Hi

Another possible solution - whenever your date string represents any valid
date format for your regional settings (p.e. I format A1 as Text and enter
there a string "12.12.2007")
=DATEVALUE(A1)

 

[RagDyeR]

You could use TTC - Text To Columns.

Select cell(s), then, from the Menu Bar:

<Data> <TextToColumns> <Next> <Next>
Under "Column Data Format", click "Date",
AND *make sure* that "YMD" displays in the next box.

Then <Finish>

NOW, while cells are *still* selected, from the Menu Bar,
<Format> <Cells> <Number> tab,
Click on "General", then <OK>.

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

You are right... sorry, I should have included an example.. that's what
happens when you rush. Example is: 2006-Jan-03.

I will try your suggestion. Thanks very much.