Excel - Convert date from text to number

[milomak]

I’ve got a whole list of dates in an excel column. The problem is that they are not in number form. So for instance I see a date 06/01/2000 but if I do an isnumber() on that, cell it returns false. How can I change the date from text to number?

 

[xrapidx]

an example of what you want the 'number' to look like?

 

[KobusDJ]

To do it with a formula, use DATE()

Syntax: DATE(year,month,day)

That will give: =DATE(RIGHT(A1,4), MID(A1,4,2), LEFT(A1,2))

 

[poffle]

What about just highlighting the cells, right clicking on format cells and selecting the correct format? Date, Number, Custom?

 

[charlie_82]

Assuming the data is in cell A1:
=SUBSTITUTE(A1,"/","")

This removes the / in the date. So you go from 06/01/2009 to 06012009

 

[KobusDJ]

The numeric value for the date 06/02/2009 is 36562. (Days since 1 Jan 1900)

 

[BigAl-sa]

The numeric value for the date 06/02/2009 is 36562. (Days since 1 Jan 1900)

sounds a bit little. disregarding leap years 100*365=36500
8*365=2920

which is 39420

I tried using excel's built-in functions to get this a while back and couldn't find a way to do it. Had to build my own function

 

[Sackboy]

On Excel 2007, use DATEVALUE().

Returns the serial number of the date represented by date_text. Use DATEVALUE to convert a date represented by text to a serial number.

Syntax

DATEVALUE(date_text)

Date_text is text that represents a date in a Microsoft Excel date format. For example, "1/30/2008" or "30-Jan-2008" are text strings within quotation marks that represent dates. Using the default date system in Excel for Windows, date_text must represent a date from January 1, 1900, to December 31, 9999. Using the default date system in Excel for the Macintosh, date_text must represent a date from January 1, 1904, to December 31, 9999. DATEVALUE returns the #VALUE! error value if date_text is out of this range.

If the year portion of date_text is omitted, DATEVALUE uses the current year from your computer's built-in clock. Time information in date_text is ignored.

Remarks

Excel stores dates as sequential serial numbers so they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,448 days after January 1, 1900. Excel for the Macintosh uses a different date system as its default.
Most functions automatically convert date values to serial numbers.
Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

Create a blank workbook or worksheet.
Select the example in the Help topic.
Note Do not select the row or column headers.



Selecting an example from Help
Press CTRL+C.
In the worksheet, select cell A1, and press CTRL+V.
To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

1
2
3
4
5
A B
Formula Description (Result)
=DATEVALUE("8/22/2008") Serial number of the text date, using the 1900 date system (39682)
=DATEVALUE("22-AUG-2008") Serial number of the text date, using the 1900 date system (39682)
=DATEVALUE("2008/02/23") Serial number of the text date, using the 1900 date system (39501)
=DATEVALUE("5-JUL") Serial number of the text date, using the 1900 date system, and assuming the computer's built-in clock is set to 2008 (39634)


Note To view the number as a date, select the cell, and then on the Sheet tab, in the Number group, click the arrow next to Number Format, and click Short Date or Long Date.

 

[KobusDJ]

sounds a bit little. disregarding leap years 100*365=36500
8*365=2920

which is 39420

My mistake. You are right. It should be 39819 for 6 January 2009

 

[milomak]

Assume cell A1 has 02/03/2007.

Excel is not seeing this as a number because the function =ISNUMBER(A1) returns FALSE.

However if I click on A1, press F2, press Home and delete the 0, it then returns as a number, ie =ISNUMBER(A1) returns TRUE.

It would seem that the answer is as simple as =VALUE(A1).

 

[Re83L]

If you have a date like 2008/12/31 and you want it as 20081231 :

Select the whole column that has the dates in, then go Data -> Text-to-cloumns (Hold ALT then A,E), select "/" as the delimiter.

Anyway, you should have three columns with "2008","12","31" in it.
Now go into a new column and type CONCATENATE(number1,number2,number3) and you should end up with 20081231. Copy this down for all dates.
This's obviously much easier to understand than 36562 or something similar.

 

[Smurfatefrog]

I would just paste the list into a text file, and then import them into excel in date format

 

[KobusDJ]

If I understand milomak correctly, he wants Excel to recognize 06/01/2000 (dd/mm/yyyy) as a date.

The format dd/mm/yyyy (06/01/2000) is not recognized by my Excel (2007) version as a date. Excel recognizes it as TEXT. Not even DATEVALUE() will change it to the desired date format.

The NUMBER value 06012000 does not work well as a date. (Millions of years in the future)

No problem with yyyy/mm/dd (2000/01/06). Excel auto recognize it as a date and ISNUMBER() returns true. Changing the format from DATE to NUMBER reveals 36531 in NUMBER format. (Worthless value, but good to know it works)

I managed to change dd/mm/yyyy with DATE((RIGHT(A1,4), MID(A1,4,2), LEFT(A1,2)) to end up with yyyy/mm/dd (in date format).