ExcelBanter - Macro to format dates

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Macro to format dates (https://www.excelbanter.com/excel-programming/381840-macro-format-dates.html)

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

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 -

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 -