Change general format to US date format

  • Thread starter Thread starter woodlot4
  • Start date Start date
W

woodlot4

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" an
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. N
matter what I try I cannot get it to read as a date. Please help.
have searched the forum over and over and cannot find the answer t
this issue. My anxiety is through the roof!
email help to woodlot4 at yahoo.co
 
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
 
You should be feeding year, month, day into DATE Worksheet Function.
In VBA you would feed into DateSerial
 
Back
Top