Change date format using Excel Macro Closed

Question

Hello,

In SAP BI I have created a BEx Query. We can see BEx reports in MS Excel. Date format in the report is DD/MM/YYYY and I want it to be displayed like YYYYMMDD. Please help me how to write Excel Macro to display the required date format.

Many thanks in advance.

Harry

venkat1926 · Answer

custom format the cell
select the cell ,range or column 
click format (menu)-cells-number-custom 
on the right side under "type" 
type this 
yyyymmdd
click ok 

study the custom format under help it is very useful.

venkat1926 · Answer

"cusotm" iteslf means that specially made like cutom pants and custom shirts.  Perhpas you can tinker with control panel -regional and language setup. I would not do this.  If you want for the whole workbook you can have a small macro. or better highlight all cells in a sheet (contsrol+A) and then make date format this one.  do some experiments except tinkering with regional setup .

venkat1926 · Answer

in format(menu) there is a submenu "autoforamt" .  you can do some experiments with this .  I have not done this.  ther relevant webpage you can see is 

https://www.ozgrid.com/Excel/free-training/excel-lesson-33-basic.htm

Now I wonder even if you do autoformat and send it to your user whether she will see that format in her computer. park this problem clearly in the newsgroup 
"excel questions" or make google search.

Sunil · Answer

For the SAP date format YYYYMMDD, just use the below formula  


 =DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))

Change date format using Excel Macro

4 responses

Subscribe To Our Newsletter!