Converting Text Dates to Serial Numbers

  • Thread starter Thread starter LPS
  • Start date Start date
L

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,
 
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
 
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.
 
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)
 
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.
 
Back
Top