It’s that time of year again! Both Steam and GOG have holiday sales going on right now, and it’s a great time to pick up some great entertainment at a solid discount.

In particular, GOG’s “Pick 5 & Pay $10” is a total steal, especially if you like adventure games, and the success of Double Fine’s Kickstarter campaign says many of you do. Botanicula on its own is worth $10 easily in my opinion, and if you haven’t yet played Machinarium you can score them both. There’s also Gemini Rue, To the Moon, the Blackwell Bundle, and Resonance. I don’t know much about any of them except they’re all adventure games.

If adventure games aren’t your bag, I can vouch for Spacechem as a very solid puzzle game. Defcon also looks interesting though I haven’t played it personally. Anomaly Warzone Earth is an engaging twist on tower defense, while Torchlight will bring back many (hopefully good) memories of Diablo and Diablo II.

Honestly, there’s not much on the list that wouldn’t be a solid choice as far as I know, except possibly Geneforge – that one looks pretty bad. But hey, maybe I’m wrong! In the end, I wound up going with:

• Botanicula
• Gemini Rue
• To the Moon
• Symphony
• Unmechanical

On the Steam side, there’s a ton to choose from, but in particular you can pick up Limbo for less than $3! For the next two hours at least. If you haven’t played it, it’s required gaming. Required.

Speaking of malaria, Bill Gates:

We may even see a malaria vaccine in 2015.

Amazing.

You may, at some point in your life, find yourself needing to take some data that represents a combined date and time and extract from it only the date or only the time. And should you find yourself in such a position, then this post might just save you a bunch of time.

The Magic Formulae

Assume our data looks like this:

A1 contains our date/time, and we want to put the date only in B1 and the time only in C1.

Extracting the Date

Put this formula in B1 to extract the date:

=TRUNC(A1)

Then format B1 with whatever date format you want. Done!

If you search the web for answers to this basic question, a lot of the suggestions are to use a combination of the DATE, MONTH, YEAR and DAY functions.¹ That works, but it’s really silly in my opinion – TRUNC is almost certainly faster, especially if you have a large quantity of data.

Extracting the Time

Put this formula in C1 to extract the time:

=MOD(A1, 1)

Then format C1 with whatever time format you want. Done!

How It Works

The best explanation I’ve found comes from an article titled How Excel Handles Dates and Times. Unfortunately it’s inexplicably an MHTML file, so you might have trouble opening it.² I made a PDF copy of the article that should work for most people.

Anyway, from the article:

To Excel, a date is simply a number. More precisely, a date is a serial number that represents the number of days since the fictitious date of January 0, 1900. A serial number of 1 corresponds to January 1, 1900; a serial number of 2 corresponds to January 2, 1900, and so on. This system makes it possible to deal with dates in formulas.

OK, that’s pretty straightforward. But what about times?

When you need to work with time values, you simply extend the Excel date serial number system to include decimals. In other words, Excel works with times by using fractional days. For example, the date serial number for June 1, 2007, is 39234. Noon (halfway through the day) is represented internally as 39234.5.

Ahhh, there we go! Everything to the left of the decimal represents the date, and everything to the right represents the time. We don’t actually need to worry about actually converting those numbers into dates and times – Excel handles that for us, but behind the scenes everything’s a number. And since we’re just talking about numbers, we can apply some simple mathematics.

In the case of dates, we truncate the number using the TRUNC function, which simply lops off the decimal numbers. This is obviously fast – all Excel needs to do is forget about the decimal values.

The time case is a little bit tricker. We want to do the same thing, but instead of lopping off the numbers to the right of the decimal, we want to lop off the numbers to the left. Thankfully, math saves us again. The modulo operation (available in Excel via the MOD function) allows us to find the remainder of a division operation. Since we want the numbers to the right of the decimal only, we mod the value by 1. Since the modulo operation gives us the remainder, the result is the decimal portion of the original number.

Again, since this is simple math and doesn’t require any fancy conversions of the data, it’s faster, not to mention simpler to write in the little Excel formula window.

I hope we can all agree there is sufficient compelling evidence that math is awesome.