excel - how do i change date format 4

[tempo1]

Hi everyone,
I have data imported from SQL SERVER, to an excel sheet.
Date is shown as mm/dd/yy. I want it to be formatted as:
dd/mm/yy. I tried "format", "number" "date" but it didnt work.
Can any one show me how to do that correctly ?
Thanks a lot.

 

[Gavona]

You probably have text that looks like a nate rather than a number that represents a date - see FAQ68-5827: Why do Dates and Times seem to be so much trouble?

Possible solutions:
Use a helper column with a formula involving the date() and Mid() functions.
Never tried it but you might just be able to use DATA,text to columns to convert
Import the data in a way that explicitly defines the column as a date - I have no idea if that is possible - sorry.

Hope that helps,


Gavin

 

[yogia]

Hi temp01:

To elaborate the DATA|Text_to_column approach as suggested by Gavin ...

1) select the Date entries in one column
2) invoke DATA|Text_to_columns ... click Next, then Next again
3) in Step 3 of 3, in Column Data Format, choose the option Date and in the Drop_Down List to the right select DMY
4) optionally change Destination as another cell as the starting cell for the converted values
5) click on Finish

That should do it.

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[tempo1]

Hi,
I trie to do what you suggested vogia but that only changed
dates where month was less than 12.
thanks anyway

 

[Gavona]

Maybe you could post a few examples of dates that worked and those that did not.


Gavin

 

[AnotherHiggins]

As Gavin said, you actually have text strings, not numbers.

This is a fairly common problem with imported data. A way to get around this is to use the 'times one fix'.

[ul]
[li]In an empty column to the right of all the imported data, type [blue]1[/blue][/li]
[ul]
[li]If you don't have specific alignment set for the entire sheet, the [blue]1[/blue] should be right-aligned - this indicates that the cell is formatted as a number[/li]
[/ul]
[li]Copy the cell where you just typed [blue]1[/blue][/li]
[li]Highlight the cells that are giving you trouble[/li]
[li]go to Edit > Paste Special > Values + Multiply[/li]
[li]NOW you can format the cells as you wish[/li]
[/ul]

NOTE: Any empty cells that you have selected before pasting will be changed to zeros. So don't select an entire column.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[yogia]

I trie to do what you suggested yogia but that only changed
dates where month was less than 12.

Hi temp01:

In that case try ...

  |A           |B           |C 
--|------------|------------|--------
  |2/19/2007	19/2/2007
  |1/31/2007	31/1/2007
  |1/5/2008	5/1/2008

my formula in cell B2 is ...
=MID(A1,FIND("/",A1)+1,LEN(A1)-5-FIND("/",A1))&"/"&LEFT(A1,FIND("/",A1)-1)&"/"&RIGHT(A1,4)

This is then copied down

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[yogia]

Or

just use Custom Number Format "dd/mm/yyyy" if the starting entries are true dates (numerics)

or use the TEXT function as in ...

   |A           |B 
---|------------|------------
   |2/19/2007	19-02-2007
   |1/31/2007	31-01-2007
   |1/5/2008	 05-01-2008

formula in cell B2 is ...
=TEXT(A1,"dd-mm-yyyy")

this is then copied down.

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[AnotherHiggins]

Sorry - I typed that last response before seeing Yogia's post of the replies.

->I trie to do what you suggested vogia but that only changed dates where month was less than 12.

That tells me that your computer is set up for "European" date format (dd/mm/yy) and isn't playing well with the "American" style input (mm/dd/yy).

You could try to change you Regional Settings to English (American), but that seems like overkill, especially if this is data that you'll be importing repeatedly.

Instead, I think you'll probably do best to use a helper column with a formula.

Keep in mind that the TEXT formula returns a text string, not a number.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[SkipVought]

"I have data imported from SQL SERVER, to an excel sheet.
Date is shown as mm/dd/yy."

In your SQL code, format your DATE string in an UN-AMBIGUOUS FORMAT...
[tt]
yyyy/mm/dd
[/tt]
will be CONVERTED correctly in all cases.

Skip,

_[red][/red]

 

[tempo1]

Thanks a lot !
Sorry to say, it was more complicted than i thought repairing the error in the excel sheet so i changed my
sql table to make the date at the desired format.
Still i'm very gratefull for your efforts.

 

[Welshbird]

I tend to use =value(datecell) in a spare cell, then copy=>paste special=>values and then format as date. It seems to work in the majority of cases no matter as to the initial format (or at least, it hasn't failed me yet!)

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[tempo1]

Thanks.