Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to format dates
I'm trying to create a macro that will format some dates on my
spreadsheet... Column 13 (M) has dates formatted as: Jan 23 2007 12:00AM - this is always the same length, so Jan 3 would be Jan 3 2007 12:00AM Column 19 (S) has dates formatted as: 2070109 (which is 1/9/07) Can someone please help me write a macro to format them as mm/dd/yy, w/o the time? I hate to post w/o providing some code but I don't really know where to start. Thank You, Dan |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to format dates
Sub formatcolumns()
Range("m1,s1").EntireColumn.NumberFormat = "mm/dd/yyyy" End Sub -- Don Guillett SalesAid Software "Dan R." wrote in message oups.com... I'm trying to create a macro that will format some dates on my spreadsheet... Column 13 (M) has dates formatted as: Jan 23 2007 12:00AM - this is always the same length, so Jan 3 would be Jan 3 2007 12:00AM Column 19 (S) has dates formatted as: 2070109 (which is 1/9/07) Can someone please help me write a macro to format them as mm/dd/yy, w/o the time? I hate to post w/o providing some code but I don't really know where to start. Thank You, Dan |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to format dates
Don I tried your code and the result for 2070109 was 10/06/7567 and it
didn't make any changes to Jan 23 2007 12:00AM. I also tried your suggestion Jim but since Excel doesnt recognize the current formatting I dont think it can make any changes to it. But thanks for the suggestions guys, I guess I can probably just use a mid formula or something to do it. Thanks -- Dan On Jan 24, 12:32 pm, "Don Guillett" wrote: Sub formatcolumns() Range("m1,s1").EntireColumn.NumberFormat = "mm/dd/yyyy" End Sub -- Don Guillett SalesAid Software "Dan R." wrote in ooglegroups.com... I'm trying to create a macro that will format some dates on my spreadsheet... Column 13 (M) has dates formatted as: Jan 23 2007 12:00AM - this is always the same length, so Jan 3 would be Jan 3 2007 12:00AM Column 19 (S) has dates formatted as: 2070109 (which is 1/9/07) Can someone please help me write a macro to format them as mm/dd/yy, w/o the time? I hate to post w/o providing some code but I don't really know where to start. Thank You, Dan- Hide quoted text -- Show quoted text - |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to format dates
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to format dates
Excel doesn't recognize 2070109 as a date, it thinks you mean the 2070109th
day since 1/1/1900. You would have to use something like text to columns (data menu), use Fixed Width to split it into 2 and 070109, not import the column with just 2, and under Column Data Format, choose Date with the YMD option. Now Excel knows it's a date. The "Jan 23 2007 12:00AM" might have come in as text. Format the cell to have General horizontal alignment: if it's left-aligned, it's text, not a numerical date. Never mind, I just checked, and Excel doesn't recognize it as a date. It needs a comma after the day and a space before "AM", like this: "Jan 23, 2007 12:00 AM". You'll have to find/replace "AM" and "PM" with " AM" and " PM", and " 200" with ", 200", and then Excel will know what you're up to. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Dan R." wrote in message ups.com... Don I tried your code and the result for 2070109 was 10/06/7567 and it didn't make any changes to Jan 23 2007 12:00AM. I also tried your suggestion Jim but since Excel doesnt recognize the current formatting I dont think it can make any changes to it. But thanks for the suggestions guys, I guess I can probably just use a mid formula or something to do it. Thanks -- Dan On Jan 24, 12:32 pm, "Don Guillett" wrote: Sub formatcolumns() Range("m1,s1").EntireColumn.NumberFormat = "mm/dd/yyyy" End Sub -- Don Guillett SalesAid Software "Dan R." wrote in ooglegroups.com... I'm trying to create a macro that will format some dates on my spreadsheet... Column 13 (M) has dates formatted as: Jan 23 2007 12:00AM - this is always the same length, so Jan 3 would be Jan 3 2007 12:00AM Column 19 (S) has dates formatted as: 2070109 (which is 1/9/07) Can someone please help me write a macro to format them as mm/dd/yy, w/o the time? I hate to post w/o providing some code but I don't really know where to start. Thank You, Dan- Hide quoted text -- Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dates format | Excel Worksheet Functions | |||
CONVERT DATES FROM EUROPEAN FORMAT TO US FORMAT | New Users to Excel | |||
Format text 'dates' to real dates | Excel Worksheet Functions | |||
the dates on cell format make different dates. | New Users to Excel | |||
Macro changes the format of dates | Excel Programming |