How to Change Date Format.

[premjeetgupta]

Hello Seniors,

I have date like following

12/08/2014 17:00

and i want to change the format as below.

Dec 8, 2014 5:00:00 PM

I have tried to change through format tool but there is no option showing to change format like above.

 

[NARAYANK991]

Hi Gupta ,

You should get it through the following custom format :

mmm d, yyyy h:mm:ss AM/PM

unless your system date is dd-mm-yyyy or dd/mm/yyyy , in which case your displayed date will be Aug 12, 2014 5:00:00 PM.

Narayan

 

[premjeetgupta]

Awesome.. Narayank Sir.

Its done like magic.

 

[premjeetgupta]

Hi Narayan Sir,

I need your one more small help. Can u let me know ? Is there any way i can add suggested format in custom format. Because every time i have to go to format option & put the suggested format it takes too much time i have 100 over sheet in excel file.

 

[NARAYANK991]

Hi Gupta ,

You can set up what are called Styles , but if you already have a hundred sheets , then it will not help ; Styles will help if you are going to add new sheets , and you want the same style to be used in them.

If your data is going to be in the same column in all the sheets , then a macro will make it easy to apply a format to all the sheets at one go.

Narayan

 

[premjeetgupta]

Actually i have 50 above excel files every file contains 5-7 sheet. But my data is in same fixed column. How style work ? Also i am zero in VBA or Macro so i have no idea how to do with the same.

 

[premjeetgupta]

A sample file for your reference.

 

[NARAYANK991]

Hi Gupta ,

See this file.

I have put in the macro ; it's called Change_Date_Format , and it's shortcut key is CTRL d.

If you press CTRL d , all the worksheets in the workbook will have their column D changed to the preset format.

If you don't like the predefined format , and wish to change it , don't save the file ; instead change the format in the code and re-run the macro by pressing CTRL d.

Narayan

 

[premjeetgupta]

Thanks a ton, It saved my 15 Min. with every files.