Change general format to US date format
 

I have a list of contacts with data. One of the columns is for "date".
However, ONe of the lists I have has the date formatted as "general" and
when I try to format it as date it puts the wrong number in it.
Example: column G reads 10298 which is the date 01/02/1998. No
matter what I try I cannot get it to read as a date. Please help. I
have searched the forum over and over and cannot find the answer to
this issue. My anxiety is through the roof!
email help to woodlot4 at yahoo.com


--
woodlot4
------------------------------------------------------------------------
woodlot4's Profile: http://www.excelforum.com/member.php...o&userid=26613
View this thread: http://www.excelforum.com/showthread...hreadid=474866

Because the cell is formatted as General, Excel perceives the entry to
be an integer number and trims off the leading zero. If you dig the
mm/dd/yy date format, you can use this formula in a new column to parse
the 10298 into an Excel date:

=IF(LEN(A1)=5,LEFT(A1,1)&"/"&MID(A1,2,2)&"/"&RIGHT(A1,2),LEFT(A1,2)&"/"&MID(A1,3,2)&"/"&RIGHT(A1,2))

.... where A1 contains the 10298. This formula checks the length of the
entry and parses accordingly.

Hi,
Came up with the same answer as Dave but with a slight ajustment.
=IF(LEN(G1)=5,ABS(LEFT(G1,1)&"/"&MID(G1,2,2)&"/"&RIGHT(G1,2)),ABS(LEFT(G1,2)&"/"&MID(G1,3,2)&"/"&RIGHT(G1,2)))

Cheers!
JG


--
pinmaster
------------------------------------------------------------------------
pinmaster's Profile: http://www.excelforum.com/member.php...fo&userid=6261
View this thread: http://www.excelforum.com/showthread...hreadid=474866

You should be feeding year, month, day into DATE Worksheet Function.
In VBA you would feed into DateSerial
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm