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

I need help with converting the 12/31/2015  date format to 20151231

basically a macro to look into usedrange of each sheet in activeworkbook and if cells with date found then convert that date to value like YYYYMMDD

for example a cell with serial number of 41639 that looks like a date of 12/31/2015  should change to value of 20151231

attached is the example file.

thanks alot
Book1.xlsx
LVL 6
FloraAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

[ fanpages ]IT Services ConsultantCommented:
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).
FloraAuthor Commented:
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
[ fanpages ]IT Services ConsultantCommented:
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?
Amazon Web Services

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

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

many thanks.
[ fanpages ]IT Services ConsultantCommented:
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.
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You may try something like this.....

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

Open in new window

[ fanpages ]IT Services ConsultantCommented:
^ 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.
FloraAuthor Commented:
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.
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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

Open in new window

FloraAuthor Commented:
sktneer

Is this only for active sheet? How this can be changed so that it runs the loop on all sheets of active workbook?
[ fanpages ]IT Services ConsultantCommented:
Hi Flora,

In case my query about Text versus Numeric output in column [ B ] (or anywhere that a cell value is replaced) was not clear, I have provided a routine that can handle both outcomes.

The way the code (shown below, also within the attached workbook) executes presently, is to convert all Dates to Numeric representations in [YYYYMMDD] format, as I think you are intending.

However, please see some notes upon how to use the routine to produce Text values that I have added within column [D] of the [Sheet1] worksheet within the attached workbook.

Column [C] contains a simple formula to show, after the routine has executed, that even though column [A]'s value looks the same as the corresponding ("Desired") value in column [ B ], the two are not identical.

It will probably make more sense once you have open the attached workbook, & run the routine at least once.

Option Explicit

Private lngErr_Number                                    As Long
Private strErr_Description                               As String
Public Sub Q_28715859()

' -------------------------------------------------------------------------------------------------------------------------------------------------------------------------
' [ http://www.experts-exchange.com/questions/28715859/excel-vba-to-search-dates-in-used-range-of-all-sheets-within-activeworkbook-and-change-the-format-to-YYYYMMDD.html ]
'
' Question Channel: Experts Exchange > Questions > excel vba to search dates in used range of all sheets within activeworkbook and change the format to YYYYMMDD
' Topic Area:       [ http://www.experts-exchange.com/topics/ms-excel/ ]
'
' ID:               Q_28715859
' Question Title:   excel vba to search dates in used range of all sheets within activeworkbook and change the format to YYYYMMDD
' Question Dated:   2015-09-20 04:15 PM
' Question Asker:   Flora
' Asker Profile:    [ http://www.experts-exchange.com/members/Flora20.html ]
'
' Solution posted:  20 September 2015 by fanpages
' Expert Profile:   [ http://www.experts-exchange.com/members/fanpages.html ]                                             [ http://www.experts-exchange.com/M_258171.html ]
' -------------------------------------------------------------------------------------------------------------------------------------------------------------------------
' Copyright:        (c) 2015 Clearlogic Concepts (UK) Limited                                                                                      [ http://NigelLee.info ]
'
' Licensed for use by Experts Exchange members under Experts Exchange Terms-of-Use, provided the copyright statement is retained.
' -------------------------------------------------------------------------------------------------------------------------------------------------------------------------

  Dim blnErr_Ignore                                     As Boolean
  Dim lngApplication_Calculation                        As Long
  Dim lngCell                                           As Long
  Dim lngTotal_Cells                                    As Long
  Dim objCell                                           As Range
  Dim objRange                                          As Range
  Dim objWorksheet                                      As Worksheet

  On Error GoTo Err_Q_28715859

