Converting time to period using VBA and Python

Code it and they will come

Ever since installing Google Analytics on this blog I’ve kept one eye on it to try and determine any trends or direction to the traffic. As one would expect there are three main sources that traffic originates from; LinkedIn (I placed it on my profile), YouTube referrals (I loath it when people cover their videos in ‘click here’ so I include a small link back in the post info) and search engines.

The flow of users from these is subtly different too. Those from LinkedIn will rather randomly navigate around without delving too deep. YouTube users spend time on the topics the video sent them through on. The final group – and the one I find most interesting – are those based on search engine matches.

The user is always right

At some point on mid-2012 someone Googled “convert time to period vba” and landed on this blog. At the time I hadn’t written the page which, quite by coincidence, gave an example of how to do it via an Excel formula and also in VBA. However, as I’m constantly reminding our analysts at work, there are many ways to skin the proverbial computer cat and thus, for the original Googler I thought it only right to write a couple more.

So, for user who wanted to know how to do it in VBA…

The first approach repeats the function given in the previous post and is thus a follows. Personally, I prefer this approach most of the time.

Public Function HHPeriod(Optional Date1 As Variant) As Integer

If IsMissing(Date1) Then
 HHPeriod = Round(Time() * 48, 0)
Else
 HHPeriod = Round(TimeValue(Date1) * 48, 0)
End If

End Function

This a approach extracts the hour and minute values from a value entered into A1 and returns the period number to B1. Obviously, it would be just as easy to return the period to a variable, message box or whatever you wish.

As you’ll see in the Python example below, the Case statement can be reduced – minutes before the half hour will add “0″ – therefore we could exclude them from the statement. I’ve left it in to demonstrate the statement.

Sub TimeToHH()
Dim TimeHH As Variant

TimeHH = Range("A1").Value
CellHour = Hour(TimeHH) * 2
CellMinute = Minute(TimeHH)

Select Case CellMinute
  Case Is < 30
    CellMinute = 0
  Case Is >= 30
    CellMinute = 1
End Select

Range("B1").Value = (CellHour + CellMinute) + 1

End Sub

And one for the Pythonesta’s…..

I was mulling over the best way to write the Python code for this task and thought I would base it on the previous VBA example to allow comparison of the two languages.

import datetime

now = datetime.datetime.now()

current_hour = now.hour
current_minute = now.minute

if current_minute >= 30:
    HH_Add = 1
else:
    HH_Add = 0

print (current_hour * 2 + HH_Add)+1

Video walk through of the Python code

Be Sociable, Share!

•