G Challenge I

Ok, my friend is trying to learn Excel VBA and get into RAD development.   I am going to set a number of challenges which I am going to call a ‘G Challenge’  😛

So the first.  Take a string in the format :


where the format is :


In VBA I would like 2 things. 

  1. The string back in the format dd-mmm-yy hh:mm
  2. An actual date serial

Example above would go to 

  1. 24-Nov-2010 17:06:15
  2. 40506.7126736111

Go!   I would use 2 seperate functions to start off with.

Answers will be forthcoming in a post G Answer I

  1. Mr G.
    January 24, 2011 at 3:06 pm

    How about this (assuming that the contents of A2 is “20101124_170615_dd20101124_EOD.g”)

    Sub G_Challenge_1()

    ActiveCell.FormulaR1C1 = _
    “=RIGHT(LEFT(LEFT(R2C1,FIND(“”_dd””,R2C1,1)-1),FIND(“”_””,LEFT(R2C1,FIND(“”_dd””,R2C1,1)-1),1)-1),2)&””/””&MID(LEFT(LEFT(R2C1,FIND(“”_dd””,R2C1,1)-1),FIND(“”_””,LEFT(R2C1,FIND(“”_dd””,R2C1,1)-1),1)-1),5,2)&””/””&LEFT(LEFT(LEFT(R2C1,FIND(“”_dd””,R2C1,1)-1),FIND(“”_””,LEFT(R2C1,FIND(“”_dd””,R2C1,1)-1),1)-1),4)&”” “”&LEFT(RIGHT(LEFT(R2C1,FIND(“”_dd””,R2C1,1)-1),6),2)&””:””&MID(RIGHT(LEFT(R2C1,FIND(“”_dd””,R2C1,1)-1),6),3,2)&””:””&RIGHT(RIGHT(LEFT(R2C1,FIND(“”_dd””,R2C1,1)-1),6),2)”
    Selection.NumberFormat = “dd/mm/yyyy hh:mm:ss”

    ActiveCell.FormulaR1C1 = _
    “=RIGHT(LEFT(LEFT(R2C1,FIND(“”_dd””,R2C1,1)-1),FIND(“”_””,LEFT(R2C1,FIND(“”_dd””,R2C1,1)-1),1)-1),2)&””/””&MID(LEFT(LEFT(R2C1,FIND(“”_dd””,R2C1,1)-1),FIND(“”_””,LEFT(R2C1,FIND(“”_dd””,R2C1,1)-1),1)-1),5,2)&””/””&LEFT(LEFT(LEFT(R2C1,FIND(“”_dd””,R2C1,1)-1),FIND(“”_””,LEFT(R2C1,FIND(“”_dd””,R2C1,1)-1),1)-1),4)&”” “”&LEFT(RIGHT(LEFT(R2C1,FIND(“”_dd””,R2C1,1)-1),6),2)&””:””&MID(RIGHT(LEFT(R2C1,FIND(“”_dd””,R2C1,1)-1),6),3,2)&””:””&RIGHT(RIGHT(LEFT(R2C1,FIND(“”_dd””,R2C1,1)-1),6),2)”
    Selection.NumberFormat = “0.0000000000”

    End Sub

    • FinancialRadDeveloper
      January 24, 2011 at 5:11 pm

      Sub G_Challenge_1()

      ActiveCell.FormulaR1C1 = "=RIGHT(LEFT(LEFT(R2C1,FIND(""_dd"",R2C1,1)-1), FIND(""_"",LEFT(R2C1,FIND(""_dd"",R2C1,1)-1),1)-1),2)&""/"" & MID(LEFT(LEFT(R2C1,FIND(""_dd"",R2C1,1)-1), FIND(""_"",LEFT(R2C1,FIND(""_dd"",R2C1,1)-1),1)-1),5,2) & ""/"" & LEFT(LEFT(LEFT(R2C1,FIND(""_dd"",R2C1,1)-1),FIND(""_"",LEFT(R2C1,FIND(""_dd"",R2C1,1)-1),1)-1),4)&"" ""&LEFT(RIGHT(LEFT(R2C1,FIND(""_dd"",R2C1,1)-1),6),2)&"":""&MID(RIGHT(LEFT(R2C1,FIND(""_dd"",R2C1,1)-1),6),3,2)&"":""&RIGHT(RIGHT(LEFT(R2C1,FIND(""_dd"",R2C1,1)-1),6),2)"
      Selection.NumberFormat = "“dd/mm/yyyy hh:mm:ss"

      ActiveCell.FormulaR1C1 = _
      "=RIGHT(LEFT(LEFT(R2C1,FIND(""_dd"",R2C1,1)-1),FIND(""_"",LEFT(R2C1,FIND(""_dd"",R2C1,1)-1),1)-1),2)&""/""&MID(LEFT(LEFT(R2C1,FIND(""_dd"",R2C1,1)-1),FIND(""_"",LEFT(R2C1,FIND(""_dd"",R2C1,1)-1),1)-1),5,2)&""/""&LEFT(LEFT(LEFT(R2C1,FIND(""_dd"",R2C1,1)-1),FIND(""_"",LEFT(R2C1,FIND(""_dd"",R2C1,1)-1),1)-1),4)&"" ""&LEFT(RIGHT(LEFT(R2C1,FIND(""_dd"",R2C1,1)-1),6),2)&"":""&MID(RIGHT(LEFT(R2C1,FIND(""_dd"",R2C1,1)-1),6),3,2)&"":""&RIGHT(RIGHT(LEFT(R2C1,FIND(""_dd"",R2C1,1)-1),6),2)"
      Selection.NumberFormat = "0.0000000000"

      End Sub

      'Input is a reserved word
      'This function will be called like : =G_Challenge_1_FRD_string(A2)
      Function G_Challenge_1_FRD_string(FuncInput As Variant) As String

      'Standard format is : yyyymmdd_hhmmss_ddyyyymmdd_EOD.g
      'Example for testing : 20101124_170615_dd20101124_EOD.g

      'Lets take several bites at this for illustrating what we are doing.

      Dim Year As String
      Dim Month As String
      Dim Day As String

      Dim Hours As String
      Dim Minutes As String

      'In excel we have the worksheet function FIND.
      'This is a massive pain to use in VBA, as FIND only works for a Range, not on a String.
      'which is what we will be working from, so we want to get the input from the range, turn it into a string
      'and then operate on that with the VBA functions we have for operating on strings.

      'create a string variable
      Dim InputAsString As String
      'Type conversion function to string.
      'for more info on conversion try
      'here : http://msdn.microsoft.com/en-us/library/0zk841e9(v=vs.80).aspx
      'and here : http://www.ozgrid.com/VBA/conversion-functions.htm
      InputAsString = CStr(FuncInput)

      'we now have a string lets operate on it. We don't have to be too clever as this is
      'only challenge one and we do have a fixed format.

      Year = Mid(InputAsString, 3, 2)
      Month = Mid(InputAsString, 5, 2)
      Day = Mid(InputAsString, 7, 2)

      'ok we have a problem with month... we need to get the mmm format which means 02 -> Feb
      'we can use a big Switch statement if we wanted something custom.

      Dim tmpMonth As String

      Select Case Month

      Case "01"
      tmpMonth = "Jan"
      Case "02"
      tmpMonth = "Feb"
      Case "03"
      tmpMonth = "Mar"
      Case "04"
      tmpMonth = "Apr"
      Case "05"
      tmpMonth = "May"
      Case "06"
      tmpMonth = "Jun"
      Case "07"
      tmpMonth = "Jul"
      Case "08"
      tmpMonth = "Aug"
      Case "09"
      tmpMonth = "Sep"
      Case "10"
      tmpMonth = "Oct"
      Case "11"
      tmpMonth = "Nov"
      Case "12"
      tmpMonth = "Dec"
      Case Else
      tmpMonth = "Err"
      End Select

      Month = tmpMonth

      'luckily this is handled in VBA for us already.
      'Month = Format(DateSerial(1, CInt(Mid(InputAsString, 5, 2)), 1), "mmm")

      Hours = Mid(InputAsString, 10, 2)
      Minutes = Mid(InputAsString, 12, 2)

      'now lets build up the output to the function.

      'A function returns a value by assigning that value to the name of the function.

      'reminder of desired output : dd-mmm-yy hh:mm
      G_Challenge_1_FRD_string = Day & "-" & Month & "-" & Year & " " & Hours & ":" & Minutes

      End Function

      Function GetDateShortVersion(MyInput) As String

      Dim InputAsString
      InputAsString = CStr(MyInput)
      'To make things fit on the screen the underscore '_' maybe used to split things up a bit
      GetDateShortVersion = Mid(InputAsString, 7, 2) & "-" & _
      Format(DateSerial(1, CInt(Mid(InputAsString, 5, 2)), 1), "mmm") & "-" & _
      Mid(InputAsString, 3, 2) & " " & Mid(InputAsString, 10, 2) & ":" & Mid(InputAsString, 12, 2)

      End Function

  2. FinancialRadDeveloper
    January 24, 2011 at 5:26 pm

    Oh, and the date as a serial is here :

    Function GetDateAsDate(MyInput) As Date

    ‘Variants hold pretty much anything. The downside of this is that they are expensive
    ‘due to their flexibility, but workig with dates etc it is ok.
    Dim DatePart As Variant
    Dim TimePart As Variant

    ‘Standard format is : yyyymmdd_hhmmss_ddyyyymmdd_EOD.g
    DatePart = DateSerial(Mid(MyInput, 3, 2), Mid(MyInput, 5, 2), Mid(MyInput, 7, 2))
    TimePart = Mid(MyInput, 10, 2) & “:” & Mid(MyInput, 12, 2)

    GetDateAsDate = DateValue(DatePart) + TimeValue(TimePart)

    End Function

    I will put more detail in a post G Worked Answer I when I get a chance. The last part is quite tricky and not much help available on the internet for it.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: