Net Work Times

I couldn't have pulled this off without Chip Pearson's formula on work days and times. In a nutshell, when I was still at Pearson, I was asked to figure out how much time had elapsed between the creation of a ticket, and the time a support agent actually started work on that ticket.

This proved to be one of the ugliest formulas I ever wrote. It was two parts:

1. Figure out the earliest time that someone could have touched the ticket
2. Figure out when someone actually did, and how many "work hours" had elapsed between the opening of the ticket and that time

I started by creating named ranges:

The first was the days of the week, the day the ticket could have been opened, days elapsed between those two, and the respective start and end times, called "dayStart."

Day Email Came In Day Service Starts # of Days Start Hour End Hour
Sunday Monday 1 4:00 AM 5:00 PM
Monday Monday 0 5:00 PM
Tuesday Tuesday 0 5:00 PM
Wednesday Wednesday 0 5:00 PM
Thursday Thursday 0 5:00 PM
Friday Friday 0 5:00 PM
Saturday Monday 2 4:00 AM 5:00 PM

The second named range was the list of holidays for the year in question (2006-2007), called "holidayList."

The third and fourth named ranges were just the standard start and end times for the business ("startTime" and "endTime", respectively.)

A typical row of data in the spreadsheet began with the case number, and the date/time opened (columns A and B):

Day Email Came In
Sunday 00:00 00:00
Monday 04:00 17:00
Tuesday 04:00 17:00
Wednesday 04:00 17:00
Thursday 04:00 17:00
Friday 04:00 17:00
Saturday 00:00 00:00

