Excel DATEVALUE Function

When a Microsoft Excel spread sheet has a column of date data in this format:

Nov 24 1995

Then the Excel function you’ll find useful is DATEVALUE

DATEVALUE function.

This formula here:

=DATEVALUE((TRIM(MID(R2,5,2))&"-"&TRIM(LEFT(R2,3))&"-"&TRIM(RIGHT(R2,4)))

Basically grabs the Day, then the Month, then the Year from the column, then converts it to a serial number that Excel recognises as a date. Then format this to display however you want it to.

Find JIRA Issues with Attachments

When you just know you’ve attached a file to a JIRA issue, but can’t find it, this is the search to do in Issues in JIRA

Replace PKEY with your project key.:

project = PKEY AND type != Epic and attachments is not EMPTY ORDER BY Status ASC

SSH and Private Key usage on Mac OS X

It’s very important that your private keys are kept secure and away from prying eyes.

A good place to keep your private keys is in the folder ~/.ssh as this makes it easier to manage (this is a subfolder of your home folder).

You then want to set the permissions of the private key file to 600.

chmod 600 ~/.ssh/privatekeyfile.pem

Then you can ssh to the server from the command line using:

ssh -i ~/.ssh/privatekeyfile.pem.pem root@fqdn -p 22