New To VBA

TPRSCHM

New Member
Joined
Nov 16, 2017
Messages
10
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
 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,464
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Nov 16, 2017
Messages
10
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_datenotice_topcd_datework_began_dateopen_to_trfc_datework_complt_datesubstan_complt_dtorig_complt_date
3212017612201771320170005182018


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

pbcnick

New Member
Joined
Nov 15, 2012
Messages
42
based on your criteria, here is a simple function which is a bit rudimentary but it will work. see comments for exceptions.
Code:
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).
 
Last edited:

TPRSCHM

New Member
Joined
Nov 16, 2017
Messages
10

ADVERTISEMENT

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

New Member
Joined
Nov 15, 2012
Messages
42
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

New Member
Joined
Nov 16, 2017
Messages
10

ADVERTISEMENT

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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,675
Office Version
  1. 2010
Platform
  1. Windows
based on your criteria, here is a simple function which is a bit rudimentary but it will work.
Code:
[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...
Code:
[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.
Code:
[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

New Member
Joined
Nov 15, 2012
Messages
42
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.

Code:
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

New Member
Joined
Nov 15, 2012
Messages
42
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. :cool:
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,224
Messages
5,594,915
Members
413,952
Latest member
JGer

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top