Formula to Convert mm/dd/yyyy String to Date

Some data I receive has the date in a string in mm/dd/yyyy format, which is easier to sort or filter in Excel when converted to a date number. If the data is in cell A2 then the formulas for splitting the date string into substrings and creating a date value are:

String	Year	Month	Day	Date
5/31/2016	2016	5	31	31/05/2016

• Year: =RIGHT(A2,4). The year is always the last four digits.
• Month: =LEFT(A2,FIND("/",A2)-1). The month is the one or two digits before a forward slash.
• Day: =MID(A2,FIND("/",A2)+1,FIND("/",A2,4)-FIND("/",A2)-1). The day is the digits between the first and second forward slash.
• Date: =DATE(B2,C2,D2).

Probably the non-obvious bit is finding the second forward slash. The FIND() formula takes three arguments: find_text, within_text, start_num, where start_num is the position to start the search for find_text. Since the month and day numbers are always one or two digits, the second forward slash must be in position 4 ("m/d/yyyy"), 5 ("mm/d/yyyy", "m/dd/yyyy") or 6 ("mm/dd/yyyy"), so starting the search from position 4 will always find the position of the second forward slash.

Later ... I could just use Data, Text To Columns and split the date string into three columns using the forward slash as the delimiter.

Excel VBA "Code execution has been interrupted"

Very strange bug: After starting an Excel VBA macro, the macro stops and Excel displays a "Code execution has been interrupted" dialog. The only way to get the macro to finish is to keep pressing the "Continue" button. The problem only occurs on my development account not in other accounts. The solution is to wait for the dialog, press the "Debug" button to use VBE then type Ctrl+Break. Apparently, the issue is caused by Excel reactivating breakpoints in the macro from earlier debugging sessions. I guess that is why the macro runs to completion on other accounts. Hat tip to The two WORST Excel Errors EVER.

Google Play cannot download error 495

When downloading an application from Google Play while using mobile / cellular network, the download may fail and Google Play reports "error 495". In my case, the fix was to enable background data for the "Media" application (I restrict almost all applications from downloading background data on my phone).

1. Navigate to Settings, Data Usage.
2. Select the mobile operator tab.
3. Scroll down until you find "Media" application. If has "restricted" next to it, it is prevented from downloading background data, so enable it and try to install your application again.

Recursive preorder traversal of a folder tree in VBA

To process files in a folder tree using Excel, I implemented a recursive preorder traversal of a tree in VBA below. I haven't had to do this earlier because I usually use Gnu "find . -exec" to process files in folders.

Option Explicit

'References
'1. Microsoft Scripting Runtime

Global gFso As Scripting.FileSystemObject

Sub Main()
  Set gFso = New Scripting.FileSystemObject
  TraversePreorder "C:\KS Work\Temp"
End Sub

Sub TraversePreorder(ByVal sPath As String)
  Dim oFolder As Scripting.Folder, vMember As Variant
  
  If gFso.FolderExists(sPath) Then
    Debug.Print sPath 'Process folder
    Set oFolder = gFso.GetFolder(sPath)
    For Each vMember In oFolder.Files
      TraversePreorder vMember
    Next vMember
    For Each vMember In oFolder.SubFolders
      TraversePreorder vMember
    Next vMember
  Else
    Debug.Print sPath 'Process file
  End If

End Sub