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:
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:
- Add a new column next to your existing data
- In the first cell of this new column, insert the formula
=IF(B2 < TODAY(), "Past", IF(A2 > TODAY(), "Future", "Current"))
- Copy this formula down to the remaining rows. eg.
- Now select all the rows you want to format (for me that’s A2:D5)
- On the Home menu tab, select Conditional Formatting, then Manage Rules
- You will add 3 rules – one for each status.
- Click New Rule
- Select Use a formula to determine which cells to format
- In Format values where this formula is true, enter =INDIRECT(“D” &ROW()) = “Future”
- Click Format and choose the desired formatting to apply for Future dates
- Click OK and repeat adding new rules for “Current” and “Past”
- You should end up with something like this:
- 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!
- 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.
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.
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:
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.
My 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.
The 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 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 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 Edwards raced through what’s new in ASP.NET Core. This is looking pretty good.
Bart 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.
Filip Ekberg dug into asynchronous programming.
Rounding out the day with Jimmy Bogard doing CI with Azure.
Wednesday 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.
First thing Michele Bustamante stepped through using Docker.
@DavidRGardiner talks chocolatey at #ndcsydney pic.twitter.com/3fhH2Bj9VH
— Ben Laan (@benlaan) August 17, 2017
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 Sanderson showed off some nice options available now in ASP.NET Core.
The one and only Richard Campbell with a history lesson about .NET. Informative and entertaining.
Dina 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.
Nick Blumhardt gave a great talk introducing SeriLog and structured logging.
Kylie 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 had some good content on building in security.
Lars Klint did a really interesting talk about Cosmo – a little programmable robot.
Last 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.
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!
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:
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