G Challenge I

Sub G_Challenge_1()

Range("B2").Select
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"

Range("C2").Select
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)