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.
  3. Enter your user name and password.
    The padlock icon will change to an open padlock; The Advanced... button will no longer be grayed out/unaccessible. (See the image below.)
  4. Select the Advanced... button.
  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

Simple notification script for Google Sheets

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...

Write your script.

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 /////////////// //
      // /////////////// //////////////// ////////////////


  // get the spreadsheet object
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  // set the first sheet as active
  SpreadsheetApp.setActiveSheet(spreadsheet.getSheets()[0]);
  // fetch this sheet
  var sheet = spreadsheet.getActiveSheet();
  // fetch sheet name
  var sheetName = spreadsheet.getName();
  // 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.
  var theEmailDistro = "my-email-address@foo.com";

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

    // The URL specific to your Google Sheet. This variable can be used in the email body.
  var theSheetURL = "https://docs.google.com/spreadsheets/my-super-sheet";

    // 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.


Test your script.

Testing is easy.
  1. Make sure your email address is one of the notification email addresses.
  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.
  4. Wait for your email.

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!