Consumer banking security: two (or three) tales of farce

Posted on By Mark Wilson
This content is 11 years old. I don't routinely update old blog posts as they are only intended to represent a view at a particular point in time. Please be warned that the information here may be out of date.

I’ve written before about the nonsensical nature of UK banking websites, with security theatre that’s supposed to make us feel that a sequence of restrictive usernames, passwords, passcodes and memorable words (all passwords of one form or another) linked with publicly available information (date and place of birth, etc.) is somehow keeping us safe.

Unfortunately, that farce looks set to continue for some time to come…

Second factor authentication

Recently, my bank (First Direct) went a step further in an attempt to introduce a second factor to its logon process (i.e. something I have, in addition to something I know).

“Bravo”, I thought, “at last, similar security measures for consumer banking, to those that are used on the back-end by employees”… except I was wrong.  At least, I hope I was.

First Direct gave me three options:

  1. Send me a device to generate a secure key.
  2. Use an app to generate a digital secure code.
  3. Continue using the old methods for Internet Banking logon, with reduced functionality.

On the basis that any device sent to me is unlikely to be where I am when I need it, I elected for the app option and, after upgrading the First Direct app on my phone, I went through a registration process.  I don’t recall the details of the process but the end result is that I now have a “Digital Secure Key password” (oh goody, another password!) in the mobile banking app, that can be used to generate a code to log on to the full website via my browser.

And how complex is this “Digital Secure Key”? Just 6-9 alphanumeric characters – no better than a very simple password – and as that’s now the only level of security between a mobile phone thief and my bank account (aside from a PIN on the phone), the app on my phone actually less secure than it was previously with the username/memorable data combination!

Still, at least there is some kind of second factor for website access…

Never write down your PIN (except when the bank does that for you…)

We all know that we shouldn’t write down the PIN for our cards, yes?

Ever.

It’s in the terms and conditions for your account – and if the bank suspects you have compromised security in this way they are unlikely to be able to help if there is fraud.

I have a Hilton Hhonors Visa card, provided by Barclaycard and, a few weeks ago, they sent me a new card as part of the rollout for Visa payWave (contactless) functionality.  The card had a sticker attached, telling me to use it from 23 June – and in the meantime I could use my old card. Separately, they sent a new PIN (quite why my new card couldn’t use my old PIN is beyond me) and, as soon as I received it, I went to an ATM to change the PIN to one I would remember.  Except I couldn’t – because the card wouldn’t work until 23 June!  I even tried using a Barclays ATM.

In the end, I had to keep the card and the PIN in my house for a few weeks until they were both valid.  Doesn’t seem very secure to me… and I wonder who would be liable if the card and the letter had both been stolen in the meantime?

And don’t get me started about 3-D secure

Verified By Visa.  Mastercard SecureCode. Just another password to remember – and as far as I can tell just a way for the banks to pass fraud risk on to merchants!

Improving iPhone battery life for use as a cycle computer

Posted on By Mark Wilson
This content is 11 years old. I don't routinely update old blog posts as they are only intended to represent a view at a particular point in time. Please be warned that the information here may be out of date.

One of the uses for my iPhone is as a cycle computer.  I don’t have a Garmin (many people who do tell me that the Garmin Connect website is a pain) and I prefer to log my rides on Strava, with the iPhone in a Topeak Ride Case.  The downside of this is that the iPhone will sometimes run out of juice on a long ride.

For that reason, on my recent London to Paris cycle ride, I needed to do everything possible to boost the battery life.  Here’s a few of the things I did – and they seemed to get me through the day:

  1. Turn (almost) everything off. 3G. Wi-Fi. Bluetooth. Roaming data (especially on the continent).  Some people say to use flight mode and, whilst that may work on some operating systems, on iOS it will also turn off the GPS, which would make the iPhone a pretty useless cycle computer!
  2. Buy a battery booster. £4.95 on eBay got me a little battery booster that will give something between a third and half a charge to my phone.  Using that at lunch, or on the afternoon break, gave a little extra power to keep the phone alive for a few hours.
  3. Try to avoid the temptation to constantly look at the screen and mionitor your stats.  The screen is the big power drain and I even wore a watch on this trip so I wasn’t tempted to look at the phone for the time!

