19 May, 2017

Use Excel to parse text to hours

I use a tool that displays elapsed time in the format of Xd Xh Xm, for days-hours-and minutes. If it's less than 1 day, then the output is Xh Xm. It's easy for me to scrape the output, but I need it in hours to manipulate it further.

Example outputs and converted results:

• 2d 0h 18m = 48.3 (in hrs)
• 14h 9m = 14.15 (in hrs)

I wrote an Excel function to accomplish this task.

Assuming my output is in cell B3, this is the function.

=IF(ISERROR(FIND("d",B3)),LEFT(B3,FIND("h",B3)-1)+(MID(B3,FIND(" ",B3,FIND("h",B3))+1,FIND("m",B3)-FIND(" ",B3,FIND("h",B3))-1)/60),(LEFT(B3,FIND("d",B3)-1)*24)+MID(B3,FIND(" ",B3,FIND("d",B3))+1,FIND("h",B3)-FIND(" ",B3,FIND("d",B3))-1)+(MID(B3,FIND(" ",B3,FIND("h",B3))+1,FIND("m",B3)-FIND(" ",B3,FIND("h",B3))-1)/60))

Notice it starts with an IF statement. The IF statement with ISERROR checks to see if the text being parsed begins with 'd' for days or 'h' for hours. Once this is resolved, there are just a couple simple things to do.

