Excel: Filter XML Using XPATH

Excel plays really nicely with XML (as it ought to, because XML is super cool). You might be pleased to learn that you can use XPATH expressions with a handy function called FILTERXML. It works fine for XML dumped straight into a cell, or you can apply it to a WEBSERVICE call. The function has only two arguments: the first is your XML and the second is your XPATH expression.

Try this very contrived example I’ve concocted to see it in action. Here’s a bit of XML you can paste into a fresh worksheet in cell A1:

<item>apple

banana</item>peach</example>

Now enter this formula in any other cell, where I’m using XPATH to pull out the second item:

=FILTERXML(A1,"//item[2]")

That’ll give you banana!

Read More

SQL Server: Convert Date to YYYY/MM/DD Format

The default format for datetime fields that a SQL Server query returns is fine for all kinds of applications, but if you’re a fan of the YYYY/MM/DD format like me then here’s how you can accomplish it using the CONVERT function:

CONVERT(VARCHAR(11),YourDateField, 111)

Without this function: 2015-10-19 23:00:00.000
With this function: 2015/10/19

Read More

SQL Server: Current Date Function

Need the current date in your SQL statement? The GETDATE() function is what you're looking for. For example, if you're writing a SELECT statement and you'd like to only return those records in which the field EventDate is in the past, you can write:

WHERE EventDate < GETDATE()

Read More

Surviving Online University

Doing a university degree online isn't easy, but I've found that there are some things that you can do to improve the experience overall. Here are some tips for getting the most out of your online studies, based on the time that I spent earning my undergraduate degree in computing.

Make a weekly schedule and stick to it.

It's great having flexibility in terms of when you want to study, but set yourself some goals to accomplish each week. If your school doesn't have specific deadlines for assignments, set some for yourself and stick to them as though they weren't arbitrary. This will help you to stay on track so that you don't suddenly find yourself with a ton of work to do right at the end.

Take notes (yes - on paper)!

This might sound old-fashioned or just plain crazy, but trust me when I say that there is something to be gained when you write out notes on a piece of paper. Even if you don't plan on reading them again, you will find it easier to remember what you're learning if you write it down. Scientific American has an interesting article that explains the science behind this..

Take advantage of message boards and online student communities.

Just because you're studying over the Internet doesn't mean that you have to be isolated. Look for fellow students through your school's web site and through other social media channels. It's great having support from other people, even if it's just to commiserate with them while you're gearing up for a stressful exam. Some schools even organize in-person events in different cities where students can meet.

Change up the scenery a bit.

Studying in your pyjamas at home can be super cozy but I recommend heading out and studying in some different places every now and then. There can be lots of distractions at home and you just might find it easier to get into the right frame of mind for working if you go somewhere like a public library. I used to enjoy studying at local university campuses for this reason.

Don't be afraid to get external help.

One thing that non-online students might take for granted is the amount of assistance that you can get from fellow students. Sometimes it can be tricky studying alone and you need someone to talk to about a particular subject. Whether it's chatting with a friend about some readings or hiring a tutor to get help with some difficult homework problems, I suggest seeking out other people for help when you need it.

Keep track of your progress.

Sometimes graduation can feel like an unreachable goal, especially if you choose to study on a part-time basis while you work a full-time job. I found it really satisfying to keep a list of all of the courses I was going to take and to cross them off as I finished them. Marking that last course as complete after I wrote my last exam was an amazing feeling.

Talk about your experience during job interviews.

An interesting part about studying online is that it demonstrates a certain set of skills that are attractive to employers. While you might be battling the stigma that is sometimes associated with pursuing a degree through distance education, tell interviewers about how you've managed to balance work from multiple courses without having anyone push you along in-person. Talk about how you're able to effectively manage your time and succeed in your studies despite having distractions at home. Finally, explain to them your success at proctored examinations. This helps to clear up the common misconception that online students do absolutely everything online and that their skills are never truly tested.

And there you have it! As always, feel free to leave a comment here or send me an e-mail if you have any questions.

Read More

Excel: Generate a random integer within a given range in VBA

The other day I needed to write a bit of VBA code that involved the random selection of an integer between two numbers. Let's say you want to generate a random integer between 500 and 800. Here's how to make that happen with VBA, storing it in a variable I'm calling randomNumber:

Dim randomNumber As Integer
Randomize
randomNumber = Int((800-500+1) * Rnd + 500)

Read More

Excel: Use the Fill-Handle to Quickly Fill Down

Did you know that you can double-click the fill-handle in Microsoft Excel to quickly fill down cells? For example, if you enter a formula in a cell, you can double click on the fill-handle (the tiny square in the lower right-hand corner of the cell) to automatically fill your formula down to the rest of the cells in that region.

Read More