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....

 

[trillium]

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.

Thanks for the extra advice! Always appreciated!
I did check and the code is working on the right sheet/column yes.

What I have learned about now about how the data collected in SharePoint is being exported into Excel is that the Created and Modified columns don't need to be reformatted in the same "format" as they are in SharePoint (m/d/yyyy), that when I import them and do the other code I have to remove the time stamp from them, they are fine to stay in a normal date format, the filter will still work.

What I'm having trouble with NOW is the DUE DATE column! The collection form where the user inputs the data is formatted automatically as m/d/yyyy (no time stamp) and for SOME REASON not ALL of these are not converting to dates! I tried the trick to "Copy 1, paste multiply" and only some convert to the serial number date format. The rest stay as text. I really don't want to have to start using Text to Columns to split them and recombine them... I've been looking behind the scenes in SharePoint but even when I change the "view" of how the DueDate field is being exported it doesn't change them. And I don't have access to modify the actually intake form so I'm stuck with how the programmer programed the Due Date...

Thank god it's Friday!

 

[trillium]

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...

Hi, for the dates in the Created and Modified columns, Excel will turn them all into serial number format.

For the dates in my Due Date column, some come and can be reformatted as serial numbers but most are stuck it seems as TEXT "m/d/yyyy" (with the /) so I've got a mixed bag of formats in that column.

 

[Hercules1946]

Hi, for the dates in the Created and Modified columns, Excel will turn them all into serial number format.

For the dates in my Due Date column, some come and can be reformatted as serial numbers but most are stuck it seems as TEXT "m/d/yyyy" (with the /) so I've got a mixed bag of formats in that column.

You will have a system date format on your computer that is used by Excel to show a date for a selected cell in the formula bar. The version of this displayed in the cell can be different depending how you choose to format it.
The problems your describing sound like some of these dates have been entered into the cells in an unrecognised format (ie not the SDF) and Excel accepts these as text rather than dates.
To resolve this, you could create a helper column to rearrange the date characters in line with the SDF.

Suppose I have 03/21/2015 in A1 as a text value that I can't change to a date. In B1, I can put in the following to convert A1 to my SDF in the UK which is dd/mm/yyyy:

=DATEVALUE(TEXT(MID(I4,4,2)&"/"&LEFT(I4,2)&"/"&RIGHT(I4,4),"dd/mm/yyyy"))

 

[Hercules1946]

=DATEVALUE(TEXT(MID(I4,4,2)&"/"&LEFT(I4,2)&"/"&RIGHT(I4,4),"dd/mm/yyyy"))

Sorry - There is an error in the above. Where it says I4, replace with A1.

 

[Hercules1946]

For the dates in my Due Date column, some come and can be reformatted as serial numbers but most are stuck it seems as TEXT "m/d/yyyy" (with the /) so I've got a mixed bag of formats in that column.

Had another thought that may explain this. If my computer has the UK standard (short) date format dd/mm/yyyy, Excel will use that to identify arriving text that complies with this format as a date.
So...

1 21/03/2015 = Valid Date (stored as 42084)
2 03/21/2015 = Invalid date because there is no month 21. (stored as "03/21/2015")

Once Excel has stored the date by using its number, then its always clear what the date is intended to be irrespective of the format used, and as a result, it can be freely reformatted or correctly used in calculations.
If the data is stored as text, then its no longer possible (in every case) to establish the correct date (05/06/2015 could be 6th May or 5th June).
So your problem might be that some of the Due Dates were invalid in relation to your system date format, and are recorded as text, which can be converted using my string handling formula.

 

[trillium]

Thank you so much Hercules1964, I'm sure it has to do with something like that. We would in offices across the country, one region is in Quebec, so it's quite possible that people have different region settings! I'm relieved to have a fix now for those TEXT situations!!!!!!

 

[Hercules1946]

Glad to be of help!