Twittering Away the Day

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.

Posted in Uncategorized

6 thoughts on “Twittering Away the Day

  1. Interesting stuff, Dick. I think your comment about being more productive because you must be accountable is really true. Isn’t that what lawyers do with their billing?

    If you’re done twittering, then I’m done twittering. I just started it because of you. And Deb.

    Using Excel is definitely the way to go. Here’s an idea: Create a bunch of general work-oriented categories. Then, when the timer goes off, have XL display a Userform that lets you allocate the last hour by percent in each category. Even if you’re away from your desk when the timer goes off, the code can determine the time since the last entry and adjust. That might be a reasonably accurate way to determine what you do all day — assuming you really want to know.

    There must be actual software that does this. Probably designed for lawyers.

  2. I have conducted a similar experiment on myself that I started about 6 years ago. I only account for 7 categories and make 4 to 8 entries per day into a spreadsheet. Time entry is made is by using data validation to provide a selection of times near now. The selection is handy for forgetfullness or other delays. Results stabalized after 1/2-1 year of observation. A log of meetings was handy when called as an expert witness. I’m a hydrologist and most of our projects operate on the time scale of years so less intense sampling was adequate for me.

    I learned that I spend 65 percent of my time on a job that I am oficially funded for 50 percent. Ignorance can be bliss.

  3. JKP: or maybe he did

    “Remember a day before today
    A day when you were young.
    Free to play alone with time
    Evening never came.”


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.