Convert 1-1 back to 1-1 from being converted to a date

[ChrisMcIntyre]

Morning all, Happy Friday!!

A system I use spits out a document directly into Excel, but frustratingly some of the columns have numbers like 1-1 (one of one) or 3-9 (three of nine), and Excel is converting these numbers to dates.

Is there an easy way to convert these back to the original system numbers?

If there's no simple method of conversion that doesn't involve lots of retyping then, I would love to build something in VBA whereby the user clicks a button on a sheet, it prompts the user to choose the file to format and then it does all the formatting and then prompts to save.

Bottom line, I want the quickest solution possible please... And I know someone out there knows the pesky solution

 

[njimack]

If you're looking for a simple formula approach, something like this:

=DAY(A2)&" of "&MONTH(A2)

 

[ChrisMcIntyre]

Hi njimack, thanks so much for this, I made some tweaks as my machine is US date formatted (don't ask why ) and I changed OF to - so the info looks the same in the data column and the new column, it works great!

=MONTH(A1)&"-"&DAY(A1)

 

[Peter_SSs]

Another option ..

22 02 11.xlsm
	A	B
1	1-Jan	1-1
2	3-Jan	1-3
3	9-Mar	3-9
4	8-May	5-8
Change from date

Cell Formulas
Range	Formula
B1:B4	B1	=TEXT(A1,"m-d")

 

[ChrisMcIntyre]

Thanks Peter!

Both work great, I just discovered that the final report can only show the result, so no extra columns and the result as text and not a formula, so I think I will need to create something in VBA that will create the extra columns get the results and paste them back over as values somehow, then remove the extra columns created by the VBA... Boy, what a pain.

Thanks both for the solutions!!

 

[Peter_SSs]

Try something like this - no extra columns required. Test with a copy as it directly over-writes the original data.
May need to adjust the range.

Sub ConvertBack()
  With Range("A1", Range("A" & Rows.Count).End(xlUp))
    .NumberFormat = "@"
    .Value = Evaluate("text(" & .Address & ",""m-d"")")
  End With
End Sub

 

[ChrisMcIntyre]

Oooo lovely, that worked just nicely, thanks so much Peter!!

 

[ChrisMcIntyre]

Bit cheeky, is it possible for you to help me with getting this macro into a standalone doc?

Ideally the user opens this new Excel doc, clicks the macro button, the macro pops up the file explorer search, the user chooses the DATA file to make the changes to, and the macro runs and updates all the numbers on the DATA sheet, the user then just then needs to save it.

 

[ChrisMcIntyre]

Also, sorry, the macro needs to ignore blank cells. Currently, when it comes across a blank cell in between the first and last entry, it's adding 1-0

 

[Peter_SSs]

Try this. Code also saves the data workbook after making the changes.

Sub ConvertBack_v2()
  Dim sFile As String
  Dim wbData As Workbook
  
  sFile = Application.GetOpenFilename()
  If sFile <> "False" Then
    Set wbData = Workbooks.Open(sFile)
    With wbData.Sheets("DATA")
      With .Range("A1", .Range("A" & Rows.Count).End(xlUp))
        .NumberFormat = "@"
        .Value = Evaluate(Replace("if(#="""","""",text(#,""m-d""))", "#", .Address))
      End With
    End With
    wbData.Save
  End If
End Sub