PDA

View Full Version : [SOLVED] Macro to Change all Date formats in workbook to 01/2017 to 01.2017 (and vice versa)



RogChele
05-10-2017, 02:08 PM
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
05-10-2017, 02:17 PM
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
05-10-2017, 03:06 PM
19133

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
05-10-2017, 04:33 PM
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
05-10-2017, 05:39 PM
@ 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
05-10-2017, 06:02 PM
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

19134

p45cal
05-10-2017, 06:12 PM
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
05-10-2017, 07:24 PM
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
05-12-2017, 11:24 AM
I see your point and I am editing my actual file to reflect the same placement of my dates.