New To VBA

[TPRSCHM]

Good Morning,

I'm still new to VBA and have learned a lot over the past month but I seam to have hit a brick wall with trying to figure out how to create a VBA to convert a text in both MDDYYYYY or MMDDYYYY formats to a standard M/DD/YYYY or MM/DD/YYYY format

Thanks
Jim

 

[FormR]

Hi, where are these text values that need converting, on the worksheet somewhere? In a variable? And where should the converted dates be stored?

 

[TPRSCHM]

FormR,

There are 7 columns on an excel spreadsheet that get populated when the information is exported. Please see below for the examples

Thanks Jim

let_date	notice_topcd_date	work_began_date	open_to_trfc_date	work_complt_date	substan_complt_dt	orig_complt_date
3212017	6122017	7132017	0	0	0	5182018

<tbody>
</tbody><colgroup><col><col><col><col><col><col><col></colgroup>

 

[pbcnick]

based on your criteria, here is a simple function which is a bit rudimentary but it will work. see comments for exceptions.

Sub test_convert_date()
' this is a simple way to peform what you need. there are 1,000 ways to skin a cat
' see last lines in function to comment or uncomment depending if you need the data type as a string or date type.
' this assumes that Day will always be DD and year will always be YYYY: eg 3012017
' this will not work for 112017 (1/1/2017). it will work for 1012017 (1/01/2017)
strDate = Range("A1").Value
sDate = convertDate(strDate) ' create a loop for your requirements and call this function when needed
MsgBox (sDate)
Stop  ' stop here to look at Locals to take note of type: String or Date.
End Sub
Function convertDate(ByVal strDate As String)
    sYYYY = Right(strDate, 4)
    Select Case Len(strDate)
        Case 7
            sM = "0" & Left(strDate, 1)
            sD = Mid(strDate, 2, 2)
        Case 8
            sM = Left(strDate, 2)
            sD = Mid(strDate, 3, 2)
    End Select
    convertDate = sM & "/" & sD & "/" & sYYYY ' (use this to have MM/DD/YYYY as a String type.
'    convertDate = CDate(sM & "/" & sD & "/" & sYYYY) ' use this to convert to Date type (used for comparison)
End Function

hope it helps

EDIT: The sample above just uses the value of cell A1. You will need to create a loop to pass values to the function and replace your values (or insert them elsewhere).

 

[TPRSCHM]

pbcnick

A little confused what I should do with the info from the locals

: strDate : 3212017 : Variant/Double
: sDate : "03/21/2017" : Variant/String

The information in the MsgBox is correct but that's where it stops. How do I have the script complete and change the actual value.

Thanks
Jim

 

[pbcnick]

Hi, sorry,
I made a bit of an assumption that you might be familiar with loops. Are you familiar with for/next loops?
Also, will the first 7 columns be dates and sometimes 0? (it looks like they will)
Also, will you be running the macro against a sheet and then later adding more rows with dates that need formatting?
let me know and I'll make a simple loop for you if needed.

 

[TPRSCHM]

pbcnick

I'm not real familiar with loops that one of the next chapters in the VBA for dummies book that I will be getting to. The range of the cells will be from N2 to U50 it is my intent to drop the data on the sheet then run a macro to convert the dates. After this is done than I will use it as a master sheet to update other reports. The columns will have the dates or 0 listed in them. I'm ok with ignoring the cells that only have a 0

Thanks
Jim

 

[Rick Rothstein]

based on your criteria, here is a simple function which is a bit rudimentary but it will work.

[table="width: 500"]
[tr]
	[td]Function convertDate(ByVal strDate As String)
    sYYYY = Right(strDate, 4)
    Select Case Len(strDate)
        Case 7
            sM = "0" & Left(strDate, 1)
            sD = Mid(strDate, 2, 2)
        Case 8
            sM = Left(strDate, 2)
            sD = Mid(strDate, 3, 2)
    End Select
    convertDate = sM & "/" & sD & "/" & sYYYY ' (use this to have MM/DD/YYYY as a String type.
'    convertDate = CDate(sM & "/" & sD & "/" & sYYYY) ' use this to convert to Date type (used for comparison)
End Function[/td]
[/tr]
[/table]

Just pointing out that your formula returns the converted date as a text string, not a real Excel date. If that is okay with the OP, then there is a one-liner equivalent for your function...

[table="width: 500"]
[tr]
	[td]Function ConvertDate(ByVal strDate As String) As Variant
  ConvertDate = Evaluate("IF(" & strDate & "=0,"""",TEXT(" & strDate & ",""0\/00\/0000""))")
End Function[/td]
[/tr]
[/table]

Note that I chose to return the empty text string ("") if the cell contained a 0 rather than the OP's date string. If the OP wants real dates, the same one-liner can be used by adding the 0+ that I show in red.

[table="width: 500"]
[tr]
	[td]Function ConvertDate(ByVal strDate As String) As Variant
  ConvertDate = Evaluate("IF(" & strDate & "=0,"""",[B][COLOR="#FF0000"]0+[/COLOR][/B]TEXT(" & strDate & ",""0\/00\/0000""))")
End Function[/td]
[/tr]
[/table]

Note that this version returns the real date as a date serial number meaning the OP will have to format the cells the UDF formula is placed in with the date format of his choosing.


Note to the OP: HOW TO INSTALL UDFs
---------------------------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use ConvertDate just like it was a built-in Excel function. For example,

=ConvertDate (A2)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[pbcnick]

Here is an update code that should help. I didn't use a for/next to make it shorter and I added some comments. I see a new post with some more info and I'll review later, but as he points out, the date is returned as a string. if you uncomment the line that converts it to Date then you will have a Date format.

Sub process_dates()
Dim myCell As Range
Dim myRng As Range
Set myRng = Range("n2:u50")  ' change your range here to accomodate your needs. this can be easily made dynamic to detect cells with data
For Each myCell In myRng.Cells
    strDate = myCell.Value ' adding this so you can see how it gets the value for each cell and follow the pattern
    If InStr(strDate, "/") = 0 And strDate <> 0 Then ' skip if it's been run before (presense of a "/") or skip if 0
        sDate = convertDate(strDate) ' pass the data to the function
        myCell.Formula = sDate  ' update the cell
    End If
Next
End Sub
Function convertDate(ByVal strDate As String)
    sYYYY = Right(strDate, 4)
    Select Case Len(strDate)
        Case Is < 7  ' I added this before adding IF statement in Sub, but left it here so you can see some syntax for a Select Case statement
            convertDate = 0
            Exit Function
        Case 7
            sM = "0" & Left(strDate, 1)
            sD = Mid(strDate, 2, 2)
        Case 8
            sM = Left(strDate, 2)
            sD = Mid(strDate, 3, 2)
    End Select
    convertDate = sM & "/" & sD & "/" & sYYYY ' (use this to have MM/DD/YYYY as a String type.
'    convertDate = CDate(sM & "/" & sD & "/" & sYYYY) ' use this to convert to Date type (used for comparison)
End Function

 

[pbcnick]

Hi, yes I believe what you are saying by returning a string format is covered by uncommenting the line in the function with CDATE.
You are more advanced than I, so I will look at your comment to see if I can even comprehend it
I supplied the easiest code and result based on his requirements, but of course us techies like to show others how to do it better. thank you for your contribution. I'll review it a bit. time for lunch!


EDIT: sorry, I didn't quote the long reply. This was meant for Mr. Rothstein.