Tuesday, 17 October 2017

Conditionally format rows in Excel depending on date range

Sometimes I get asked Excel questions. This is one of those times!

Given a spreadsheet with rows that contain a start and finish date, format the rows in the past, present and future in different colours, so that it looks like this:

Spreadsheet showing rows with different colours

I initially tried using a formula with Excel’s Conditional Formatting feature. Despite this being the recommended solution in some search results, I just found it set the one format for all the rows – not what I wanted. This post by Joseph D’Emanuele put me on the right track.

Here’s what I ended up doing:

  1. Add a new column next to your existing data
    Spreadsheet with extra Status column added
  2. In the first cell of this new column, insert the formula
    =IF(B2 < TODAY(), "Past", IF(A2 > TODAY(), "Future", "Current"))
  3. Copy this formula down to the remaining rows. eg.
    Spreadsheet with Status column populated
  4. Now select all the rows you want to format (for me that’s A2:D5)
  5. On the Home menu tab, select Conditional Formatting, then Manage Rules
  6. You will add 3 rules – one for each status.
  7. Click New Rule
  8. Select Use a formula to determine which cells to format
  9. In Format values where this formula is true, enter =INDIRECT("D" &ROW()) = "Future"
  10. Click Format and choose the desired formatting to apply for Future dates
  11. Click OK and repeat adding new rules for “Current” and “Past”
  12. You should end up with something like this:
    Excel's Conditional Formatting Rules Manager dialog box
  13. Click OK and you should have rows formatted different colours depending on whether the start/finish date is in the past, current or in the future!
  14. You can optionally choose to hide the Status row if you’d rather not see it all the time.

Note that the formula in the conditional formatting (=INDIRECT("D" &ROW()) = "Future") is hard-coded to the column – “D” in my case. If you move data around, you’ll need to update this to refer to the new column letter.

Friday, 1 September 2017

On the NBN

We switched over to the NBN at home a couple of months ago. Unfortunately since the last change of Government in Australia, it appears that all NBN rollouts are getting fibre to the node (FTTN) rather than original plan of fibre to the premises (FTTP). So with the node servicing our neighbourhood being a fair distance from our house, it looks like the best we can expect for the foreseeable future is ~40MB. That is a 4x increase on what we used to get with ADSL2+, but it is a shame we can’t get the full 100MB if we wanted to pay for it.

40MB isn’t too bad – I know of others that are only getting 20 (and there’s stories of some that switch over from ADSL to get a slower speed than what they used to have). Just seems a lost opportunity that if everyone had got fibre, I’m sure that would be capable of being upgraded in the future to even beyond 100MB.

Wednesday, 30 August 2017

ShaggyMax screen protectors

My old laptop ended up getting a really scratched display. The keyboard obviously had been rubbing against the glass for a while, which was a shame. I think this was made worse because the only thing separating them was two rubber feet that had come loose after a few years.

My new laptop has a different design with a small rubber strip that goes all the way around the edge, which seems to do a better job. Nevertheless, I did want to get something that would help protect the display and reduce the chance of scratches.

A bit of Googling came up with a few options. A call out to Twitter ended up with an in-person response from Ryan showing me he’d got a ShaggyMax-brand protector for his brand new 9560 (the newer version of my 9550).

That was good enough for me, so I’ve now got my own!

I bought a 15.4” Laptop Screen Protector. You can buy them through Amazon, or direct from their website. Here’s what it looks like on my laptop:


Laptop with screen protector covering keyboard

It’s a microfibre cloth that is thin enough that it doesn’t interfere with closing the lid, but hopefully will be thick enough to softly cushion and protect the glass. So far, so good.

Saturday, 26 August 2017

NDC Sydney 2017

Delegate badgeMy second time attending NDC Sydney, and this was extra special as I’d been accepted as a speaker. I was excited and pretty nervous!

I flew over Tuesday evening. It was quite windy with a fair bit of turbulence on the flight. A decent tailwind meant even though we left Adelaide late, we still got into Sydney on time.


Wednesday

Jennifer MarsmanThe conference kicked off with a keynote from Jennifer Marsman talking about using EEG and Machine Learning to perform lie detection (on her husband!). Not sure about the lie detection, but I do like the application of EEG to enable things like controlling a wheelchair, and it was useful to learn about the different kinds of algorithms and models you used for different types of problems.

Barry DorransBarry Dorrans walked through a bunch of security vulnerabilities that had been discovered in .NET. Some subtle causes and a few things to watch out for in your own code.

Edith HarbaughEdith Harbaugh gave a good overview of using feature flags. She is from Launch Darkly, a company that provides ‘feature flags as a service’, though her talk was more general. One point she made was that feature flags can be dynamic – something that can be changed at runtime, not just a config setting that is set at startup.

Damian EdwardsDamian Edwards raced through what’s new in ASP.NET Core. This is looking pretty good.

Bart De SmetBart De Smet is a guru of .NET and CLR internals. I've watched some of his Pluralsight courses already. This talk on C# internals was fascinating.

20170816_062053378_iOSFilip Ekberg dug into asynchronous programming.

20170816_074407961_iOSRounding out the day with Jimmy Bogard doing CI with Azure.
20170816_103047694_iOSWednesday evening there was a cruise on Sydney harbour. It was a little chillier than the last time I’d been out, but nice food and great to socialise with other attendees.

Thursday

20170816_230056563_iOSFirst thing Michele Bustamante stepped through using Docker.


Then it was my talk on using Chocolatey and Boxstarter! All my demos went off without a hitch and there were lots of questions from the audience. I think the early attendees appreciated the bonus chocolate frogs too.
Thanks to Ben for coming along and being a friendly face in the front row (and taking the photo!)