' Const blnCONVERT_TO_TEXT                              As Boolean = True               ' See notes in column [D] of [Sheet1] worksheet
  Const blnCONVERT_TO_TEXT                              As Boolean = False
  
  blnErr_Ignore = False
  
  lngApplication_Calculation = Application.Calculation
  
  Application.ScreenUpdating = False
  Application.EnableEvents = False
  Application.Calculation = xlCalculationManual
  
  For Each objWorksheet In ThisWorkbook.Worksheets
   
      DoEvents
      
      Err.Clear
      lngErr_Number = 0&
      
      blnErr_Ignore = True
      Set objRange = objWorksheet.UsedRange.SpecialCells(Type:=xlCellTypeConstants, Value:=xlNumbers)
      blnErr_Ignore = False
      
      If lngErr_Number = 0& Then
         If Not (objRange Is Nothing) Then
            lngCell = 0&
            lngTotal_Cells = objRange.Cells.Count
            
            For Each objCell In objRange
            
                lngCell = lngCell + 1&
                
                If lngCell Mod 50& = 0& Then
                   Application.StatusBar = "[" & objWorksheet.Name & "].[" & objCell.Address(RowAbsolute:=False, ColumnAbsolute:=False) & _
                                           "] Cell #" & CStr(lngCell) & "/" & CStr(lngTotal_Cells) & " (" & _
                                           Format$(lngCell / lngTotal_Cells, "0%") & ") - Please wait..."
                   DoEvents
                End If ' If lngCell Mod 50& = 0& Then
                
                Select Case (True)
                
                    Case (Not (IsDate(objCell.Value)))                          ' Checks for Date
                    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
                    
                    Case Else
                        If (blnCONVERT_TO_TEXT) Then
                           objCell.NumberFormat = "General"
                           objCell.Formula = "=""" & Format$(objCell.Value, "YYYYMMDD") & """"
                           objCell.Copy
                           objCell.PasteSpecial xlPasteValuesAndNumberFormats
                        Else
                           objCell.NumberFormat = "0"
                           objCell.Value = Format(objCell.Value, "YYYYMMDD")
                        End If ' If (blnCONVERT_TO_TEXT) Then
                        
                End Select ' Select Case (False)
                
            Next objCell
         End If ' If Not (objRange Is Nothing) Then
      End If ' If lngErr_Number = 0& Then
      
  Next objWorksheet
  
Exit_Q_28715859:

  On Error Resume Next

  Set objCell = Nothing
  Set objRange = Nothing
  Set objWorksheet = Nothing
  
  Application.StatusBar = False
  Application.CutCopyMode = False
  
  Application.Calculation = lngApplication_Calculation

  Application.EnableEvents = True
  Application.ScreenUpdating = True
  
  DoEvents
  
  MsgBox "Finished!", _
         vbInformation Or vbOKOnly, _
         ThisWorkbook.Name

  Exit Sub
  
Err_Q_28715859:

' lngErr_Line = Erl
  lngErr_Number = Err.Number
  strErr_Description = Err.Description

  On Error Resume Next

  If (blnErr_Ignore) Then
     On Error GoTo Err_Q_28715859
     Resume Next
  End If ' If (blnErr_Ignore) Then

  Application.ScreenUpdating = True
  
  Beep
  MsgBox "Error #" & CStr(lngErr_Number) & _
         vbCrLf & vbLf & _
         strErr_Description, _
         vbExclamation Or vbOKOnly, _
         ThisWorkbook.Name

  Resume Exit_Q_28715859
         
End Sub

Open in new window



That said, after reading the notes, please let me know if anything is not clear.

You will also see that I have included some code to show (upon the MS-Excel Application Statusbar) the progression of the changing of cells throughout the cells of the worksheets across the workbook, as the process may take some time to complete if you have many worksheets/cells to interrogate.
Q_28715859.xlsm

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Try it like this....
Sub ChangeDateFormat()
Dim ws As Worksheet
Dim cell As Range
DoEvents
For Each ws In Worksheets
    For Each cell In ws.UsedRange.SpecialCells(xlCellTypeConstants, 23)
        If IsDate(cell) Then
            cell.NumberFormat = "@"
            cell = WorksheetFunction.Text(cell, "yyyymmdd")
        End If
    Next cell
Next ws
MsgBox "Done!", vbInformation
End Sub

Open in new window

[ fanpages ]IT Services ConsultantCommented:
^ 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(...)?
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You can use any of them, both will produce the same output.
[ fanpages ]IT Services ConsultantCommented:
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).
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
It's matter of debate.
ProfessorJimJamMicrosoft Excel ExpertCommented:
good score guys. i cannot earn points when you guys are online ;-)  questions are answred on the fly :-)

 just kidding
[ fanpages ]IT Services ConsultantCommented:
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.
FloraAuthor Commented:
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
FloraAuthor Commented:
sktneer
i also have seen your code, i will test and will revert back.
[ fanpages ]IT Services ConsultantCommented:
When I open the workbook, I see potential changes in a few cells in column [A] (see below)...

Sheet1 - Potential changes
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.
[ fanpages ]IT Services ConsultantCommented:
Interesting!

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

Sheet1 - Potential changes - Second Execution
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...
FloraAuthor Commented:
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
[ fanpages ]IT Services ConsultantCommented:
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.

Sheet1 - Potential changes - Rows 5151 to 5176
FloraAuthor Commented:
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
[ fanpages ]IT Services ConsultantCommented:
^ 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)
[ fanpages ]IT Services ConsultantCommented:
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

Open in new window


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

Thanks.
FloraAuthor Commented:
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.
FloraAuthor Commented:
i tried with modification of the 3 lines as mentioned by you, still the result does not work with US date format.
[ fanpages ]IT Services ConsultantCommented:
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.
[ fanpages ]IT Services ConsultantCommented:
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?
FloraAuthor Commented:
yes fanpages.

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

thank you very much to you fanpages and sktneer
FloraAuthor Commented:
thanks fanpages for being patient with my ignorance. your professionalism is very much appreciated.
[ fanpages ]IT Services ConsultantCommented:
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 :)
FloraAuthor Commented:
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.
ProfessorJimJamMicrosoft Excel ExpertCommented:
interesting thread and comments.

i also learned something.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.