Changing Cell Format from General -> Date

**kfk5012** · 09-28-2010, 11:51 AM

The goal of this is to be able to sort by date..

Currently I have a column of dates/times that, when I look under "Format Cells", are formatted as "General". Here is an example: 14/07/2010 21:20:08

(The way the data was churned out, the date is presented as dd/mm/yyyy)

Ideally, I'd like to format the cells as mm/dd/yyyy (i.e. 07/14/2010), but cannot do so since the information was not originally in Date format.

Any ideas? Thanks in advance..

**oldchippy** · 09-28-2010, 11:54 AM

Hi,

Select the column of dates, go to Data > Text to columns, in step 3 select M/D/Y format to turn text values into dates

**DonkeyOte** · 09-28-2010, 11:58 AM

OC, given the Time I suspect TtC is not viable.

I suspect you will find "valid" dates will have had month & day transposed, those stored as text will be "invalid" dates given US format (ie month > 12).

Assuming dates in A1, A2 etc

B1: =IF(ISNUMBER(A1),DATE(YEAR(A1),DAY(A1),MONTH(A1))+MOD(A1,1),0+REPLACE(REPLACE(A1,1,3,""),4,0,LEFT(A1,3)))
copied down

Once done you can copy the values in B and paste over originals in A (values only)

**kfk5012** · 09-28-2010, 12:07 PM

Hm DonkeyOke I tried the formula but it doesn't seem to have worked..

Data in A1: 13/07/2010 00:32:43
Pasted formula in B2
Resulting data in B2: 40372.02272

**DonkeyOte** · 09-28-2010, 12:11 PM

If you format B2 as Date Time etc you should find it is correct.

In Excel Dates are Integers and Time is Decimal (1 being equiv. to 24 hours of course).

The Date Format is simply a "mask" which Excel uses to alter the appearance of the underlying numeric value.

**kfk5012** · 09-28-2010, 12:16 PM

Ohh gotcha. Works perfect. Thank you!!!

**markstradtner** · 09-29-2010, 04:28 PM

I have a simple chart issue. My simple chart is 3 rows and 4 columns= 12 values.
The valules are being added to the previous value and are charting as a sum of the value after being adder to the previous value.
IE: Row A = 2 ,3 ,4 ,6
Row B = 1, 2, 1, 1
Row C = 2, 3, 4, 1
The first column has the first point at 2, the second point at (2+1) 3, the third point is (3+2) 5

the second Column has the first point at 3, the second point at (3+2) 5, and the third point at (5+3) 8

and so on......

I believe this is some setting in how the numbers are charted but can not find anything to help. What am I missing? Help!