h1

Excel: Convert hours and minutes to minutes

August 2, 2017

Converting hours and minutes (hh:mm) to minutes in an Excel spreadsheet is actually quite simple, once you know what to do. But I had to do quite a bit of trial and error and Googling to get it to work.

To convert hours and minutes to minutes, you have to multiple the hh:mm value by 1440 (which is 24 [number of hours in the day] multiplied by 60 [number of minutes in an hour]), AND make sure you set the formatting correctly for the both the hh:mm cells and the resulting minute cells. This is where I got caught — I didn’t have the correct formatting applied to the cells. Once I got that right, it all worked.

Here’s how…

  1. Enter elapsed times in hours and minutes (using the format hh:mm) in Column B. (Yes, those non-stop flights to/from Sydney to Dallas Fort Worth are killers!)
  2. Set the format for this column to Custom > h:mm. (To format the column, select the column header, right-click on it, select Format Cells, select Custom on the Number tab, then select h:mm from the list of types. Click OK.)
  3. Insert a new column (C) and called it Minutes.
  4. Set the format for the cells in this new column to Number with no decimal places — this formatting is critical for the formula to work. (To format the column, select the column header, right-click on it, select Format Cells, select Number on the Number tab, then change the Decimal Places value to 0. Click OK.)
  5. Put the cursor in the first cell in the new column that pairs with a cell in the hh:mm column. In my example, that was C3, which pairs with B3.
  6. Type =B3*1440 in the formula bar, then press Enter to convert the hours and minutes into minutes.
  7. Click in the C3 cell and ‘grab’ the bottom right handle of the cell marker (it turns to a + sign when you’ve grabbed it correctly) and drag it down the other cells in column C. (See below for how to apply it to ALL cells in the column.)

  8. When you release the mouse, all those cells you dragged this formula over will be converted to minutes and seconds.

To apply this formula to the entire column:

  1. Copy (Ctrl+C) the result in the first cell with the formula (C3 in my example).
  2. Select the entire column (column C in my example) by selecting the column header.
  3. Paste (Ctrl+V).
  4. You’ll have to rename the column back to Minutes, but you’ll have that formula now applied to every row of your spreadsheet for that column.

These sites helped me figure out what to do:

See also:

[Links last checked August 2017]

h1

New tribes

July 31, 2017

Foreword written for Southern Communicator, Issue 41, June 2017

**********

New tribes

Tribe: Colloquial (humorous) a family or class of people (Macquarie Dictionary)

In September 2008, after 16 years in the software technical writing world, I segued into editing technical documents. It was just as the global financial crisis was happening, and a time when mining and software companies (my main source of income) were getting rid of contractors to preserve their bottom lines. I lived in a rural area three hours’ drive from Perth (Western Australia), so was fortunate to land a temporary three‐month telecommuting contract with a global oil and gas company—I’m now in my tenth year with them—editing some of their Word documents. Yes, Microsoft Word.

Word is still heavily used by corporates for much of their day‐to‐day documentation and reporting. We can talk all we like about how other software is better for constructing documents, managing the review cycle, formatting, publishing to various outputs, and the like, but the reality is that other software (for example, Framemaker, InDesign, Author‐it, and so on) often remains the domain of specialist tech and marketing communications people, with little inroads into mainstream corporate life.

As I moved into this different, but related, editing world, I was still very much attached to my technical communication ‘tribe’ (I attended and spoke at techcomm conferences until 2014). But eventually I decided it was time to embrace this world I’d inhabited for six years. And what better way to embrace a new tribe than by hanging out with them in the places they frequent. For me, that’s conferences and online.

Dr Google told me about various professional and informal editing organisations, some of which were very specialised (for example, medical editors, thesis editors). I was already a member of the Western Australian editing society, which recently became part of IPEd, the professional group for Australian editors. IPEd holds a two‐day conference every two years, and the state branch holds a one‐day seminar each winter. But, like Oliver Twist, I wanted more.

