Applying Timezone Offsets in Excel

Here’s how to convert a UTC date record in excel to a local timezone where that timezone is supplied separately.

For me the timezone offset is stored in seconds. For you it may be different, if it’s minutes then you can use a similar structure with different values. If it’s HHMM rather than a number, you’ll need to work out how to extract those components.

The ‘N’ column has the UTC date (e.g. 20/01/2012  6:36:52 AM).  The ‘O’ column has the timezone (e.g. 36000, i.e. GMT+1000)

The formula to calculate the local time when the timezone is in seconds:

=N2+TIME(INT(O2/3600),INT(MOD(O2, 3600)/60),MOD(O2, 60))

The formula to calculate the local time when the timezone is in minutes:

=N2+TIME(INT(O2/60),MOD(O2,60),0)

You’ll need to set the cell’s formatting to date, otherwise you’ll just see Excel’s internal date representation. And of course, you can subtract if you’re going the other way (to UTC).

Basically this just converts the seconds into a TIME object that Excel can understand.

Installing Windows7 on a new MacMini Using Lion

Here are my steps for installing Windows7 from a DVD on a new MacMini via bootcamp, without needing an external DVD drive for that mac, as always YMMV.

You’ll need:
1. A windows DVD
2. Another Mac with a DVD drive, e.g. your MacBook Pro (if you need to make the ISO)
3. USB storage of 4GB or more (SD cards don’t work directly, but work through a USB reader).

Creating the Windows ISO

1. Insert your Windows DVD
2. Open ‘Disk Utility’ (if the DVD isn’t mounted, you’ll need to wait for it to mount, close and re-open ‘Disk Utility’)
3. Select your Windows Disk in the list (the disk, not the drive)
4. Click the ‘New Image’ button in the toolbar
5. Select ‘DVD/CD master’ as the Image Format
6. Press Save

Setup

Copy the ISO onto your MacMini however way you can.

Run Bootcamp, it will prompt you to select the ISO, and the USB storage, and take it from there.

Turbocharging git on Mac OS X

This weekend I discovered two awesome graphical tools to improve my git experience on Mac OS X:

The most awesome is KDiff3 (search for ‘Apple Mac OSX binary’ on that page for the Mac binary). This is a three-way merge tool with an editing panel below. Seriously where has this been all my life? Amazing.

Download it, and install – make sure it’s in your system PATH. Then simply run git config --global merge.tool kdiff3 to set it as your default merge tool. Now every time you get a conflict, your git mergetool command will actually be rather helpful! Basically you are shown the base revision (that both branches split from) in the middle, one branch either side, and an editor below allowing you to pick which side you want and/or edit the file directly.

The second tool I (re)discovered was SourceTree (Mac App Store DL Link). It is a Mac GUI client for git. I’m pretty comfortable with the command line, but what I love about this tool is it allows me to review my changes before I commit them (either staged or unstaged), discard hunks I don’t want, and of course open the file in question for editing. Very convenient. git diff is nice and all, but I love having that handy ‘discard hunk’ button in SourceTree so I can skip my silly changes. For me this is the most useful feature, though no doubt there are others. Oh and it’s free (it’s a bit confusing, as there is a “trial” period, but after the trial you simply register for free, and are given a license).

more »

Viewing Unread Inbox Emails in Gmail

is:unread in:inbox

About has some more tips.

iTunes Match Broke the ‘Play’ Button

iTunes match is a fantastic idea. All my music, everywhere. As someone who generally doesn’t lug around my external music disk this is a truly awesome. Want to play a track that wasn’t sync’d to your iPhone? Sure just download it. Clean up some dodge CD import? Amazing. Not needing to upload everything to the cloud? Brilliant. A$35? Bargain.

Pity about the implementation.

So I matched (which was quick), and uploaded (which took *ages* imagine how bad it would have been without the matching, iTunes Match concept++), cool.

Last night I downloaded a few playlists, including “Rated”, and then “All Music”. Well I got rated, but all music only got the 3 tracks while I slept. Right now I type this from an aircraft between Brisbane and Melbourne. Some music would be nice. Here’s a little breakdown of the issues I see when playing the music that did download:

• Tracks that are on my device don’t play. Either I press the play button and nothing happens (it sits there on 0:00, but showing me the pause-button like it would if it was actually playing), or they are skipped. Now I turned off the option in Settings to show “All Music” so all I should be able to see are the ones downloaded.
• Skipping a track will often just stop the music playing entirely. The fix is to back out, and go into another playlist. Or kill the ‘Music’ app
• The track name & track is completely mismatched (NB. I think this is an issue on the device, not that iCloud matched the wrong music, but I can’t confirm this yet).
• If I avoid skipping tracks it works better – but then I have to put up with all the crap on my iPhone
• My back button doesn’t work sometimes

