Daily Dose of Excel

The twitter experiment is now over. I used Tweetake to get my tweets into a CSV, which I then opened in Excel.

I tried to post every 1/2 hour. At the time of the post, I’d write what I did since the last post. Since I usually did more than one thing, I had to split each entry. My plan was to split the tasks evenly among the time. That is, if I did three things in 27 minutes, each thing would get 9 minutes. Not exact, but close enough for government work.

Rather than write a macro to split the tweet, I just did it manually. It probably took the same amount of time, but writing the macro would have been more fun. Once I split them, I used the old F5 Fill in the Blanks trick to extend the time, the id, and the description.

Next I parsed the day out using =DATEVALUE(MID(B2,5,6)&", "&MID(B2,27,4)) and the time using =TIMEVALUE(MID(B2,12,8)). The date/time Twitter exports is Mon Aug 25 12:30:34 +0000 2008.

To get the elapsed time, I used this formula =IF(C3<>C2,1/24/2,(D3-G3)/COUNTIF($A$3:$A$101,A3)).

Click to embiggen.

and in G3: =VLOOKUP(A3-1,$A$1:$H$101,4)

If there’s a change in the day, it’s assumed to be 30 minutes. Otherwise it takes the change in the time divided by the number of instances of that tweet. The previous time is calc’d in G3 using VLOOKUP without the fourth argument.

Finally, I made a pivot table. Class in the rows, sum of elapsed time in Data. Then I sorted on time and formatted the field.

Misc. includes lunch, general screwing around, and things I just couldn’t shoehorn into a category. Information System is time developing spreadsheets to do what Quickbooks simply cannot. Marketing is a little high because of a special project. Payroll is a little low because it wasn’t a payroll week. Time Management is time spent prioritizing what I have to do.

I think I’m done using Twitter for this purpose. Which means I’m probably done using Twitter altogether. We’ll see. But I had a very productive week this week and I think it was because I was going to be accountable, even if it was only to myself. I think I’d like to continue recording my time and get a full month of data. To do that, I’m going to write an Excel program where I can make a quick note and it will record the date, time, and category. Every time I make a note, a timer will start to remind me if I haven’t done it in 30 minutes. Of course I’ll post that here.

In Excel, you can get the Username by using Application.UserName. The Username is what is entered on the General tab of Tools > Options. Inexplicably, you cannot get the user's initials, but you can in some other Office programs. I read a suggestion to automate Publisher and get the user initials via that object model. That seems a little extreme to me.

This is what I'm using. It gets the first character of, up to, the first three words in Application.Username.

Some testing:

I write some data to an Access database, after which I retrieve the Autonumber that Access generates. Prior to writing the data, it sits in a class and all of the classes sit in a collection. The collection needs a unique string for each entry, so I create one until I can the proper one from Access. Just a place holder that's unique. Specifically, I use this code:

If this record has already been written to Access or has already been created, it will have a RoyaltyLineID that's not zero. Otherwise I create a random number between 1 and 99,999 to serve as a unique id for the collection until such time as I can get a proper Autonumber from Access.

After thousands of RoyaltyLines processed, it finally broke. I was entering a particularly large order (the specific invoice line had 66 royalty lines), but only got to line 14 and I got a duplicate. The tenth RoyaltyLineID was the same as the fourteenth. To quote Phillip J. Fry:

Oh, the fools! If only they'd built it with 6,001 hulls! When will they learn?

I guess I'll increase it by an order of magnitude, but is there a better way?

I signed up for Twitter a couple of months ago, made a few posts, and pretty much abandoned it. If you're not familiar with it, Twitter is a micro-blog. You make posts of 140 characters or less. I don't follow many people and I'm not sure what value this whole idea has. I know that NASA used it recently to keep people up-to-date on Mars/Phoenix. That seems like a pretty interesting use. I have to believe that an interplanetary mission is far more interesting that most people's day.

Last week I decided I was going to write down what I did all day for five straight days. Every 1/2 hour, or so, I will jot down a note about what I'm doing or what I've done over the last half hour. I think I have a good idea what I do all day, but I want some objective evidence to see if my perceptions match reality. I was thinking I need a little program that popped up a userform every 1/2 hour for me to type my note. Then I thought that Twitter might work well for this. It won't pop up a reminder, but at least I could make my notes from anywhere.

I'm not encouraging you to follow me on Twitter. It will be excruciatingly boring, I can assure you. However, if you ever wondered it what you're day would be like if you had chosen accountancy as a career, well this should be right up your alley. http://twitter.com/dkusleika

Hi all

Great tip: Thanks to Dave Peterson

For the student (or families with students):
http://www.microsoft.com/student/discounts/theultimatesteal-us/default.aspx

Ron de Bruin
http://www.rondebruin.nl/tips.htm

Hi all

I add this page to my site last week with a few examples to add missing controls
to the Quick Access Toolbar or Ribbon.
http://www.rondebruin.nl/notinribbon.htm

I am curious which missing built-in commands the readers of this blog have add to the QAT or Ribbon.

Tip: John WalkenBach posted a add-in for the speech controls on his site this week.
You see the speech controls also in the example ribbon group that is on my site.
http://spreadsheetpage.com/index.php/tip/add_the_speech_controls_to_the_ribbon

Ron de Bruin
http://www.rondebruin.nl/tips.htm