Previous Topic Next Topic

Change date format using Excel Macro

Closed
Harry - Sep 19, 2009 at 06:40 AM
 SUNIL - Jan 27, 2015 at 12:35 AM
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
Related:

4 responses

Answer 1 / 4
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Sep 19, 2009 at 08:56 PM
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.
0
Ashwin
Nov 25, 2009 at 01:19 AM
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.
0
SUNIL > Ashwin
Jan 22, 2015 at 08:25 AM
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
0
Answer 2 / 4
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Nov 25, 2009 at 05:30 AM
"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 .
0
Ashwin
Nov 25, 2009 at 05:56 AM
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
0
Answer 3 / 4
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Nov 25, 2009 at 07:33 PM
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.
0
Answer 4 / 4
Sunil
Dec 14, 2010 at 07:04 AM
For the SAP date format YYYYMMDD, just use the below formula


=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))
0
Kanishk
Jul 11, 2013 at 02:46 AM
It works perfectly..:)
0
SUNIL
Jan 27, 2015 at 12:35 AM
Thank u
0

Similar discussions

Date formatting in dd/mm/yyyy
nisha -
appu -

11 responses