m/d/yyyy date format not working with VBA, but does manuall?

[trillium]

Hi
I have to convert a serial date into this specific format "m/d/yyyy" for an entire column. When I do it manually through Excel Format Cells / Custom and create "m/d/yyyy" is works. When I write VBA code to convert it, it doesn't work?

Columns("L:L").Select
Selection.NumberFormat = "m/d/yyyy"

This is very frustrating, working with dates....

 

[JoeMo]

Try it this way

Columns("L").NumberFormat = "m/d/yyyy"

Or this way

Range("L:L").NumberFormat = "m/d/yyyy"

 

[Hercules1946]

Hello
Try "mm/dd/yyyy". I think Excel is auto-correcting the error, but VBA does not.

 

[trillium]

didn't work

 

[trillium]

Sorry JoeMo - the

Columns("L").NumberFormat = "m/d/yyyy" / Range("L:L").NumberFormat = "m/d/yyyy"

Didn't work

didn't work

 

[Jonmo1]

ok, can you describe "doesn't work" ?

Do you get an error? What error?

Does the format actually get applied to the cells, but the values just don't appear correctly according to that format?
Or does the format NOT get applied to the cells.
i.e. After you run the code, select a cell in the range, and go to Format - Cells. Is the correct format shown?

What does this formula return
=ISNUMBER(L2)

Where L2 is one of the cells containing your dates.

 

[trillium]

Hello
Try "mm/dd/yyyy". I think Excel is auto-correcting the error, but VBA does not.

Hercules1946 - that one did the trick! Thank you!

 

[saurabh726]

trillium ,

You said serial dates?? Does excel recognise the values which they have as dates or they are in number format like MMDDYYYY??

Or which format they are in??

Saurabh...

 

[trillium]

Sorry, you are right, I wasn't clear on what "Doesnt' work" means.

What I'm trying to do it remove the time stamp from inside a date that is generated in SharePoint, then exported into Excel (custom formatted as m/d/yyyy hh:mm am/pm)

Straight re-formatting doesn't work cuz the time part stays within the serial date # as a decimal (i.e 42383.4576), and it's messing up the report dashboard that was build and is just wanted straight dates to count # of case late for example.

Sharepoint is formatting the due date, created and modified dates as m/d/yyyy and that is how the data is also being filtered, so that's why I was trying to keep the formatting the same.

SO... to answer you... format doesn't seem to be applied (when I checked it's formatted as default date). And they do show as numbers when I check and will turn into serial date as well.

Any other suggestions when working with dates with timestamps coming from SharePoint?

ok, can you describe "doesn't work" ?

Do you get an error? What error?

Does the format actually get applied to the cells, but the values just don't appear correctly according to that format?
Or does the format NOT get applied to the cells.
i.e. After you run the code, select a cell in the range, and go to Format - Cells. Is the correct format shown?

What does this formula return
=ISNUMBER(L2)

Where L2 is one of the cells containing your dates.

 

[Hercules1946]

didn't work

Are you sure that your code is operating on the correct worksheet or does it fire only when some event occurs ?
This is dependant upon things like:
1) where the code resides
2) is the (correct) worksheet open/active?
3) What workbook/worksheet is referenced in the code?

To check these I would place a break in the code after column "L" is selected. When the code stops, refer back to correct sheet to see if col "L" is in fact selected.