Professional editing groups in North America and Great Britain were my focus, especially those that held multi‐day annual conferences. But which to choose? Eventually I opted to join the American Copy Editors Society (ACES); ACES’ original focus was newspaper copy editors, but now covers all types of editors. I attended and spoke at my first ACES conference in the US in 2015—I was a total newbie, only knowing one other person attending that conference. This was so different to the techcomm conferences where I knew many people. I’ve since attended and spoken at the 2016 and 2017 ACES conferences, and the circle of those I know in this new tribe is growing wider.

Speaking at a conference is a great way to meet people—it’s amazing how many stop you in the hallway to tell you how much they enjoyed your session, ask you a question, or just strike up a conversation over a drink or a meal because they feel they know you because they’ve heard you speak, or, as an Australian in the US, because they ‘just love your accent’! Speaking is also a terrific icebreaker, and I can’t recommend it enough for those who find themselves very lonely at a conference. Put yourself out there and offer to speak. The ASTC conference is usually held each October, so consider putting in a submission to speak at the next one, or try the TechCommNZ conference, held every two years.

Another tribal behaviour is the chat around the camp fire, or today, around the coffee machine. But how can you informally pick fellow editors’ brains when you work alone and are physically distant from your work colleagues, or don’t have any work colleagues? Join an online group. Despite the time zones, I try to participate in the monthly, hour‐long #ACESChat on Twitter. I also hang out at the Editors Association of Earth (Facebook group), where editors from around the world help each other decipher awkward sentences, solve Word problems, share bloopers, talk about language variations and regionalisms, and so on. And because we’re global, there’s always someone awake somewhere to help with a curly question, or offer moral support.

Tribes—it’s all about finding ‘your people’ and the feeling of belonging when you do. So search, join, attend, participate, speak—hang out with your people because it’s good for you professionally and it’s good for your soul to be with like‐minded people, even if just for a short while.

References:

[Links last checked July 2017]

h1

Tagging photos

July 12, 2017

I’m slowly scanning some of my old photos to convert them into digital format — I’ve started with photos of family and friends, ignoring the scenic ones at this stage. I was very good back in the day — when I received an envelope of photos from the photo processing place, I labelled the backs of almost all my photos with dates (sometimes just month and year, but it’s a start) and names of people and places. I’m very grateful for the organised past me!

I decided to add these notes to the properties (metadata) of each scanned photo in Windows, just to preserve the information. But it’s tedious. It’s easy enough to add metadata for a few photos, but not for hundreds or potentially thousands of them. While you can select multiple photos and apply the same metadata to them, there are always individual photos where the metadata is different (a new person in a photo, for example). And because I didn’t really know what I was doing, I only added data to the title, subject, date taken, and comments fields, not realising that much of this wouldn’t be visible in photo manipulation software or online services such as Flickr, and that adding tags would have been a better strategy. It’s a lot of typing with much potential for typos.

I knew about products such as MP3 Tag for doing mass metadata changes to music files, so went hunting for something similar for photos. There’s very little out there, which surprised me. Most photo software has the ability to add tags etc., but doing so doesn’t write that info back to the file as viewed in the Windows properties, which is what I wanted; yes, I tried several software apps I have on my computer to test this out. Why do I want these tags preserved with the file? Because if I send/share the files with family etc., I want them to be able to view the metadata too, just like they would if they turned over the real photo.

I did find one piece of software that allows you to write your metadata back to the file, AND keeps a list of tags you’ve already used so you don’t have to retype them – just choose from the list as you type the first letter of a name. That’s Adobe Photoshop Elements (I’m using version 13.0). You use the Organizer functions to add the tags, then select all the files in a folder and go to File > Save Metadata to File (or Ctrl+W) to populate the Tag properties in the Windows file. Done!

It may not be the best solution, but it’s one that works for me.

h1

Getting close…

June 9, 2017

My blog stats as at this morning (Friday 9 June 2017). I predict it’ll tip over the 10 million mark by Monday. Wow.

Sunday 11 June 2017, 11:15am. Nearly there:

blog_10m_02.png

Monday 12 June 2017, 9:30am. Almost…:

blog_10m_03.png

