excel vba to search dates in used range of all sheets within activeworkbook and change the format to YYYYMMDD

Do you need Excel Visual Basic for Applications code?

If you are happy to use an in-cell formula, you could place this in cell [C2]:
=TEXT(A2,"YYYYMMDD")

Copy down from cell [C2] to [C5176], then copy'n'paste from column [C] to column [A] As Values, then remove the contents of column [C] completely.

Please advise if you do need VBA code instead (or whether it is impractical to perform this copy'n'paste routine on all the worksheets in the workbook, or whether the dates are scattered across worksheets & are not all within one column like your example).

thanks fanpages. I am a big fan of your skills.

the problem with formula is that i have many columns in multiple sheets for example 21 sheets in workbook different columns, if i start using formula, it will take forever.

i need a macro solution to look for cells with dateformat and if found convert it. so somethign like for each cell in all worksheets in activeworkbook etc. i am not good with vba, as i am a newbie learner

i appreciate your help

Thank you for your kind words.

Providing code is not a problem.

I have something working already... but I would like to confirm the format of the replacement values.

In your column [ B ] the values are "String" (VarType 8) or, if you like, Text values (not numeric values).

Is that your intention?

Also, if any cell contains a Date & a Time are those to be ignored completely?  Is it just cells with a Date (only); & is this always in the [MM/DD/YYYY] format?

i want them exactly as it looks in Column B . not text value.

many thanks.

Column [ B ] are text values, formatted as "General".  That is my point.

Please widen the column so that it is, say, double the existing width, & you will see the values are not numeric; they are still aligned to the left edge of the cell.

If there were numeric they would be right-aligned.

If you want numeric values, that is fine; it is actually a lot easier.
If you wanted text values, that is more work (& takes longer to run).

Hence why I was checking.

Please review the values in column [ B ] in your example file & confirm if you want them as numbers or as text (as you presently are showing).

Thanks.

You may try something like this.....

Sub ChangeDateFormat()
Dim lr As Long
Range("A2", Range("A1").End(xlDown)).NumberFormat = "yyyymmdd"
End Sub

Select allOpen in new window

^ sktneer: The example worksheet is not what is required.

The cells to change could be anywhere within the UsedRange of any worksheet within the workbook; not just in column [A] of one worksheet.

sktneer,

i do not want format change,  i want the values to change, fanpages has already understood the requirement, as he pointed with formula solution ID: 40985734

i need vba solution, as i mentioned in my follow up comments.

Okay in that case you may try something like this....

Sub ChangeDateFormat()
Dim cell As Range
DoEvents
For Each cell In ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants, 23)
    If IsDate(cell) Then
        cell.NumberFormat = "@"
        cell = WorksheetFunction.Text(cell, "yyyymmdd")
    End If
Next cell
MsgBox "Done!", vbInformation
End Sub

Select allOpen in new window

sktneer

Is this only for active sheet? How this can be changed so that it runs the loop on all sheets of active workbook?

^ sktneer, I am curious about:

cell = WorksheetFunction.Text(cell, "yyyymmdd")

Have you found this to be faster than...

cell = Format(cell, "yyyymmdd")

Or is there another reason why you are preferring to use WorksheetFunction.Text(...)?

You can use any of them, both will produce the same output.

You can use any of them, both will produce the same output.

Yes, I know, but from my experience using the WorksheetFunction.Text(...) approach is a lot slower (especially over many cells in a loop).

It's matter of debate.

good score guys. i cannot earn points when you guys are online ;-)  questions are answred on the fly :-)

 just kidding

There are times when I see you in full flow, & I think the same, Prof! :)

Both you & sktneer always seemed to be first to the punch for me over the last week.

I'm sure the roles will be reversed again tomorrow.

Don't let previous contributions stop you making suggestions though.

thank you very much fanpages for writing this sophisticated code.

i used your code in the attached workbook.  it shows message box "done" while there is no change in the data.

can you please look into this?
Book1.xlsm

sktneer
i also have seen your code, i will test and will revert back.

When I open the workbook, I see potential changes in a few cells in column [A] (see below)...


Do you at least see those?

If your worksheet shows something different, then that is one problem.

However, if some of the cells have been changed, & some have not, then that is another problem.

Please can you confirm either way?

In the meantime, I will see if I can detect why the others have not been changed as expected.

Interesting!

When I run the code, the outcome is like this...


Just to confirm: What is your native Date format; are you in a region where [MM/DD/YYYY] is the standard setting?

MS-Excel stores Date values as [MM/DD/YYYY] regardless but, for reference, I am in the UK where [DD/MM/YYYY] is the default.

That may well have some bearing on the different results.  I will investigate further...

i am sorry. if my question was not well written.  