• FIND is used to locate a specific letter in the string.
• For the first value, use LEFT to parse, and get the value up to the letter 'd' (or 'h' when 'd' isn't present). LEFT needs to know which cell you're checking (B3), and how many characters to grab from the beginning. FIND helped us determine how many characters in until we reached 'd' so we know we need to go when character less.
LEFT(B3,FIND("d",B3)-1)
• MID is used to get subsequent values. MID requires the cell reference (B3),  where to start, and the number of characters. We want to start after the prior letter, i.e. if we're searching for the minute (m) value, we'd start after hour (h) + 1.  We add an additional 1 to compensate for the space after the prior letter (h).
The number of characters is easy, it's the location of the designators letter (e.g. 'm') - the start location - 1. Remember the designator letter (m) is one more character than the actual number, hence we have to subtract 1 as part of our formula.
MID(B3,FIND(" ",B3,FIND("h",B3))+1,FIND("m",B3)-FIND(" ",B3,FIND("h",B3))-1
• We can't forget about converting our days and minutes to hours. So when we parse the value for days, we multiple by 24, and for minutes, we divided by 60.
(LEFT(B3,FIND("d",B3)-1)*24)
(MID(B3,FIND(" ",B3,FIND("h",B3))+1,FIND("m",B3)-FIND(" ",B3,FIND("h",B3))-1)/60
• Remember --
• You need to add them together.
• You need 2 versions: 1 for the condition where the day (d) is included and 1 when the day (d) isn't included.
TIP: Whenever I build complex functions like this, I first build smaller pieces and then combine them together.

10 February, 2017

Is your Mac shutting down after it goes to sleep?

This eluded me for some time! Usually it didn't go all the way through the shut down, as there were files that needed saving. Either way, it was a pain getting going after my Mac was asleep.

This is how I solved it.

1. Go to System Preferences... > Security & Privacy
2. Select the padlock in the lower-left.
A user name/password dialog box will open. If you don't have the correct privileges for your Mac, you may be stuck at this point.
The padlock icon will change to an open padlock; The Advanced... button will no longer be grayed out/unaccessible. (See the image below.)
5. Uncheck the box proceeding "Log out after _XX_ minutes of inactivity."
6. Select OK
7. Close the Security & Privacy preferences dialog box.

That did solve the issue for me.

I've heard from others that had similar issues, and what finally solved it for them was to make sure their hard disks didn't go to sleep (found in the Energy Saver preferences).

25 January, 2017

UPDATED: May 19, 2017
I changed the script to be more user friendly and the ability to include HTML in the email body. I also found that Google moved a few things in the menus, so I modified the instructions as appropriate.

Google Sheets has a nice feature to notify yourself if something has changed, but every person who wants to be notified must add a notification for themselves. With a simple script, you can send notifications to people of your choosing.

In this example, I've written a script to send an email when 1 or more rows are added.

Get started.

1. Go to the sheet
2. Find a cell in the sheet that wont ever be over-written.
1. Enter the value of the current total number of rows in the sheet.
2. Note the cell for use in your script. I used G2.
3. Go to Tools > Script editor...

Add this script in the script editor. Change as required to meet your needs.

function check4NewLines() {

// /////////////// //////////////// ////////////////
// /////////////// Cell to track last row //////////////// //
// /////////////// //////////////// ////////////////

var theLastRow = "G2";
// Remember to put the number of rows, i.e. last row, in your spreadsheet into the cell you've referenced here.

// /////////////// //////////////// ////////////////
// /////////// End cell to track last row /////////////// //
// /////////////// //////////////// ////////////////

// set the first sheet as active
// fetch this sheet
// fetch sheet name
// figure out what the last row is
var lastRow = sheet.getLastRow();
// cell used to track last row we checked. See the Get started section in this post.
var lastRowChecked = sheet.getRange(theLastRow).getValue();
// The number of new lines on the sheet.
var newLines = lastRow - lastRowChecked;

// /////////////// //////////////// ////////////////
// /////////////// Customized variables //////////////// //
// /////////////// //////////////// ////////////////

// These variables that can be used in your subject or message:
// 1. newLines : the number of new rows added to the sheet
// 2. theSheetURL : the URL to the Google sheet
// 3. sheetName : the name of the Google sheet

// Update with appropriate email address(es). Multiple addresses should be comma delimited.

// Set email subject
var theSubject = "My email subject";

// The URL specific to your Google Sheet. This variable can be used in the email body.

// The email message. The message must be quoted. If using HTML, be sure to only use single quotes in your HTML.
var theEmailHtmlBody = "There is " + newLines + " or more new entries in the " + sheetName + " Google sheet."

// /////////////// //////////////// ////////////////
// ///////////// End customized variables /////////////// //
// /////////////// //////////////// ////////////////

if (newLines > 0) {
// This is the message that will be emailed.

// 'newLines is
MailApp.sendEmail({
to: theEmailDistro ,
subject: theSubject ,
htmlBody: theEmailHtmlBody

});

// set new 'last row'
sheet.getRange(theLastRow).setValue(lastRow);
}
}

Go to the menu and select File > Save.

Testing is easy.
2. Either change the value in the row tracking cell to lower than the number of rows OR add a new row.
3. Run the script.
1. Click the 'play' button -- find it left of the 'bug' button.
OR
2. Click 'Run' from the menu and select the name of your function.

Deploying.

1. Clean up from your test.
1. Make sure your last row number (in G2 in my example) is correct.
2. Make sure the notification emails are correct.
3. Save the script again if you changed anything, such as the email address(es).
2. Select Edit > Current project triggers from the menu.
3. Select from the trigger options. For example you can have it check on the hour or once a day.
Need more help? Search Google of course.
Good luck!

29 June, 2016

Front loaded text and scanning the page

We push our writers to always front load their titles and paragraphs not just because we think it's a good idea, but because studies back up the technique.

Jakob Nielsen posted a great article, "First 2 Words: A Signal for the Scanning Eye," where he goes into the details of his research in this area.

Jakob starts with reminding us how our customers have many lists to read, err scan -- it's not just search results.

• Search result pages
• List of current and/or archived articles and press releases
• Product listing
• Question lists on an FAQ page
• Bulleted and numbered lists, checklists, etc

11 characters is used as the baseline measure the number of characters a user actually reads when looking through a list (link text). The test is to see just the first 11 characters, and see if you can predict what's behind the link -- what will you get when you click.

The best link text has these characteristics.

• Plain language
• Specific terminology

The first 3 have something in common -- no slang or internal jargon.

• Generic words
• Having the information-carrying text at the end

Jakob concludes with a reminder that our customers do have the option to read past the first 11 characters. What we need to remember is to front-load our titles and links, so the customer, when necessary will read the rest of it, i.e. it needs to grab their attention to read enough to validate it's the correct link (which would be followed by a click). "Nanocontent (first bit of a link) just needs to be good enough that users will sniff the most promising links in full." How?

• Provide enough additional context, so the user knows what to expect (and make sure the clicked-to page actually delivers).
• Clearly differentiate links -- don't make me choose between 2 very similar links.

It's all so simple. Right?

28 June, 2016

Improve your writing for the web with the Hemingway Editor

As we know, readers on the web don't really read, they scan and skim articles, and won't ready anything that's long form. With the Hemingway Editor, you can improve your writing by making it more "bold and clear" -- make your writing standout so your audience actually reads it.

The Hemingway Editor is going to give you feedback and input on the following:

• Sentences that are too hard to read
• Simpler alternatives to words and phrases
• Use of passive voice

The Hemingway Editor also has other helpful functions:
• Basic formatting
• Import from Word
• Export as HTML
• Character count
It's free to use online, ore a mere \$10 for the app version.

27 June, 2016

Text (SMS) from your computer using MightyText

It's 2016 -- if you're not texting, you've fallen behind. I text my family, I get appointment reminders via text, and text is being used for 2-factor authentication.

At my company there are several different communications apps, from Yahoo Messenger, to Slack, to HipChat. They each work well, but only texting is guaranteed to reach the person I want to talk to.

With that being said, texting can be difficult from my phone if I have multiple conversations going on, or I have a lot to share. Fortunately, I've found a great solution. Whenever I'm at a computer, PC and Mac (I have both), I can use MightyText instead.

MightyText works with Android phones and Google Chrome browser. Not only can I type faster using MightText, I get notices from my phone on my computer screen. I no longer need to take my phone out of my pocket to see alerts or respond to text messages.

It also as the added benefit that I can easily attach any image that I can access from my computer. There's also an integration with Gmail, if you want to work within the Gmail interface (over having its own tab).

MightyText is on my short-list of must have apps.

Favorite Podcasts

It's been a year since I've posted, and only once in 2015... how time flies! I was sharing with a friend my favorite podcasts, and thought I could share here.

• Daily Tech News Show (DTNS) -- I've been following the host, Tom Merritt for 10 years. He's excellent. If you only have time for 1 podcast, this is the one.
• Serial -- Spin-off from This American Life (below). There have been 2 seasons. Each is a story. I would highly recommend listening to both seasons.
• This American Life -- Big variety; some better than others. The host Ira Glass is excellent.
• TWiT -- This is a network of Podcasts. The quality varies. The founder, Leo Leporte should be recognized as the person who pushed podcasts to be more mainstream. I've been to their studios in Petaluma, CA. Try these:
• This Week in Tech. This was the original show that started the network.
• Tech News Today. Started by Tom Merritt (DTNS).
• The Tech Guy. Syndication of Leo's radio show.
• Security Now. Deep technical -- if you like this stuff, the host Steve Gibson is the best.
• Triangulation. Interviews with current experts in the tech field.
• there are several more, too.
• The Vertical Podcast with Woj. If you like basketball, Woj is highly respected NBA writer. Not the best radio voice, but well worth the time.
[** I currently work for Yahoo, where Adrian Wojnarowski is also employed, though our jobs are unrelated.]

22 May, 2015

When you need two VLOOKUPS together

VLOOKUP is a quick and easy way to return a value from a bunch of rows, by matching a single value. But once you need to match two values, all of sudden things become a bit more difficult.

One solution is to concatenate the two cells in the source and lookup tables. Another is to leverage INDEX and MATCH.
`=INDEX(E2:E1000,MATCH(1,(C:C=A2)*(D:D=B2),0),0)`
When entering this formual, because it's an array formula, you must enter it by using CTRL-SHFT-Enter, instead of just Enter.

Here's how this works:
• The MATCH syntax is
`MATCH(lookup_value, lookup_array, [match_type])`
where match type is 0 = exact, 1 is less than, and -1 is greater than.When the match occurs, it will return the relative position of the match within the range.
• In our MATCH formula, it's looking for an exact match to 1 (lookup_value = 1).
• If the value of A2 is found in column C, a value of 1 is returned, otherwise a 0 is returned.
• Likewise for the value of B2 in column D.
• When multiplied together, if a matching value in columns C and D on the same row match the values from A2 and B2, then a 1 is returned, else we get a 0 (lookup_array).
• The INDEX syntax is
`INDEX(array, row_num, [column_num])`
•  When MATCH returns a value of the matching row, we get the value in column E for the corresponding row.
With this formula, if you needed to add a 3rd or even more cell matches, you could just extend the multiplication technique.

09 August, 2014

Columns side-by-side or stacked -- CSS to adjust for screen width

I recently had to brush up on my CSS skills to develop a solution where two buttons would appear side-by-side unless the view port was less than 480 pixels, in which case they should then display stacked. Through a bit of research I found a couple of solutions. When I tried to implement the solutions as presented, the buttons didn't appear side-by-side, but instead on separate rows -- I'm sure something I must of overlooked, but nevertheless I therefore came up with my own modified solution.

My particular example used 3 columns -- the middle just for space, which could be done with padding and/or margin. The key is using "@media." By default the widths of the columns were all set for 100% -- configured for stacking. When the screen was 480 pixels or larger, using "@media" the widths are adjusted 45%, 10%, and 45%. If you're not sure what each of these CSS properties do for you, try searching "css [property]" such as "css clear" and click on the link for w3schools -- usually the first or second response.

This should be enough to get you going. Depending on your use you may need to do further work, so when you;re on the 480+ pixel screen, you're not consuming all available width.

Class definitions

.mySection {
clear: both;
margin: 0px;
zoom:1; /* For IE 6/7 */
}

.mySection:before {
content:"";
display:table;
}
.mySection:after {
content:"";
display:table;
clear:both;
}

.myGroup {
display: block;
}

.grid_1of3, .grid_2of3, .grid_3of3 {
width:100%;
}

.grid_2of3 {
height:10px;
}

@media only screen and (min-width: 480px) {
.myGroup {
display: inline;
}

.grid_1of3,
.grid_3of3 {
width:45%;
}

.grid_2of3 {
width:10%;
}
}

HTML code

``````<div class="mySection" style="text-align:center;">
<div class="myGroup grid_1of3"><img src="first_btn.png" border="0" /></div>
<div class="myGroup grid_2of3"> </div>
<div class="myGroup grid_3of3">< img src="second_btn.png" border="0" /></div>
</div>
``````

16 June, 2014

Yes, you heard that right! If you're a Comcast customer and you rent a cable internet modem from them, then it has a separate wireless channel that other Comcast customers can use. This means as a Comcast customer, you can surf the Internet from any neighborhood where Comcast has an install base.

While in theory I like the flexibility as a Comcast customer to get WiFi anywhere (Xfinity WiFi Hotspots), in practice I'm not too pleased.

• I pay Comcast to have their Internet service, which enables this capability.
• I pay another \$8 to rent their modem, which is also required for this capability.
• I was never asked; my account was opted-in.
• If I have an issue with my connection, I have to hold a long time (typically), and if something is wrong at my end, I have to pay for them to correct, yet again Comcast benefits from my service working.
• If you live in an apartment complex, Comcast advertises your apartment complex on the list of available hotspots (mine shows up).
• Unless you have Comcast, if you want more than the guest pass allows (see below), Comcast is making money from this service, yet isn't passing it back to its customers that make it possible.
• It has the potential to slow down connectivity for all of us on the same Comcast Headend. Note, it shouldn't slow you down specifically, unless the headend itself is congested. Comcast claims a separate 6 Mhz channel is used.
There may be another benefit beyond flexibility for you. If you have friends and relatives visiting, and they're Comcast customers, they can use for free and not consume your data -- it seems Comcast caps data at 250 GB per month. If your friends or family don't have Comcast, they can still use it as a guess, twice for up to an hour in a 30 day period.

What can we do about it?

• Dump Comcast. I know this can be difficult, because for most of us the only other option is a much slower DSL connection.