Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date Format Macro Problem
To all,
I have a worksheet where column A is a series of dates. These dates can be in the format 2006/10/02 yyyy.dd.mm or 10/02/2006 dd.mm.yyyy. etc. I want to be able to change them all so that they equal the dd.mm.yyyy format. Is there anyway to do this using VBA, and not the cell format options, which do not seem to work anyway. Thanks in advance for your help, regards Joseph Crabtree |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date Format Macro Problem
Sub changedateformat()
Sheets("sheet1").Select 'change the sheetname here ActiveSheet.Range("a1:a20").Select 'change here which cells you want to format Selection.NumberFormat = "mm/dd/yy" End Sub You can either call this to run from another macro or create a command button to call this when it is clicked. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date Format Macro Problem
Hi, Im afraid that doesn't work. It keeps the dates exactly as they are. Any ideas? Thanks On Dec 20, 2:22 pm, "keri" wrote: Sub changedateformat() Sheets("sheet1").Select 'change the sheetname here ActiveSheet.Range("a1:a20").Select 'change here which cells you want to format Selection.NumberFormat = "mm/dd/yy" End Sub You can either call this to run from another macro or create a command button to call this when it is clicked. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date Format Macro Problem
I have tested this on a list of cells with values like below;
2006/02/02 02/02/2006 04/01/01 01/01/2004 etc etc and it works fine. The only ones it doesn't work on correctly are dates entered with . instead of / or - so 2006.12.12 doesn't change. What format are your dates in currently? Does it change any of the dates or none? Did you change the sheet name in the code? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date Format Macro Problem
Hi,
Ive used a version of text to columns to format the dates into the correct format. Thankyou very much for your help anyway. Regards Joseph Crabtree On Dec 20, 2:44 pm, "keri" wrote: I have tested this on a list of cells with values like below; 2006/02/02 02/02/2006 04/01/01 01/01/2004 etc etc and it works fine. The only ones it doesn't work on correctly are dates entered with . instead of / or - so 2006.12.12 doesn't change. What format are your dates in currently? Does it change any of the dates or none? Did you change the sheet name in the code? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date Format Problem | Excel Discussion (Misc queries) | |||
Excel Format Problem - Date Overrides Format | Excel Discussion (Misc queries) | |||
date format problem using Macro | Excel Discussion (Misc queries) | |||
Date Format Problem | Excel Discussion (Misc queries) | |||
Date format Problem Macro | Excel Programming |