Change date format using Excel Macro

[Closed]
Report
-
 SUNIL -
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

4 replies

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
801
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.
HI Venkat,

If I do this change in Custom it doesnt effect for all systems, if am not wrong. If we want the changes effected globally do you have any solution, if so can you share with me

Regards,
Ashwin.
> Ashwin
Hi Ashwin,

I have a lot of file of excel where I cant do manually I looking for a macro where all files will be in one folder and just run the macro,please help me out

My date format in excel is 21.01.2015 but I want in 1/21/2015
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
801
"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 .
Thanks for your reply venkat. Regional settings in control panel will work and effects globaly in my system. But if client opens the same report from thier machine, the regional settings option doesnt work right. So any solution other than this solution to the client.

Regards
Ashwin
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
801
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.
For the SAP date format YYYYMMDD, just use the below formula


=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))
It works perfectly..:)