I hung around because Bart De Smet was up after me diving in to a bunch of areas of .NET to watch out for when you really care about performance.

Steve SandersonSteve Sanderson showed off some nice options available now in ASP.NET Core.

Richard CampbellThe one and only Richard Campbell with a history lesson about .NET. Informative and entertaining.

Dina GoldshteinDina Goldshtein on self-monitoring apps. Some interesting ways to do logging and diagnostics.

Finally a bit more Michelle Bustamante with more Docker – this time looking at some of the orchestration tools.

Thursday evening there was a social evening, including a bunch of pretty funny “short stories about failure” from some of the speakers. Some people partied on late into the night, but I retired early.

Friday

Nick BlumhardtNick Blumhardt gave a great talk introducing SeriLog and structured logging.

Kylie HuntKylie Hunt talked about dealing with problem bosses. A mix of good suggestions and sharing personal experiences.
Maybe I’m just an old fuddy-duddy, but personally I’m less impressed by speakers at conferences that need a swear jar.

Laura Bell Laura Bell had some good content on building in security.

Cosmo robot with Lars KlintLars Klint did a really interesting talk about Cosmo – a little programmable robot.
Jimmy PelletierLast talk I caught was from Jimmy Pelletier on how his team introduced micro services into their architecture.

There was the option of attending PubConf after the main conference had finished, but I’d already made plans to catch up with some old Adelaide friends who are now living in Sydney. It was really great to spend Friday evening and Saturday with them before my flight home in the late afternoon.

Here’s a pretty waterfall that I saw on Saturday when we went out for a walk. It's been dry in Sydney recently, so imagine what it would be like after a bit of rain.
Waterfall

All in all, again NDC Sydney didn’t disappoint and I can only hope it continues to grow and get better next year. There were quite a few times where I was spoiled for choice. I'll be looking forward to catching up on missed sessions when the recordings are published in a few weeks. It was great to see the Adelaide contingent growing from 2 last year to 9 (that I know of). The word is getting out that this is THE developer conference you should attend!

Saturday, 24 June 2017

Running SQL Server Configuration Manager without SQL Server installed

I like to have SQL Server Management Studio (SSMS) installed as part of my standard development environment. Usually this is because I also have an instance of SQL Server Developer or Express Edition installed, but not always.

One thing I noticed with the separate distribution of SSMS is that it also includes all the other management tools that used to be bundled with the server, like SQL Server Configuration Manager.

I wanted to change the SQL Client configuration settings and Configuration Manager is the tool I’d use to do that. But when I fired it up (it’s named SQL Server vNext CTP2.0 Configuration Manager in the latest release of SSMS), I’ve been greeted with this message:

Error dialog from SQL Server Configuration Manager

I don’t see this problem when an instance of SQL Server is installed though.

The fix is documented in KB 956013. I ran the following from an elevated command prompt:

mofcomp "%programfiles(x86)%\Microsoft SQL Server\140\shared\sqlmgmproviderxpsp2up.mof"

Now SQL Server Configuration Manager starts up without error

Oh, and the easiest way to install SSMS is to use Chocolatey!

choco install sql-server-management-studio

Monday, 15 May 2017

TypeScript tsconfig.json being ignored in C# project

TL;DR - Make sure your tsconfig.json file has the Build Action set to Content

I have some TypeScript files included in a .NET project. I added the Microsoft.TypeScript.MSBuild NuGet package to the project to enable the TypeScript compiler to transpile the .ts files to JavaScript. All was good until I wanted to set some compiler options using the tsconfig.json file. I created this file but every time I compiled the project it didn’t do anything different.

I almost pulling out my hair in frustration, so I decided to dig in deeper to find out why this was happening.

First, I ran msbuild with detailed logging. That showed up the following line just before the CompileTypeScript target was run:

Target "PreComputeCompileTypeScriptWithTSConfig" skipped, due to false condition; ('@(ConfigFiles)' != '') was evaluated as ('' != '').

A bit more digging led me to packages\Microsoft.TypeScript.MSBuild.2.3.1\tools\net45\Microsoft.TypeScript.targets – This defines that PreComputeCompileTypeScriptWithTSConfig target which is looking for @(ConfigFiles), which in turn is set in target FindConfigFiles. That target calls the FindConfigFiles task that is in TypeScript.Tasks.dll.

Firing up JetBrains dotPeek to reflect on the code for FindConfigTasks shows that it uses the ContentFiles property to look for existing tsconfig.json files, and that property is set to @(Content) in the targets file.

Jumping back to my project, I notice this:

Screenshot of tsconfig.json and Build Action set to None

Ah haa! Let’s set that Build Action to Content and try again..

Yes! Building the project shows tsc.exe being passed the --project parameter pointing to the tsconfig.json file!

Friday, 5 May 2017

Speaking at NDC Sydney 2017

Well this happened!

Congratulations message

Yes, my talk “Life is like a Box of Chocolate(y)” was accepted for NDC Sydney!

I’ll be speaking about using Chocolatey and BoxStarter as a way of scripting out the configuration of your developer workstation. You might think of these as more “IT” tools, but I want to show developers how easy they are to use.

Combined with Windows 10’s Reset, this means you can rebuild your Windows environment in a matter of minutes, allowing you to apply the “cattle, not pets” approach to your own PC.
NDC Sydney banner

Last year I attended NDC as a volunteer crew member and afterwards said that “this is THE developer event in Australia”.

So go have a look at the speakers and sessions. I reckon it’s a pretty compelling line-up, and if you register before Sunday 14th May, you can take advantage of early bird prices.

I hope to see you there!