Date Format

[Karan001]

Hi Expert,
Currently what ever data i am downloading from any system the excel file date format is coming like 03.03.2011,But i want the dowloaded excel file date format like 03/03/2011

May i know how i can make the excel downloaded date format like 03/03/2011 as default.

Regards,
Karan

 

[SydneyGeek]

When you import a text file, you have the option to select the data type for each column.

Assuming the file is .txt do this:

Open the text file via Excel. Select whether the file is delimited or fixed width, then Next.
Pick the delimiter or move the fixed width column breaks, then Next.
Now click the date field. Choose the Date option, and then use the drop-down to pick how the dates are formatted.
Click Finish, choose where to extract the data, and you should be done.

Denis

 

[Karan001]

Hi Denis,
I tried your suggestion but it is not working,because there is no option of date like dd/mm/yyyy.Here i want the date format with seprator in excell.


Regards,
Karan

 

[SydneyGeek]

Pick the DMY format.

Denis

 

[Karan001]

Hi Denis,
After Getting DMY i am not getting any change in the format i am getting only dot (.) instead of /.

i am looking a setting where i can get / in date format as default in excel


Karan

 

[SydneyGeek]

That's strange. It works whenever I use it, and it ran with a test file when I posted the reply.

The other option that has worked for me, is to create 3 blank columns to the right of the imported dates.
Select the date column (assume it's C for this example) and use Text To Columns to split the dates on the . character.
Now, in F2 (in this case), put this formula: Date(E2,D2,C2)
Fill down.
Select column F, copy, Paste Special > Values, then delete C:E.

Denis

 

[yuvastanza]

That's strange. It works whenever I use it, and it ran with a test file when I posted the reply.

The other option that has worked for me, is to create 3 blank columns to the right of the imported dates.
Select the date column (assume it's C for this example) and use Text To Columns to split the dates on the . character.
Now, in F2 (in this case), put this formula: Date(E2,D2,C2)
Fill down.
Select column F, copy, Paste Special > Values, then delete C:E.

Denis

Hey Karan001

Try to set your settings as Start-->Control Panel-->Regional and Language options.

Fix the date format you like and restart the systems.

Here after whenever you download data. the date in data will be appear as you fixed.

Try this.