The number 666 is so decorative, it looks like a trio of little pigs’ tails. (Cue for a fairytale) Is that why it’s the number of the beast?<\/p>\n

I have to do a 666 post before midnight or I’ll change into a pumpkin. (Cue another fairytale).<\/p>\n

Although, being something of a pedant, I can’t really see 06\/06\/06 as a legitimate candidate for the day of the Beast. It’s really 06\/06\/2006, well 6\/6\/2006 even. This number is mainly remarkable for being one of the few dates in the year when British and American dates collide and are correct in both systems. If you’ve ever had to deal with spreadsheets where the data has been imported in one date format and you’ve found half your dates get rejected and almost all the others are wrong, you know what I’m talking about.<\/p>\n

I have worked out a solution for this. Try to stop Excel (we name the guilty here) from trying to parse the dates itself, first. Insert 4 columns next to the column with the duff dates. In the first cell of one column, type =month(reference the first cell in your duff date column);<\/strong> in the next column put =day(reference the cell etc);<\/strong> in the third put =year(etc, <\/strong>are you beginning to detect a pattern?)<\/p>\n

=<\/strong>the cell address with the day in<\/em> &”\/”& <\/strong>the cell address with the month in<\/em> &”\/”& <\/strong>the cell address with the year in<\/em><\/p>\n

This cell should now hold something like =b2&”\/”&c2 &”\/”& d2<\/strong> The cell display should show a date. If you are lucky, it will be in English date format.<\/p>\n

Copy all four cells down until you can see a correct date for every date in your range, in the fourth column. Select the lot, copy<\/strong> them then paste the values<\/strong> over the existing formulae. You can now delete the columns holding the original dates, the days, months and years. You will be left with a column of dates in English format. Apply English date format as well, if you need to. (Even if it’s not applied and the dates are displayed in American date format, they are still correct English dates, i.e. 15\/11\/06 is seen as 15th November not an invalid date that’s trying claim there are 15 months in that year)<\/p>\n

(Obviously, reverse the day\/month order to change English dates to American. Start saying rout<\/strong> for route<\/strong> and Baysil<\/strong> for basil<\/strong>, while you are at it)<\/p>\n

I hope you are impressed. This blog is not just a pretty face shamelessly trying to get higher in the blog reading stakes by picking out words from Technorati’s top searches. As you see, we also provide a public service.<\/p>\n

* Concatenate is computerese for joining strings together. It really is a word. Yes, I was just showing off.<\/p>\n