Macro to Change all Date formats in workbook to 01/2017 to 01.2017 (and vice versa)

[RogChele]

Greetings,

I am currently working in Excel 2016, Office 10, with the BOA Analysis activated.

Based on who refreshes the queries and their user-data setup, the default dates comes back as either 01/2017 or 01.2017 format (in SAP, you can choose how you want the queries in excel to default the date; European or US)

There are 4 sheets with queries with dates that each show either 01/2017 or 01.2017 and then there are 10 other sheets that have lookup functions based on 01.2017 formats.

Since the queries based on who refreshes them provides a different dating format, I would like to create a macro that searches through the workbook for the format of the query date and then changes all dates to that format within the workbook.

The result will ensure that all lookups continue to work regardless of who refreshes the queries.

With this macro, it will be activated by pushing a button.

What I would like:

1) Refresh the Queries
2) Hit the button that will activate the macro to check query date format.
- The button will say "My Date Format is "."" and another button that says "My Date Format is "/""

[Paul_Hossler]

Dates can be a little tricky

Q1 - Can you post 2 small WBs with just a small number 'date' cells?

For example, 01.2017 might be set via a Style or Custom Number Format or as 'regular' text


Q2 - Do you want the cell value to be an true Excel date, or a string that looks like a date?

In the meantime, you can try something like this which make the 'dates' into strings

Option Explicit

Sub DateFormats()
    Dim ws As Worksheet
    Dim c As Range, r As Range
    Application.ScreenUpdating = True
    If MsgBox("Do you want to change 'dot' dates to 'slash' dates?", vbQuestion + vbYesNo, "Change Date Formats") = vbYes Then
        For Each ws In ThisWorkbook.Worksheets
            Set r = Nothing
            On Error Resume Next
            Set r = ws.UsedRange.SpecialCells(xlCellTypeConstants)
            On Error GoTo 0
            
            If Not r Is Nothing Then
                For Each c In r.Cells
                    If c.Text Like "##.####" Then
                        c.Value = "'" & Left(c.Text, 2) & "/" & Right(c.Text, 4)
                    End If
                Next
            End If
        Next
    
    
    ElseIf MsgBox("Do you want to change 'slash' dates to 'dot' dates?", vbQuestion + vbYesNo, "Change Date Formats") = vbYes Then
        For Each ws In ThisWorkbook.Worksheets
            Set r = Nothing
            On Error Resume Next
            Set r = ws.UsedRange.SpecialCells(xlCellTypeConstants)
            On Error GoTo 0
            
            If Not r Is Nothing Then
                For Each c In r.Cells
                    If c.Text Like "##/####" Then
                        c.Value = "'" & Left(c.Text, 2) & "." & Right(c.Text, 4)
                    End If
                Next
            End If
        Next
    End If
    Application.ScreenUpdating = True

End Sub

[RogChele]

Sample_Data_Date_Format.xlsx

I created a very shortened dummy spreadsheet similar to what I am working on.

Tab "Lookup_One" is where my formulas are located referencing the dates in the next two queried pages.

Tab 2- "US" is a copy of a query (they are only values) with date formatted "01/2017"

Tab 3 - "CA" is a copy of a query (they are only values) with date formatted "01.2017"

I just noticed your response so I will try it now but I wanted to at least provide some information to you.

[p45cal]

How about changing the formula in cell D12 of the Lookup_One sheet from:
=IFERROR(INDEX(US, MATCH(B12, US!$A:$A, 0), MATCH(Lookup_One!D$6, US!$A$3:$R$3, 0)), 0)
[which btw I think should really be:
=IFERROR(INDEX(US, MATCH(B12, US!$A$3:$A$11, 0), MATCH(D$6, US!$A$3:$R$3, 0)), 0)]
to:
=IFERROR(INDEX(US, MATCH($B12, US!$A$3:$A$11, 0), IFERROR(MATCH(D$6, US!$A$3:$R$3, 0),MATCH(SUBSTITUTE(D$6,".","/"), US!$A$3:$R$3, 0))), 0)



and cell D13 from:
=IFERROR(INDEX(CAD, MATCH($B$13,CA!$A$3:$A$11,0), MATCH(D$6, CA!$A$3:$R$3, 0)), 0)
to:
=IFERROR(INDEX(CAD, MATCH($B13, CA!$A$3:$A$11, 0), IFERROR(MATCH(D$6, CA!$A$3:$R$3, 0),MATCH(SUBSTITUTE(D$6,".","/"), CA!$A$3:$R$3, 0))), 0)



then copy across?
Then it won't matter which format is used on either of the US and CA sheets so no need for a macro.

[RogChele]

@ Paul,

This worked perfectly.

I altered it slightly to fit the button form!

Thank you SO much.


@p45Cal,

Thank you as well. I will see how that formula works step by step. I attempted to use a substitute formula but wasnt getting the right information. This helps.

Thank you both again.

[Paul_Hossler]

Good

1. The red cells have almost the same pattern (001.2017) like the green cells (01.2017) but they're not changed

2. The macro can be made much more efficient IF the data sheets (US, CAD, etc.) have a 'signature' in a cell, or the dates are always only ever in the first X rows

Capture.JPG

[p45cal]

2. The macro can be made much more efficient IF the data sheets (US, CAD, etc.) have a 'signature' in a cell, or the dates are always only ever in the first X rows

I suspect also if the first instance of Application.ScreenUpdating = True were changed to Application.ScreenUpdating = False!

[Paul_Hossler]

I suspect also if the first instance of Application.ScreenUpdating = True were changed to Application.ScreenUpdating = False!

I can't believe that my keyboard made a silly mistake like that

[RogChele]

I see your point and I am editing my actual file to reflect the same placement of my dates.