Knowing how to terminate apps in iOS comes in very handy!

I remember when a few of my friends had Windows 6.5 mobile phones that couldn’t answer a phone call. Pretty critical bug on the most important feature. Well my iPhone’s iPod feels about that stable – not good.

Believe it or not – this is not all the problems with iTunes match. Those are just the issues with playing music. Here are some more:

• Downloading music on your device (the only way to get it, it seems) creates a massive (invisible to the user) download queue with potentially thousands of items. If you then go and download an App from the App Store, it will be put at the back of this invisible download queue, and yeah – never download.
• Every day (I think because of this massive download queue I created) iPhone will ask me for my password. Why?? I’m not buying anything. I’ve not logged out. In fact, the iPhone has to be one of the few things I know that just can’t seem to save my password. Confirm my password if I buy a $100 app? Yeah that’s a great idea. Confirm my password if I want to resume downloading my iCloud music? Stupid.
• There is no way to stop this process once started. Even if you press cancel on the password dialog it will ask you an hour later
• Whatever you do, don’t log out of your Apple account with such a download queue and log in with your alternate account. You will get an error message saying that the track can’t be downloaded as the account doesn’t have iCloud. When you click OK, you get another one (probably one for each song). If you have thousands of songs in the clue your iPhone is now unusable – time to power on and off, or disable Wifi.
• Oh, and logging out of your account will delete all your iCloud match playlists & music. Your device is now a single-account device. Go it? [you can still have multiple-accounts through iTunes on your Mac, as it won't nuke your music library there – though you will have to re-add the computer to match every time you log back in, but that's not too bad].
• If you turn iTunes Match off & on, all your playlists will be deleted until resync.
• There is no way to just download all your music in one go (wouldn’t this be what most people with capacities greater than their music collection want?) – what did I buy the 64GB iPhone for again? The workaround is to create a playlist of all your iCloud tracks, then scroll to the bottom (can take a while), and hit download (which creates the aforementioned massive download queue – beware!).

Deleting music from the device does seem to work OK – you can delete playlists, albums, tracks, etc. Deleting it this way still leaves it in the cloud.

Some technical guesses as to these problems.

• Some of my music was corrupted on download
• Once again, nobody seems to test anything on non-silicon-valley-blazing-fast-internet. My connection isn’t too shabby, one of the better ones money can buy in Australia.

The long shot of it is: my Music app is completely buggered, practically unusable. Like my friends phones which couldn’t answer phone calls with Windows Mobile 6.5, my iPod can no longer play music with iTunes Match.

Basically Apple created an awesome cloud storage system for your music without a way to actually *manage* it on your device.

My advice: wait for the service pack… If you do persevere, don’t queue large downloads by downloading small playlists one at a time.

I’m going to turn match off on my iPhone but keep it setup on my Mac – and sync with iTunes for now. If I’m ever caught without my external HDD & my iPhone loses all it’s music (less likely with OTA updates I guess), I can always turn it back on.

Review: Clarify (Mac App)

If you’ve ever had to explain a sequence of computer steps to someone, then you need the Mac App ‘Clarify’… my life is so much easier now :)

Download

It helps you take a bunch of screenshots (or you can import any you already have via the clipboard, right click open, etc), then annotate the images and add text as well.

You can then export the result as a PDF or even upload it to their servers where you get your own custom subdomain.

One of the most useful Apps I have ever bought.

Migrating data in a Rails migration

Turns out it’s easy to whack any old piece of code into the migration for migrating your data, for example these work well for some arbitrary SQL statements (I used UPDATE ones…)

ActiveRecord::Base.connection.select_one('SELECT COUNT(*) FROM mytable')
ActiveRecord::Base.connection.execute('SELECT * FROM mytable')

Thanks revgeorge.

Debugging Memory Crashes in iOS

For starters, enable NSZombies.

If you need to try and work out who’s doing the bad release, I found this logging helpful (and you can break point it too), basically you override retain and release.

- (id)retain
{
	NSLog(@"retained. retain count now %d", [self retainCount]+1);
	return [super retain];
}

- (oneway void)release
{
	NSLog(@"released. retain count now %d", [self retainCount]-1);
	[super release];
}

Or course this is all a bit redundant with ARC… the sooner we all use that the better I guess.

Downloading All Music in iTunes Match

iTunes Match is pretty cool. Not without its faults, but still pretty cool.

One thing I couldn’t see how to do was to simply download all my iCloud music onto my device.  Only individual playlists, artists etc. I have a 64GB iPhone so there is definitely plenty of room for my library.

Here’s my workaround solution: basically, you create a new Smart Playlist named ‘iCloud’ which simply matches all music that is in iCloud.  Make sure you set the match rules to ‘any’, and give the three conditions I have so everything is matched.

Then on your device you can view the playlist, scroll to the bottom and hit download. Nice :)