Content, typography and geeky stuff (#MKGN)

Posted on By Mark Wilson
This content is 11 years old. I don't routinely update old blog posts as they are only intended to represent a view at a particular point in time. Please be warned that the information here may be out of date.

As another Milton Keynes Geek Night approaches, I’ve realised what a terrible blogger I’ve been lately… heck, maybe I don’t even qualify as a geek any more! I thought I’d try and make amends with these notes from the last event – which was, as always, an enjoyable evening with a real mix of speakers providing plenty of food for thought.  If these little tasters leave you wanting more, check out the audio on Soundcloud:

Future Perfect Tense

The first talk was from Relly Annette-Baker (@RellyAB) – bursting with energy as she blasted through her talk on “Future Perfect Tense” – or creating content for in imperfect web. The talk is worth another listen but, effectively, Relly spoke of the separation of content and design so that websites can evolve as technology moves on.

Taking a physical world example, every piece of Lego is built to combine with every piece of past and future Lego, whereas Playmobil have hard-cast mounds.

On the web, our content needs to be structured chunks that build packages (screen sizes, needs, requirements, points). Once we understand what the content is, it can be put in the CMS, and we need to understand how people are using that content.

Additionally, we shouldn’t fork content – pragmatically there are times when we may need a separate mobile site but that’s short term – long term we need to cope with a plethora of devices (from a 1″ watch to a 50″ smart TV – we can’t have a new site for each one). We need to have a plan to move content to new sites.

Netflix is on 100s of devices but only has 3 content formats – short, medium and long.  And, as for the next big thing – Google “iPhone will fail” to see how, in 2006/2007, people were seeing an expensive smartphone, not a cheap portable computer.

None of us know the next cool thing but we don’t need to. Build teams, tools and content that adapt whatever the conduit. Create chunks that can move around and be repurposed. The rest will look after itself…

Knowing you know nothing

Knowing you know nothing was a short talk from James Bavington (@JamesBavington) who started out with a quote, from Socrates no less:

“The only true wisdom is knowing you know nothing” [Socrates]

Or, to paraphrase:

“You’re stupid if you think you know everything” [James Bavington]

There are many, many tools available to help us in creating websites (thankfully we don’t have to know them all) but web design concepts can be limited by our technical knowledge and ability:

James used an analogy about how he started investigating the Google Maps API to push his skillset and improve the user experience in a site he was creating.  This led him to a side project using the Google Maps API and experimenting with many features.

James takes the view that expanding your technical awareness (not necessarily ability) gives your creativity more to play with.  As someone who struggles with seeing loads of interesting “stuff” that I rarely investigate as fully as I’d like, I may try James’ advice for expanding my knowledge:

  1. Little and often.
  2. Set project challenges.
  3. Have a side project.
  4. Know that I know nothing.

Distributing the future more evenly, with JavaScript

Jeremy Taylor (@jdt_me) was next up – talking about distributing the future more evenly, with JavaScript.  The gist of Jeremy’s talk was that we’re not using the “slab of glass in our pocket” (i.e. smartphone) properly and that, instead of collaborating and solving the grand challenges of our age, corporations are in battle.

Many socially-ethical businesses will use “free” services that rely on ads – it’s just not healthy, Jeremy believes.

Browser is no longer a document browser but is effectively a hypervisor with each tab running in its own sandbox, and WebRTC peer connections to connect them, making the entire web a giant playground.

Citing the examples of Browserify (introduction) leading to WebTorrent, Jeremy suggests that we need simpler tools to create distributed systems and that JavaScript provides new ways to integrate and collaborate.

Sass: a whistle stop tour

Stuart Robson (@StuRobson) gave another 5 minute talk – about a CSS extension language called Sass (Syntactically Awesome Style Sheets).  Stu’s slides are on SpeakerDeck and he describes the talk as “a quick blast through what makes Sass an awesome extension of CSS that you should use no matter how marginally”.  It certainly looks useful to me – with variables, nesting, code snippets and more…

One minute talks

Then, there were the one minute talks with the usual bunch of recruitment pitches, conference plugs, and two that made me sit up and listen:

  • A new TechHub co-working space coming to Milton Keynes (although I can’t find any details on the web).
  • Fibe (sp?) are a young startup of school kids who understand they can only do so much (having said that, the speaker was really confident and came across really well). They are looking for an HTML hermit, a JavaScript genius or a database [demon] to work with and help them…

Take your stinking paws off my design you damn dirty developers

Finally, Andrew Clarke (@Malarkey) gave a provocatively-titled but fascinating talk that is intended to help developers udnerstand design issues.  Andrew’s talk normally takes an hour, so he cut it down to just typography (the full set of slides is on SpeakerDeck) and I could have listened for hours. whoever thought that the finer details of lettering could be so interesting?

I learned how it’s important to understand the size of type to be fused and how the measure (the width of the body of type) can be controlled for comfortable reading by reducing column widths or increasing font sizing (2-3 alphabets, i.e. 56-78 characters is ideal). I learned how adjustments to the measure need to be accompanied by changes to line hight and how different styles should be used for reversed out content to improve readability. I learned that the tracking (word-spacing in CSS) can be used to improve readability and that san serif typefaces need looser line heights.

Then there’s the area of typographically-correct glyphs:

  • Hyphens break words over multiple lines, or join-words.
  • En dashes are longer and denote a range (equivalent to the work “to”), for example 1–10 or Earth–Mars.
  • An em dash is even longer — for a pause — or a separate train of thought in the same sentence. Either use no space or a hair space. And two adjacent em dashes can indicate a missing letter in a w——rd.
  • Single and double primes should be used for feet and inches or minutes and seconds. Never for quotations!
  • Quotations use curly quotes: “ ” or ‘ ’.
  • And an ellipsis is not just 3 dots — it’s one entity… which marks a continuation.

[Related reading: 10 HTML entity crimes you really shouldn’t commit]

Moving on, Andrew spoke of markers and margins in lists, and of using CSS relative positioning to place © close to the baseline – and adjusting parentheses too!

In summary: designers and developers need a better understanding of each other’s work. They need to know how to work together!

When’s the next one?

If you think this sounds good, follow @MKGeekNight or check out the MK Geek Night website – MKGN number 9 is coming up on 12 June 2014 and speakers are:

  • Rachel Andrew (@RachelAndrew): Your own definition of success – choosing a profitable side project idea.
  • Dan Donald (@hereinthehive): Designing evolution.
  • Robert Bavington (@RobertBavington): Responsive Web Design Today.
  • Ben MacGowan (@BenMacGowan): The era of gratuitous animations.
  • Mark McCulloch (@wearespectaculr): Are you speaking Scottish in Tokyo?

There’s usually free beer and pizza too… so what’s not to like! 

Code dojo for test-driven development

Posted on By Mark Wilson
This content is 11 years old. I don't routinely update old blog posts as they are only intended to represent a view at a particular point in time. Please be warned that the information here may be out of date.

Every now and again, I think it would be great to do some coding, to give up this infrastructure stuff (or at least to give up the management stuff) and solve problems programmatically for a living.  Unfortunately, I also have a mortgage to pay, and certain expectations on living standards, so rewinding my career 20 years and starting again is probably not an option right now…

Even so, I took a C# course on PluralSight and, last month, I attended the Code Dojo that my colleague Steve Morgan (@smorgo) was running for some of the developers in Fujitsu’s UK Microsoft Practice.

Dojo is a Japanese word that means “a place of the way” with various explanations including a place where a group of people stay to discipline themselves.  So, it follows that a Code Dojo is a place where a group of software developers come together to be enlightened.

Our Code Dojo focused on a Kata, which is another Japanese term that literally means “form” – i.e. describing patterns of movements practiced solo or in pairs.  In this case, the pattern that we followed was one of Test Driven Development (TDD).  We used TDD to implement a software solution to a given set of requirements but, like all projects, the requirements start off incomplete and there are new requirements received at each stage of the project.

We each took it in turns to write code (even me), with the rest of the group observing and offering help where necessary.  The principle of TDD was used to write unit tests that are machine-executable expressions of requirements.

First, we wrote a test for a single requirement and then attempt to run it.  It should fail because the requirement isn’t implemented so we wrote just enough code to satisfy the requirement (and no more).  The next step is to run all tests and, if any fail, fix the failing tests or the implementation until everything works.  Finally, we refactored the code to make it more maintainable and supportable.

Very quickly, we had grasped the TDD mantra of “red, green, refactor” – i.e. at least one test fails, fix the code to pass the tests, then improve the code but tests must still pass.

The event was over all too quickly, and we ran out of time, but it was certainly worthwhile – and a great education for me.  we used C# and Visual Studio but really you could use any language to apply the principles and I really should give it another go at home.

Steve’s next Code Dojo is today but I can’t be there as I’ll be cycling to Paris as you read this (and, even if I wasn’t, I’d need to be at a management meeting!). Hopefully there will be more soon and I can continue my education…

How not to change the tyres on your bike

Posted on By Mark Wilson
This content is 11 years old. I don't routinely update old blog posts as they are only intended to represent a view at a particular point in time. Please be warned that the information here may be out of date.

Hopefully, in two weeks’ time, I’ll be soaking up the delights of Paris having cycled there from London – and I’m in full prep mode at the moment, buying up all sorts of things that I might need. I’ve also bought some things I definitely need – like some new tyres for my road bike.  They didn’t arrive in time for the bike’s service (mental note: when buying from Ribble Cycles, free postage doesn’t just mean first class instead of next day, it means it might take a couple of days to pick too) so, when my son’s football match was cancelled due to the downpour yesterday morning, I took the opportunity to change my tyres.

Because it was tipping it down outside and the garage is too full of “stuff” (and has pretty poor lighting), I decided to work in the kitchen.  Yes, I was that bloke with his bike in bits in the kitchen, and Mrs W. seemed to understand why.

Having changed the front tyre – rule 40 applies –  (and taken the opportunity to test inflating tyres with a CO2 canister…), I remembered that tyres are normally directional. Oops. I hadn’t popped the back one off yet, so I was examining what was left of the tread pattern to try and work it out, holding it up to the daylight from the kitchen skylight when… it slipped out my hand and my rear cassette made some nasty gouges in the edge of our rather expensive Silestone counter. Arghh.

After determining the correct tyre direction I found that, by luck, the front one was on the right way (50/50 odds) so I proceeded to change the back tyre.  I popped it back onto the bike – following rule 41 – inflated it with my track pump and, whilst watching the gauge more than tyre I didn’t see the bulge where the tyre had popped off the rim at one point. After pumping it up to 110PSI, I bent down to investigate and – BOOM! – the inner tube exploded right next to my ear. Not good.

So, spare inner tube fitted, careful inspection and this time everything seems OK.  I guess we’ll find out when I take it for a ride tomorrow.  Should have been a simple job – but was made far harder through my incompetence – and I spent the next couple of hours phoning around to try and find out if anyone can repair scratches/chips in Silestone counters…

There’s nothing in the Velominati Rules about being an idiot is there?

Three cycling challenges for this summer

Posted on By Mark Wilson
This content is 11 years old. I don't routinely update old blog posts as they are only intended to represent a view at a particular point in time. Please be warned that the information here may be out of date.

Remember “Fit at 40” – my quest to lose three stone and run three races of 10km or above before my 40th birthday?  I got there, although a succession of running-related injuries put pay to some of my good intentions for 2013 but there’s plenty of two-wheeled exercise still taking place (I even broke Rule 42 and completed a sprint triathlon last year).

This year I have three personal challenges planned on the bike that will all stretch me in different ways:

  1. In June, I’m joining a group of friends who are cycling from London to Paris and raising money for Prostate Cancer UK, Cancer Research UK and the British Heart foundation.  That’s about 270km in 3 days.
  2. If that wasn’t challenging enough, in July, I’m joining a team of riders from Fujitsu, cycling from Wakefield to Manchester, including part of the Tour de France route and including the fearsome Holme Moss climb. We call it the Tour de Fujitsu and we’re raising money for Streetbikes CIC.  Frankly, I’m a bit worried about that one. I’ve driven over Holme Moss and remember it being pretty steep and long.
  3. Then, in August, I’m taking part in the Prudential Ride London-Surrey 100, retracing the steps of the 2012 Olympic road race.

Oh yes, and I’m going to be a Tour de France Tourmaker too… but that’s not on my bike!

If anyone would like to support me by sponsoring either the London-Paris or Tour de Fujitsu rides, it would be very much appreciated. You can also follow my training progress on Strava.

Publishing to GitHub from Visual Studio

Posted on By Mark Wilson
This content is 11 years old. I don't routinely update old blog posts as they are only intended to represent a view at a particular point in time. Please be warned that the information here may be out of date.

Those who follow me on Twitter (@markwilsonit) may be aware that I’m attempting to learn some C# skills in my spare time (what little of that I have).  It’s not my first foray into coding – I have a Computer Studies degree and, in my youth I wrote code in a variety of languages (BASIC, C, Turbo Pascal, Modula-2, 68000 assembler, COBOL, Visual Basic, C++ and probably some others too) but aside from a little bit of C++ on the Arduino and the odd bit of PowerShell, I haven’t done much in the last 20 years.  As my career moves further towards management I’m increasingly convinced it’s technology I enjoy though – and I’m seriously considering a move from infrastructure to software…

Anyway…

I’ve been following a Pluralsight C# course from Scott Allen (@ode2code) and, part way through, Scott uses System.Speech to demonstrate adding references to assemblies.  I had a play, adapting something I’d written earlier to talk to me as well as output to the console – nothing grand – just a bit of fun.  After showing it to my sons, the eldest (who is 9), described it as “epic” (which I understand is pretty good) and I tweeted, only to be amused by a reply which suggested the same library had caused hilarity in Duncan Smart (@DuncanSmart)’s household:

Small world, eh!

I followed the link to Duncan’s code and thought “Hmm… GitHub… I use that for my Arduino code… I wonder if…” – and yes, Visual Studio can publish to GitHub too.  It took some work to suss it out though, so here’s what I did (following advice on StackOverflow)…

  1. In Visual Studio, select File then Add to Source Control (which creates a local Git repository)
  2. On GitHub, create a new reposotory (but don’t initialise it with a README – Visual Studio wants an empty repository.
  3. Copy the HTTPS URI for the new repo, then go back to Visual Studio, open Team Explorer, select Home then Unsynced Commits and enter the GitHub URL before clicking Publish.
  4. You may find that you have to commit the changes locally first, which in my case required creating a local username and supplying an email address.
  5. After committing, the solution should be visible on GitHub.

For reference, I was using Visual Studio Express 2013 for Windows Desktop.

Short takes: text editing; Windows Phone; and recovering deleted images…

Posted on By Mark Wilson
This content is 11 years old. I don't routinely update old blog posts as they are only intended to represent a view at a particular point in time. Please be warned that the information here may be out of date.

More snippets from the life of a geek playing at being a manager in the IT industry…

Replacing text with a carriage return/line feed in Windows Notepad

It’s a long story but I needed to find out how many people are in our department, at a level above me.  My manager is on holiday, so I couldn’t ask him. Instead, I drafted an email to the whole department, expanded the distribution list and then counted the names…

Of course I didn’t quite do it like that… I pasted the list into a text file (which I thought I’d import to Excel as a CSV and then count the number of rows). That didn’t work out (I got 111 columns instead and I lost count shortly after AA, AB, AC, etc.) so I tried replacing the “; ” with line feeds in Notepad. Notepad can’t do that, but Word can… Ctrl+H will open the find and replace dialogue and using ^p as the string for the replacement will insert a new paragraph mark. 111 replacements were made (hence 111 names).

Changing the voicemail number on my Windows Phone

One of the issues with my iPhone is that I can’t change the voicemail number from 901 (O2 – the network my phone thinks it’s on) to 443 (Giffgaff – the MVNO that my account is actually with).  I’ve jailbroken and hacked around with config files but it doesn’t work on iOS 7.0.4.

Thankfully, my Nokia Lumia 625 (running Windows Phone 8) is a little more flexible.  When new, it asked me what the voicemail number I needed was.  In the absence of any information from my service provider (EE), I googled and found information that suggested it was +447953222222. My IT department later suggested I should use +447973100123 and changing it is as simple as hitting the ... in the phone app and entering settings, then changing the voicemail number.  As my messages are still intact, I guess that both numbers actually end up in the same location…

Turning off Twitter’s lock screen updates on Windows Phone

Talking of Windows Phone, when I installed the Twitter app it asked if I wanted to see selected tweets on my lock screen.  It seemed like a good idea at first, until I realised I couldn’t actually click on them.  Turning off the Twitter lock screen updates was difficult to hunt down – it’s not set via the Twitter app settings but in the lock screen settings, as Jamie Thomson (@jamiet) and Craig Hawker (@craighawker) highlighted to me.  Thanks guys.

Recovering deleted images from a camera flash drive

Of course, any of us who work in IT know that we automatically get to provide a family IT support service.  I shouldn’t complain because, after my parents in-law paid for someone to do some work on their PC I was horrified to see that he had removed Microsoft Security Essentials and added AVG (which I had removed because it kept nagging to upgrade to a paid version), installed a load of unnecessary software (Defraggler, Firefox, etc.).  My “keep it simple, stupid” approach to septuagenarian IT had been destroyed by someone who wanted to inflict his way of computing on others.

Anyway, back to the point…

…My Mother in-law was disappointed to find she was missing some images on her digital camera.  She swears the camera did it by itself (I suggest it was user error) but, critically, no new pictures had been taken since.  Following advice from PC Advisor, I used a free application called Recuva to restore the deleted files on the memory card (ironically, from the same software company that creates Defraggler, the tool I said was unnecessary a little earlier).  It was beautifully simple, although I was unable to get Windows to recognise the camera as a drive (it does depend on the camera) and had to mess around with card readers instead.

Stuck door lock on Mk5 Volkswagen Golf

Posted on By Mark Wilson
This content is 11 years old. I don't routinely update old blog posts as they are only intended to represent a view at a particular point in time. Please be warned that the information here may be out of date.

My wife’s ’08 registration Volkswagen Golf (Mk5) refused to unlock the rear passenger door earlier this week.  After trying various combinations of keyfobs, inside door locks, locking and unlocking, I hit the ‘net and found a multitude of articles with various bits of advice – usually resulting in trying to remove door trims and expensive garage bills.  I did find one post on the Mk5 Golf GTI forum that looked hopeful though (even though ours is a Match not a GTI!) and decided it was worth a try:

“You do need two people.

One operates the central lock switch on the drivers door while the other tries to open the passenger door. You try and open the door at the same time as it tries to unlock.

Can take ages.”

I tried this a few times with the outside handle (I could hear the lock “clunking” but the door still wouldn’t open) then I tried with the inside handle a couple of times and – hey presto, an open door!  I think the mechanism could do with some grease (not sure what sort), but seems to be working at the moment.

Internet search saves Mark a couple of hundred quid at the local Volkswagen dealer…

Fighting with formulae in Excel

Posted on By Mark Wilson
This content is 11 years old. I don't routinely update old blog posts as they are only intended to represent a view at a particular point in time. Please be warned that the information here may be out of date.

Excel. Love it, or hate it, there are few of us in white collar jobs who don’t have to grapple with it from time to time and, earlier this evening, after OneNote had yet again decided that syncing shared notebooks via our ageing SharePoint platform as not going to work, I needed to come up with a new way of storing actions and decisions for the management team I’m one member of.

I would have liked to create a SharePoint task list but I don’t have the necessary permissions, or the inclination to find out who can give me them (and then to justify why I need to do this). Which led me back to the tool upon which so many business processes are built: Excel.

On the way, I grappled with some formulae, so I decided to knock up some notes for the next time this happens – or the next person who needs to do something similar…

Nested IFs, SEARCHes and ISERRORs

My Excel workbook is simple: two tabs (“Decisions” and “Actions”).  The Decisions tab looks after itself: three columns with a date, a decision, and the rationale behind it.  Actions is a little more complex (read over-engineered).  I have date, status, action, owner, target date, and notes. Then I want the status to be auto-populated based on the notes (If the notes say complete, then status is “Completed”; if the notes include the word cancelled then the status is “Cancelled”, otherwise the status is “Open”).

This was the first challenge.  Excel doesn’t have an ELSE in its IF function, but you can nest IFs as follows:

IF(logical_test, [value_if_true], (IF(logical_test, [value_if_true], [value_if_false]))

(or use one of the LOOKUP functions).  I combined this with the SEARCH function to look for instances of the word “Cancelled” in the notes and everything was working well with results returned for a match on “Complete”, or an instance of “Cancelled” but then the catch all value if false always returned a #VALUE! error.

As SEARCH will always return #VALUE! if the search string is not present, I started to look for workarounds.  Some googling told me that I need to test for an error condition with the ISERROR function but that didn’t seem to solve the issue. So I reversed the logic – check for the error first andthen take action accordingly:

=IF(F2="Complete", "Completed", IF(ISERROR(SEARCH("Cancelled",F2))=TRUE,"Open", IF(SEARCH("Cancelled",F2), "Cancelled")))

It may be easier to understand this in some form of pseudocode:

IF cellvalue="Complete"
{
status = "Completed"
}
ELSE
{
IF cellvalue doesn't contain "Cancelled"
{
status = "Open"
}
ELSE
{
IF cellvalue contains "Cancelled"
{
status = "Cancelled"
}
}
}

Conditional formatting based on cell contents

The next thing I wanted to do was to format each item in the action list according to it’s status and/or date. I wanted to colour completed actions grey; mark cancelled actions as grey and strikethrough the font, then add a red/amber/green status on open actions according to the target date. Conditional formatting did the trick here but I wasn’t sure how to highlight a row based on the contents of one cell in the row.

I found the answer on the HowToGeek site and key to this is not worrying that the reference to the cell used to test the logic is a single cell – Excel is smart enough to work out that you need the same column on the next row when evaluating the condition there.

So, I ended up with six rules, three of which use the TODAY function to evaluate whether the task is overdue or not (or due today):

Formula Format
=$B2="Cancelled" AaBbCcYyZz
=$B2="Completed" AaBbCcYyZz
=$E2="" No format set
=$E2<TODAY() AaBbCcYyZz
=$E2=TODAY() AaBbCcYyZz
=$E2>TODAY() AaBbCcYyZz

It may all be a bit OTT, but I for one work in a very visual manner – I need documents to show me something, not just rely on reading the text.  Hopefully this will work out to be a useful tool for us…