I had the week # and year in columns C and D; in E, the first of two formulas:
=IF(AND(DAY(B2)>=2,DAY(B2)<=5,HOUR(B2)>4),text(date(year(b2),month(b2),day(b2)),"mm/dd/yyyy")&>=17,TEXT(B2,"dddd")="Friday"),TEXT(DATE(YEAR(B2),MONTH(B2),DAY(B2)+3),"mm/dd/yy"

In column F, I had the ticket touched time; in G, the Actual Duration:
=(IF(B2=F2,0,IF(AND(INT(E2)=INT(F2),NOT(ISNA(MATCH(INT(E2),holidayList,0)))),0,ABS(IF(INT(E2)=INT(F2),24*(F2-E2),(24*(endTime-startTime)*(MAX(NETWORKDAYS(E2+1,F2-1,holidayList),0)+INT(24*(((F2-INT(F2))-(E2-INT(E2)))+(endTime-startTime))/(24*(endTime-startTime))))+MOD(ROUND(((24*(F2-INT(F2)))-24*startTime)+(24*endTime-(24*(E2-INT(E2)))),2),ROUND((24*(endTime-startTime)),2)))))))/24)

YIKES.

Yeah, well, it worked.

If this code can be of any use to anyone, please do? A former colleague of mine (Jeff Z) mentioned on my Facebook page that he gets questions about this in class - here you go, my friend. (For those of you who live in the Sacramento area, Jeff Z is a former colleague of mine from New Horizons Computer Learning Center. While I was Training Supervisor there, I once had the honor of awarding him Instructor of the Year, where I described him as "more the All-Madden Team than the Heisman, but an MVP on any team.")

Thank you to Chip Pearson, for making this formula possible, and to the support management team at Pearson (no relation) for giving me the chance to make this happen. Be well.

Excel Wish List - HOT DAMN!

There's an old saying in the news business: "If you don't like the news out there, go make some of your own."

I decided to apply that same line of reasoning to Excel functions. Back in a previous blog post, I had asked out loud if certain functions could be included (see Microsoft Office Wish List, the section under Excel):

1. Count instances of a string (how many times does the letter "e" show up in "Geoff"
2. Find the nth instance of a string (find where the 3rd "g" shows up in a cell's text)
3. An ORDINAL function
4. A work hours elapsed function
5. A VLOOKUP function with an additional argument for what instance you're looking for

Well, I went 4 for 5 today. I've been able to pull off all of them but #4.

The code follows:

For #1, my challenge was to count how many times a letter or letters show up in a cell's text.

The code:

Public Function CountText(strTexttoCount As String, strTextToFind As String)
Dim strTextoFindLen As String
Dim intTextToCountLen As Integer
Dim intTextToFindLen As Integer
Dim g As Integer
intTextToCountLen = Len(strTexttoCount)
intTextToFindLen = Len(strTextToFind)
For g = 1 To intTextToCountLen
If Mid(strTexttoCount, g, intTextToFindLen) = strTextToFind Then
CountText = CountText + 1
End If
Next
End Function

This one proved to be pretty straightforward; pass the cell's text and the text to find as string variables to a function, then loop through the cell's text letter by letter. If the text to find is there, then increment a counter by 1. When you're all done looping, what is the counter at?

#2 was the Nth instance of a string - find what position in a string does the 3rd g, the fourth f, the second T show up?

The code:
Public Function ModeFind(strTexttoCount As String, strTextToFind As String, intInstancetoCount As Integer)
Dim strTextoFindLen As String
Dim intTextToCountLen As Integer
Dim intTextToFindLen As Integer
Dim intTextOccurrence As Integer
Dim g As Integer
intTextToCountLen = Len(strTexttoCount)
intTextToFindLen = Len(strTextToFind)
For g = 1 To intTextToCountLen
If Mid(strTexttoCount, g, intTextToFindLen) = strTextToFind Then
intTextOccurrence = intTextOccurrence + 1
If intTextOccurrence = intInstancetoCount Then
ModeFind = Len(Left(strTexttoCount, g))
End If

End If
Next
If intTextOccurrence <>
ModeFind = 0
End If
End Function

I recycled some code from the previous function to make this one happen, but same idea - loop through the cell's text, and keep looking for the "find" text. Count how many times you find it - when that counter is equal to the instance number, then get the position number, and return it.

#3 was the ORDINAL function idea. This one was actually the easiest of the four that I pulled off; code follows:

Public Function Ordinal(lngNumberToOrdinal As Long)
Select Case Right(lngNumberToOrdinal, 1)
Case 1
Ordinal = lngNumberToOrdinal & "st"
Case 2
Ordinal = lngNumberToOrdinal & "nd"
Case 3
Ordinal = lngNumberToOrdinal & "rd"
Case Else
Ordinal = lngNumberToOrdinal & "th"
End Select
End Function

All I had to do was get the leftmost digit of the number passed to the function, and then figure out what it was. Based on what it was, I added the appropriate suffix using a Select...Case statement.

My #4 challenge, I haven't worked on yet. I found some code from a guy called Jimmy Peña that looks promising...if I get his code to work, I'll let you know.

#5 was a bit of a bugger. My method is inelegant, clunky, and effective:

Public Function vlookupRow(rngRangeToSearch As String, varTexttoFind, lngRow As Long, lngColumn As Long)
Call FindBillyBrown35(rngRangeToSearch, varTexttoFind, lngRow, lngColumn)
vlookupRow = vOurResult
End Function


'******************************************************************************************
'This is a heavily modified version of some code from Dave Hawley of OzGrid.com that
'I've modified and re-purposed for my own function
'give credit where credit is due
'original can be found at http://www.ozgrid.com/News/LookUpMatchIndexVBAFind.htm
'THANK YOU MR. HAWLEY!
'******************************************************************************************
Sub FindBillyBrown35(rngRangeToSearch As String, varTexttoFind, lngRow As Long, lngColumn As Long)

Dim rFoundIt As Range
Dim iLoop As Integer
Dim intCount As Integer
' FindBillyBrow Macro
' Using the Find Method over Vlookup
'
With Sheet1.Range(rngRangeToSearch)
'Set variable to start search from
Set rFoundIt = .Cells(1, 1)
'Loop no more times that the name occurs
For iLoop = 0 To WorksheetFunction.CountIf(Range(rngRangeToSearch), varTexttoFind)

'ReSet variable to found occurence of name. Next loop search _
will start AFTER this Set cell.
Set rFoundIt = .Find(What:=varTexttoFind, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
intCount = intCount + 1
'Check if it's the right one.
If intCount = lngRow Then
vOurResult = rFoundIt.Offset(0, lngColumn).Value
Exit For 'Leave loop
End If

Next iLoop
End With 'Sheet1.Range("Data")

If IsEmpty(vOurResult) Then
vOurResult = "#N/A"
End If
End Sub

I did the call to the Sub so that I could debug this stupid thing; I was having a devil of a time getting the range to evaluate properly. What I found, after all was said and done, was that if I quoted the range in the function itself (e.g., =VLOOKUPRow("A1:C6","apple",2,2) then I could get it to work perfectly. I'm not thrilled with that solution, but it works, so maybe it's time not to look a gift horse in the mouth.

Well, it's a start...just goes to show that if you want to throw down the gauntlet, be prepared to pick it back up, as well. Let that be a lesson to you. Until the next lesson, be well.

Office 2010 - Thanks Microsoft!

PowerPoint
The Animation Painter button is WONDERFUL, and the Insert Screenshot button is equally superb. On balance, a lot of good new features.
1. Save as WMV: Check.
2. Find and Replace: No go.
3. Consistency and Professionalism: No go.
4. OK, I asked for more templates. The new templates are a HELL of a lot better than previous versions, so there is some improvement on this point. Between that and the Color Schemes introduced in 2007, we're getting there.
5. Navigation Menu Wizard and navigation buttons – Nothing. That's a real black eye, considering how many good shapes there are.
6. A better slide sorter – Still nothing. The ability to run the slideshow in one monitor and then edit in the other window is nice, but not a real improvement.
7. More stock photos, less clip art in the Gallery, both online and installed. Clip art plain SUCKS.
8. Still no color picker, but I suppose I could save custom color schemes as part of a theme. OK, I could live with that.
9. VBA – Doth mine eyes deceive me?! The "Record Macro" button is gone?!?

So how'd we do? I'll say we went 2 for 10, or 20%. PowerPoint had a pretty poor showing. D+ on my wishes, but the overall "how much better is it" is a solid B.

Microsoft Access
Access has many ways that it now integrates with SharePoint. Can't really fault Microsoft for wanting to do that, but I'll still wish for a more platform agnostic ASP.

1. Data Access Pages: Yeah, sort of. You can publish a database to a SharePoint server running Access Services, so that's moving in the right direction.
2. Parameter Query Wizard? No go.
3. Macro Wizard: HOME RUN! They really nailed it on this one! The ability to create steps and procedures in macros in Access 2010 is really fantastic.
4. RegEx, they get a pass on this one. If it's not in Office, it's not in Access.
5. Better and more updated form/report templates, and a greater flexibility to customize those templates: It's getting there.

I'd say on the Microsoft Access front, they went 3 for 5 at worst, and 4 for 5 at best. I'll give it a B.

Microsoft Word
I'm alone in the world on my belief Word needs slimming, apparently. No sense condemning Microsoft for catering to the majority.
1. Regular expressions, like Access, gets a pass.
2. Improved macro recording: I still can't use the mouse to select text, or click anywhere. Yippee.
3. Version control is going backward, not forward. Still no true "versions" feature, outside of SharePoint.
4. The Track Changes feature is better when the document is downloaded from SharePoint, and then version controlled using its native functionality. That's par for the course, I suppose.
5. Mail Merge: It HAS gotten better; now, my merge fields are on a pull-down menu, so I don't need to have that window open. Nice.

Hardly the finest hour for Word, but it's going in the right direction. I'll give it a B.

Outlook
Nice to see the Ribbon. The Actions buttons are really slick.
1. Hotmail? Same problem.
2. Haven't seen a Premium Live option.
3. Nothing on forms.
4. This is coming in October 2011, so we'll call it happy.
5. Duplicate Contacts – still nothing.
1 for 5 on the Outlook list, so that's an F. Upgrade it to a B- for the other features they did add.

Excel
The overall look and feel of Excel 2010 is just right, and a few of the new features THRILL me.
1. Still no Custom List Wizard.
2. PivotTables – WOO HOO! The Slicer has addressed this problem in SPADES! Nice work!
3. Show Report Filter Pages still does what it used to.
4. I may have to write my requested functions, see how I do. Bummer. But, again, as I mentioned, this is because I'm in the minority.
5. Still no love for Conditional Formatting in PivotTables. Strike two.

I'll give them an B+ on this one, just because the Slicer is SO COOL, and some of the other stuff I'm asking for is understandably esoteric. The ability to truly copy Report Filter pages is the next big step I see needing to happen, though.

Overall
On balance, it looks like Office 2010 is moving in the right direction. I don't know if the more esoteric features I'm asking for (like Regular Expressions) are worth pursuing. If I can write my own custom functions, I'm good there. But some of the other functionality, I feel like Microsoft needs to acknowledge that we live in a multimedia world, and we should respond accordingly.