convert date column to general value

I am trying to upload excel data to a databse which only accepts general values. Some one tried to help me with formulas as per attached word document but it is not working. I get #value as result. (cell colored red)

need to Import this Excel sheet into an AMS Database system - All Cells NEED "general" value even if its numbers.

Tried doing manually but main sheet has 3500 entries...:-(

I am sure there has to be easier way to do this.

Your help is higly appreciated

Thanks much

vikkam confused:

Try

=datevalue(D2) 
=datevalue(g2)

and copy down


Or better way, copy an emty cell, then select entire column with dates then right click > paste special > add > ok
Then apply dd mm yy format to these values

Tried =datevalue(D2)
but it gives me value as 1

Is it possible to do it in one cell and show as not clear.

Thanks for prompt response.

What is the default date format of your locale - dd/mm or mm/dd ?

There are inconsistencies in the dates - some are listed as mm/dd/yyyy and others as dd/mm/yyyy (this precludes use of Text to Columns conversion unfortunately)

Assuming default date format of dd/mm/yyyy then perhaps:

C2: =IF(ISNUMBER(D2),D2,DATE(RIGHT(D2,4),LEFT(D2,2),MID(D2,4,2)))
copied down
cells formatted as General

However... there are still some invalid entries to account for (00/00/0000, 00/00/001 etc...) - should these be 0 or something else ?

On an aside 1 is the correct DateSerial for Jan 1 1900 on a 1900 Date System.

copied the formule but no results are displayed.
Is it possible to do on one cell in spread sheet ad show ?

please post your version with suggested formulae in place.

want it to read once exported - 12/10/2009 - not the 27431..and so on

Basically I am trying to write dat in same format mm/dd/yyyy but as text.