yes, i checked, it converts all dates if it is in month of december and only from 10 december to 31 december, however the example i gave in my original post was just an example of a date, and what i meant was that it should convert all dates, regardless of what month of year or day it is.

currently your code only works if the date is on or between 10 december to 31 december

sorry again for confusion

That is strange.

Sorry, there was not any confusion on my side.  I did not write the code to process dates just for that month.  It handles all (recognised) dates the same way!

What is the source of this data?  Is it possible that dates in December in your original (raw) data are formatted (Number Format; select a cell & use the [CTRL]+[1] key combination) differently to any other cell that contains a date?

Please could you attach the unchanged data; that which you had prior to the first use of the code that resulted in the outcome in ID: 40985989 (above)?

PS. Looking beyond the rows within the two images; row #37 (October) & onwards contain dates for non-December dates.  Scrolling to the end of the data shows November in cell [A5166], for instance.

i created the attached file.  i put date from 1st jan 2015 to 31 dec 2015 then then run the macro
please see the next sheet, it converts the dates only for the last quarter i mean October, november and december only dates between 10 to 30 or 31

please see attached file.  orignal data and next sheet macro converted

my computer time and date is default windows format is United States  m/d/yyyy

i am not sure if this could be this cause.
Book1--1-.xlsm

^ When I execute the code after opening that most recent workbook, all the values in both worksheets are converted as expected.

This must be a UK/US date different issue.  Although, I could understand if an issue occurred at the 13th day of the month, but before the 10th date/month.  That makes no sense at all, presently.

So, it is still very strange that is picks those specific dates to convert for you, & none of the others.

How did you get on with sktneer's code?

Is the outcome the same as mine, or as originally planned?

(Edit) I cannot see any difference in the rows of your [Original] worksheet.  Even setting my PC to US regional formats &/or changing the date formats in column [A] to the US standard. (/Edit)

As a suggestion, please could you remove/comment-out these three lines:

                    Case (Int(objCell.Value) <> objCell.Value)                  ' Checks Date only (or Date with Time at 00:00)
                    Case (Len(objCell.Text) <> 10)                              ' Checks length of 10 characters; so Time at 00:00 is ignored)
                    Case (Len(Replace(objCell.Text, "/", "")) <> 8)             ' Checks Date contains two "/" characters

Select allOpen in new window

Then re-run the code on the [Original] data again?

Thanks.

thanks fanpages and i felt indebt of troubling you with this solution.

you are right.  it is caused by US date format and UK Date format.

when i changed my computer windows date time setting to UK then your macro worked perfectly.

so the problem is when my system date format is US then your code does not work.

Sktneer's code works regardless of UK or US format. it works in both.

i tried with modification of the 3 lines as mentioned by you, still the result does not work with US date format.

OK, please use sktneer's code & accept his proposal as the solution.

No hard feelings! :)  I would really like to know why this is happening though.  As I said, I could understand issues around the 13th day of a month, but not the 10th month of the year.

I would be interested to learn the outcome of removing the three lines I mentioned above, if you have the time, please.

If not, that's fine.

---

Just seen your last comment.

How very strange!

OK, one to be wary of in the future.

Thanks for sticking with it though.

Oh... hang on...

Is your date format [m/d/yyyy], rather than [mm/dd/yyyy]?

i.e. No leading zero for single-digit month or single-digit day?

yes fanpages.

removing those three lines, fixed the problem. you got that right.

thank you very much to you fanpages and sktneer

thanks fanpages for being patient with my ignorance. your professionalism is very much appreciated.

You're very welcome.  No need to apologise.  I introduced the problem being overly-cautious with checking the values to ensure the code did not change anything that looked like a Date, but wasn't, or a value that contained both a Date & a Time value.

Also, sorry, your comment (ID: 40986061) made me think you had already removed the three lines, so that made me even more confused! :)

The offending line, I think is just this one:

 Case (Len(objCell.Text) <> 10)                              ' Checks length of 10 characters; so Time at 00:00 is ignored)

That is, it is checking that the data is exactly ten characters in length.

For dates for the first 9 days of the month, that will not be true.
Also for months before October that will not be true.

Yes, I had assumed you would be using leading zero(e)s for days & months.

Phew!  That's a relief.

If you wish to reinstate the other two lines of code, they should continue to work as I originally intended.

However, if you are happy with removing all three, & see no detriment to doing so, then that's OK too.

Sorry for the hassle.

I will know for next time I see a similar question posed by somebody in another region now.

Thank you :)

thanks again fanpages.

removing one line Case (Len(objCell.Text) <> 10)   did not work.  when i remove Case (Len(objCell.Text) <> 10)   and Case (Len(Replace(objCell.Text, "/", "")) <> 8) then it worked.

so my final code to use will be your original code deleting the abovementioned two lines.

interesting thread and comments.

i also learned something.