Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 406
  • Last Modified:

change the format of a column in a excel spredsheet

Hi Experts, I have a excel SS that is created and one of the fields is coming out as mm/dd/yy how can I write a macro to change that columns format to yyyymmdd. Thanks for any help.
0
needhelpfast569
Asked:
needhelpfast569
  • 3
  • 2
1 Solution
 
helpfinderIT ConsultantCommented:
Hi, do you need to perform this only with macro for some special reason?
because you can simple format cell(s) and set custom format yyyymmdd
0
 
needhelpfast569Author Commented:
the file is being created from access and the file is created as a new file each time and overwrites the existing file. I tried that one.
0
 
helpfinderIT ConsultantCommented:
so try this macro

Sub FormatChange()
    Range("A1:A3").Select
    Selection.NumberFormat = "yyyymmdd"
End Sub
0
 
needhelpfast569Author Commented:
Hi helpfinder, that works great do you know how I can set that macro to run after the file is built from ms access?
0
 
helpfinderIT ConsultantCommented:
Unfortunately this I do not know. But I know how to run macro when that file is open, if it is enough for you.

You just open that Visual Basic and double click ThisWorkBook. From left drop down menu where General is by default choose Workbook and from right drop down menu choose Open. I generates a part of code for you. BEtween the lines you will call the macro I posted above, so it will looks like:
Private Sub Workbook_Open()
Call FormatChange
End Sub

This makes macro to run every time file is opened

I am attaching also sample where this is working (try to change dates in A1-A3 e.g. to 1.1.1960, 12.4.1984 etc, save the file, close it and reopen.
format-date.xlsm
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now