Geek Stuff: Microsoft Excel VBA to convert text field

Fields in 2 columns on spreadsheet contains text 2011/03/18 (imported from external source from a date range). I want to convert this into a proper date value. Instead of using the function =DATEVALUE I can build this in via VBA and create a button to map to my macro. First step is to convert the text fields to be in number format of "General". The code below will achieve this for 2 field ranges on 1 sheet.

Sub convertdate()
'
' Convert date format Macro
'
Sheets("NEW Report").Select
Range("T3:T5000").Select
Dim Rng As Range

For Each Rng In Selection

If IsNumeric(Rng) Then
Rng.NumberFormat = "General"
Else
On Error Resume Next
Rng = DateValue(Rng)
On Error GoTo 0
Rng.NumberFormat = "General"
End If
Next Rng

Range("P3:P5000").Select

For Each Rng In Selection

If IsNumeric(Rng) Then
Rng.NumberFormat = "General"
Else
On Error Resume Next
Rng = DateValue(Rng)
On Error GoTo 0
Rng.NumberFormat = "General"
End If
Next Rng

End Sub

Then from Excel menu go to Format, Cells (Ctrl+1) and choose "Date" as the format.

Useful link to show you how to do date arithmetic: http://www.cpearson.com/excel/datearith.htm

Geek Stuff: Microsoft Excel array formula for multi column query

Where:

C2:C2000 is the status column
D2:D2000 is the priority column

B$2 is the status qualifying criteria (x)
$A3 is the priority qualifying criteria (y)

This gives a count of all entries where the 'status' = x and 'priority' = y

{=SUM(('Extract'!$C$2:'Extract'!$C$2000=B$2)*('Extract'!$D$2:'Extract'!$D$2000=$A3))}

Use ctrl-shift-enter to add the curly brackets or else the array formula won't work properly.

*The above example looks for the 'Extract' worksheet outside of [this] spreadsheet.

Example of internal worksheet:

{=SUM(($B$2:$B$155="High")*($D$2:$D$155="Quality Acceptance Phase"))}

Geek Stuff: Microsoft Excel IF function to query text and populate to another cell

=IF(LEFT(A492,2)="SU","SUGGESTION",IF(LEFT(A492,2)="BU","BUG","?"))

This query means: look at left 2 char in cell A492, if it says SU then populate [this] field with the word SUGGESTION. If cell says BU then populate [this] field with the word BUG. If it is neither SU or BU then populate field with the character ?

=IF(B491="Critical","BUG",IF(B491="High","BUG",IF(B491="Significant","BUG",IF(B491="To be Confirmed","?","SUGGESTION"))))

This query means: look at cell B491, if it says "Critical" (or "High" or "Significant" - *note* there are 3 IFs for this) then populate [this] field with the word "BUG". 4th IF: if cell B491 = phrase "To be Confirmed" then populate with the character ?, otherwise populate field with the word SUGGESTION.

*Don't forget to count all the open brackets and make sure you have the same amount of closing brackets at the end.

Geek Stuff: Microsoft Excel Match and VLookup

Example of what I am wanting to achieve with this combination of functions in Microsoft Excel:

If contents of C3 appear anywhere in range on "Extract" worksheet ("Extract" is the name of one of the worksheets in my spreadsheet) then perform the VLookup function. Otherwise return 'Not in Functional Results' in the cell that contains this formula.

Value Lookup - look up value in C3 (on this worksheet) in the specified range on Extract worksheet. Return the value in the 6th cell of the column (of the range specified). Use FALSE for exact lookup and TRUE for approximate match.

=IF(ISNA(MATCH(C3,Extract!$C$2:$C$5000,0)=0)=TRUE,"Not in Functional Results",VLOOKUP(C3,Extract!$C$2:$H$5000,6,TRUE))