As predicted, the counter ticked over to 10 million sometime on Monday 12 June 2017, between 4:30pm and 8:30pm. Stats as at Tuesday 13 June 2017, 6:20am:

blog_10m_04.png

 

h1

How long before this danger causes a problem?

May 31, 2017

In the ‘what were they thinking’ category comes this — replacing those moisture-absorbing gel packets in products with something that has a similar size, shape, and feel to the product!

I purchased a container of glucosamine tablets recently, and when I opened it, I found a blue plastic cap-like thing instead of the gel packet. Tell me it doesn’t look like one of those bullet-like tablets! How could a person with limited dexterity, compromised touch, or vision problems distinguish this blue thing from a tablet? Seniors tend to be those who take glucosamine, and they are more likely to have dexterity, touch, and vision issues as they age, so I’m just waiting for the news headline that says someone was hurt or injured or even died from ingesting one of these blue plastic cap things.

What were they thinking??? Did no-one see that you need to clearly distinguish the product from something that isn’t the product and shouldn’t be ingested? At least the gel packets were a different shape, different feel, and made from quite different materials, enabling them to be ‘not like the others’.

h1

Word: Add padding to a character style

May 29, 2017

My client wanted the button text in the user manual I was writing to look similar to the buttons in the app. For this app, blue, green, and orange background colours were used for the buttons, with white text.

Easy enough to do — just set up three Word character styles, one for each colour, have different coloured shading for each, and bold white text for the font. Make it simple for both writing and future updating by assigning keyboard shortcuts for each style. Done.

But, while my client liked what I’d done, he was concerned that the first and last letter of the button text butted up against the edge of the coloured shading (see image below), and wanted to know if we could add some padding.

I was pretty sure I could do that to a character style using borders the same colour, but then I ran into an issue I’d never seen before. When I applied a border of any weight or colour, I lost the background shading for the text. In the example below, you can see that the area inside the blue border has white space inside it, not blue shading with white text as I expected.

No matter what I did, I couldn’t get it to work. I explained the situation to my client. Fortunately, he’s a programmer and knows a bug when he sees one :-) He did a little bit of experimenting and came up with the solution, which was to reapply the background shading to the character style AFTER adding the border.

It worked, and here’s the end result (it has a 1.5 pt border, just enough to add a bit of padding to both ends of the text, but not too much that the top and bottom padding adds too much gap between lines in a paragraph):

h1

Word: Copy AutoCorrect entries to another computer

May 3, 2017

Did you know you can copy your AutoCorrect entries from one computer to another? You might want to share yours with a work colleague, or you might have a new computer and not want to set them all up again.

Beware: Copying these files to another computer WILL overwrite the AutoCorrect files in the destination computer, so if you’re copying them to a colleague’s computer, make sure they have listed their own AutoCorrect entries first (see https://cybertext.wordpress.com/2017/05/03/word-list-keyboard-shortcuts-autotext-and-autocorrect-entries/).

Notes:

Your AutoCorrect entries apply to all programs in the Microsoft Office suite, and are stored in *.ACL files under your user profile on your computer. When you copy them, you’ll put them in the same place but under the other user’s profile on their computer.

  1. On your computer, go to: C:\Users\[your_user_name]\AppData\Roaming\Microsoft\Office.
  2. You’ll see a suite of ACL files listed. The MSO numbers in each file name indicate the language/locale; e.g. MSO0127.acl = Math, MSO1033.acl = English (US), MSO2057.acl = English, (UK), MSO3081.acl = English (Australia). (For a full list of locale numbers, see: http://support.microsoft.com/en-us/help/221435/list-of-supported-locale-identifiers-in-word.) Hint: Look at the date last modified — the ACL files with the most recent dates are likely the ones your installation of Office uses.
  3. Copy the ACL files you need (or copy them all if you’re not sure and they’re going to a new computer).
  4. On the destination computer, go to: C:\Users\[user_name_of_other person]\AppData\Roaming\Microsoft\Office.
  5. Paste the copied files into this folder, saying yes to overwrite the existing files.

[Links last checked May 2017]