Tag Archives: Tip

Nail Down That Date!

Passing a spreadsheet around between organizations has a hidden problem; one that can easily make trouble. And the trouble comes, not from the spreadsheet, but from the default date setting on the computer.

Excel uses the default date setting to interpret the order of date information. Whether its’ Month-Day-Year or Day-Month-Year, or even Year-Month-Day, that information comes from the OS date settings. These are settings that we don’t often think about once we’ve set them. And typically, they are the same throughout an organization.

But take the spreadsheet you’ve designed, that uses Month-Day-Year into a Day-Month-Year organization, and all sorts of problems crop up.

The first problem is that you might not notice immediately; if July 6, turns into June 7 – that might not jump out at you as a problem. If you are lucky, you’ll spot something weird about the 12th of Month 21 …

So how do you nail down those dates so they can’t shift? One strategy is to break up your date entry into your preferred format, and then rebuild the date using the DATE function.

The syntax for the DATE function is =DATE(year, month, day)

The Date Function
Using the Date function to reassemble a date from separate cells

Here you can see the DATE function is building a date from the values in three separate cells: A3, B3 and C3 and the formula looks like this =DATE($C3,$B3,$A3)

Another advantage of this strategy is that Data Validation can be applied to these cells; ie the day column can be restricted to whole numbers between 1 and 31, the month column to whole numbers between 1 and 12 and the year column as well. In the sample file I’m using, the column holding the complete date (D) is hidden from the user. They will only see columns A thru C. The complete (and correct) date is referenced in formulas.

An alternate strategy would be to use the DATE function to extract the correct order from a whole date typed into a cell. In this case you would need to rely on the users to enter the date consistently regardless of their date system. I would recommend a custom date format be applied and a comment to tell the user what the required date format is. Breaking the date up avoids this reliance on the user’s compliance.

Making Powerful Image Quotes

For those of you who haven’t had one of my seminars on using PowerPoint to create powerful image quotes for your social media feed; now’s the time to get out into the garden with your camera phone and take a few photos.

photo of chrysanthemum plant
Chrysanthemum

You need to create a stockpile of good background photos that you can use for fresh quotes. And summertime in your garden is a great time and place to do this.

Closeups of plants and flowers make a great background for a variety of quotes – like this one I found on the Olds Municipal Library Facebook feed.

picture of sunflowers behind a quote from Jo Walton
A wonderful quote from Jo Walton.

You can see how they use a transparent overlay over part of the picture to help the text stand out.

You may not have an immediate need for those pictures, but you can set them aside for later use, like this image of purple pink chrysanthemums (my chrysanthemums are looking particularly lovely this year, due to the fact I’ve just bought them).

You know you're in love when you can't fall asleep because reality is finally better than your dreams.” ― Dr. Seuss
The colour of these flowers, will do nicely for a different Valentines’ Day image quote.

You don’t need a fancy camera to get these pictures, the camera on your phone will do just fine. But do make sure you take your pictures in both horizontal and vertical orientations to make sure you have more layout options later on.

A picture in landscape and portrait orientation
When you don’t know how you’ll be using those photos, options are very good.

Don’t just focus on flowers (hehe, see what I did there), leaves and foliage are useful too.

a mirrored image of foliage
Hey! I think I see a face in there!

Don’t forget that the same picture can be used multiple ways, once you start throwing colour filters and special effects at it.

comparison of two photos with colour filters applied
Left is original photo – the right has the saturation cranked up.

Oh, and that image has been flipped, since I like the leaves appearing on the right side of the photo better.

A final tip, when saving your image quotes, use the PNG format, it creates fewer artifacts (small jiggly lines that make text harder to read) than JPEG.

Finally, be sure to create your image quotes in the right dimensions Facebook, Twitter etc. I have have some pre-sized templates that you can use.

PowerPoint – Organizing your colours

I find it useful when creating a presentation that has a custom colour palette to create a custom layout like the one below:

Slide Master containing information about the Colour Palette used in presentation
Colour Palette Assignment Slide Layout – accessible via Master view, or by selecting the layout

You’ll note that the RGB values for the colours are listed, and this is because prior to PowerPoint 2013, the eyedropper tool was not available. I also find it tremendously helpful to note what I use each colour for, so that when I open this file in a couple of years from now there will be a little less detective work.

My Tech Travel Kit

I was listening to the “Gadgets” section of the 20 Minute Delay podcast and it inspired me to share my tech travel kit.

First up is the bag I carry it all in, the Maxpedition Fatty Pocket Organizer

Photo of Maxpedition Fatty Pocket Organizer
Maxpedition Fatty Pocket Organizer

Other sizes are available, but this works for me. It allows me to organize the myriad pieces of cords and kit that are a part of modern life.  Here is an interior view –

Inside the Maxpedition Fatty Organizer
Maxpedition Fatty – Interior view

Next up is a Powerseed portable charger that will charge phone and tablet. One of the nice features of this model is the light. In a pinch it acts as a flashlight, and it certainly has saved me from fumbling around in an overly darkened hotel room a time or two.

Powerseed Charger photo
Powerseed Charger

This model doesn’t appear to be available anymore, but I like being able to charge multiple devices and as I said the light has been unexpectedly helpful.

Powerseed - view of ports and light photo
Powerseed – view of ports and light

Next is a nifty Bluetooth wireless speaker. The Anker SoundCore nano.  Having a speaker on hand allows me to leave the TV off, and listen to my music of choice.

Photo - Anker SoundCore Nano
Anker SoundCore Nano

This has an amazing sound to size ratio. Its volume will fill a hotel room and is very light.
Also from Anker is this 4 port USB charger.

Photo Anker Charging Block
Anker Charging Block

It saves time and weight and made a noticeable difference when I started packing only this instead of all the different phone/tablet charging blocks. The newer version has foldaway prongs.

Lastly is this small extension cord.

Photo Travel extension cord
Travel extension cord

I don’t know how many hotel rooms I’ve been in with inadequate or inaccessible wall plugs. This one came from our local Princess Auto and in addition to having yet more USB ports, I like how it’s designed to plug into itself. This makes it much easier to pack, since it’s the one item that doesn’t fit into the tech bag. BTW,  Princess Auto is often a great source for discount tech type gadgets.

 

Thursday – PowerPoint Links

I’ve just been working on a PowerPoint template for a Jeopardy style game. I inherited this template, and as frequently happens a little cleanup is necessary to ensure the PowerPoint template works as desired.

To help you visualize the problem – a picture of the game board

Jeopardy Game Board 1st slide
The Game Board

Each square hyperlinks to a separate slide with the question (and answer).

I felt there were a number of improvements I could do to make the presentation easier to use and maintain. I won’t go into every change today, but a couple of changes involved hyperlinks
(shortcut key Ctrl + K, if you are editing 25 hyperlinks, then the reason for using a shortcut key becomes obvious).

The first maintenance problem I ran into was that the previous designer had applied the hyperlink to both the shape AND the text on the shape (now there are 50 hyperlinks – if you are counting).

Button shape with text selected
Shape with text on top

They did this for a very good reason; that the text on a hyperlinked shape does not change state like normal hyperlink does (the state change shows if the link has been visited or not).

So if the slides the shapes are linked to are reordered or edited, the links have to be painstakingly tracked down and edited and since essentially the links are layered one on top of each other it is a real pain.

I had a better plan. Move the button shapes to the Slide Master (after creating a layout designed for the Game Board slide). Then insert text placeholders (yes, 25 of them) for the dollar values. Position the placeholders over each button. No hyperlinks here.

Now moving back to the Game Board slide in Normal View, I can hyperlink the text box. Text boxes behave differently from shapes, and do change state to show the link has been visited.

Another advantage of the text placeholder is that if the user inadvertently moves the text boxes, the Reset command will snap them back into position. (A definitely plus when editing 25 text boxes).

The other visual difficulty I had, was with the colours of the hyperlinks themselves. They didn’t have a strong contrast with my (new) button colour, and the visited colour was still (kinda) visible. I wanted a strong link colour and once visited I wanted the link to disappear. I could add animations, but why bother when I could solve both problems easily by changing the link colours in the Color Theme.

Theme Colour Panel PowerPoint 2016
Theme Colour Panel PowerPoint 2016

Here is the theme colour panel after I adjusted the Hyperlink and Followed Hyperlink Colours.

The colours in the theme were picked after playing with the free https://coolors.co/ app I also got some good advice from this article. The image at the top of the article is the colour palette created by the Coolors.co app – translated into RGB. I usually add this information as a layout in the slide master.

 

Social Media tips – free photo resources (2018)

Its’ time for an update on free photo resources for your social media work. I like to have them all together in one place since this is a list I use myself 😉

These sites are often offering more than photos, including clip art or vector images. Many fund themselves via premium or paid options, so search carefully to ensure that the picture you love is free.

  • Library and Archives Canada –Image Search
  • New York Public Library – Search Page
  • The Rijkmuseum in Amerstdam has digitized its collection. All of its works are free to use. Its’ policy “If you use our images for publication, then we request that you acknowledge the source (Rijksmuseum, Amsterdam). We would also like to receive a copy of the publication for our library.”
  • Try the Creative Commons search tool.
  • One of my favourites, Pexels.
  • University of Alberta Peel Image search.
  • Good Free Photos offers Public Domain Photos, Clipart, images, and Vectors
  • I’ve mentioned Unsplash before.
  • Not a photo resource site, but very useful is The Noun Project a great site when looking for a graphic to illustrate a concept or idea. You can pay OR give credit to the artist.
  • Rawpixel has a search specifically for public domain images.
  • Gratisography is the work of photographer Ryan McGuire. You’ll note his singular style at work.
  • IM Free  offers photos, icons and more.
  • Any internet search will probably turn up photos from Pixabay, so its probably quicker to go there directly.
  • Shopify runs a free graphics site called Burst.
  • Picjumbo offers free and a paid subscription model.
  • All photos found in the Morguefile archive are free for you to download and re-use in your work, be it commercial or not.
  • Stock Vault offers free stock photos and the opportunity to purchase via their Premium option.
  • Negative space
  • Kaboom pics claim to fame, is that the colour palette of each photo is extracted for you, useful if you are planning coordinating backgrounds or print materials.
  • Fancy Crave  All photos published on Fancycrave are licensed under the Creative Commons Zero (CCO) license which grants you an irrevocable, nonexclusive copyright license to download, copy, modify, distribute, perform, and use photos from Fancycrave for free, including for commercial purposes, without permission from or attributing the photographer or Fancycrave.
  • Stock Snap
  • Startup Stock Photos
  • Splitshire
  • Life of Pix
  • The New Old Stock focused on older pictures curated from  institutions participating in the Flickr Commons.

2018-05-17 Update:
The design tool Canva also has photos available, both paid and free – the link for searching for free photos on Canva is  https://www.canva.com/photos/tag/free+photos/

PowerPoint – Making a Mask

Creating a mask effect in PowerPoint is easy, once you’ve located the Shape Combine command. You can add this command to the Ribbon or the Quick Access Toolbar.

Below, you see it being added to my toolbar.Adding the Shape Combine Command to the Toolbar

Adding the Shape Combine Command to the Toolbar, alternately look for the Combine Shapes command as more options are available.The command will not be active until there are two shapes selected. Below, I’ve created a blue rectangle and a red oval. The oval shape will be cut out from the rectangle.Blue rectangle with red oval positioned for the cut out.

Blue rectangle with red oval positioned for the cut out.You may prefer to add the Combine Shapes command instead. More options are available as you can see below.

Select the shapes you wish to combine.
Both shapes are selected, so the Combine Shapes button is active

The result of the Shape Combine command, a rectangle with an oval “hole: in the center.The result of the Combine Shapes Command

The result of the Combine Shapes CommandOnce the mask is created, you can dress it up. Below, I’ve changed the fill to an image of a leafy forest floor.The forest floor has a hole in it.

The forest floor has a hole in it.Now I can layer whatever image I wish (in this case a frog) under the mask. You can animate the layer underneath the mask. Can you image a wheel of creatures rotating into the viewpoint in the center of the mask? That would be great for a talk about ecology!

Can you spot the frog?
Can you spot the frog?

 

Where Am I?

A fairly regular occurrence when you do a lot of driving; you find something during your drive that should be reported (roadkill, accidents, debris, etc.). If you are on a rural road, the GPS coordinates would be ideal. But, since you are a sensible person you don’t want to take your hands off the steering wheel to fiddle with your phone.

I have a solution for iOS  phones (mostly – it still will require one touch of the screen).

The solution is to use Siri and ask the question “Where am I?”

Response to question by Siri
Siri shows a map of my location

Now unfortunately, Siri doesn’t store this information, and once your screen turns off it will disappear. However, if you touch the map, the information will be transferred to the maps app. The maps app will hold the information until you are ready to deal with it.

The result of question "where am I"
The saved map

Now when you can pull over you can use either the option to Share My Location or Mark My Location.

Selecting Share My Location brings up your share sheet.

The share sheet
You can share your location in a number of ways.
Additional options are displayed as you pull the panel higher.
Additional options are displayed as you pull the panel higher.

You can also  pull the panel a little higher up over the map, for more information.  Like the GPS coordinates or contact options.

You can use the Mark My Location to do exactly that on the map. A red pin will display on the map, and the option to Edit the location appears.

Marked location options
Once you’ve marked the location, you can edit it.

If you choose to edit the location, a satellite view of the location opens, and you can drag and drop the pin.

Editing the location
Editing a location

Marked locations remain in the map app for later reference.

 

Outlook – Retrieve Dismissed Reminders

Some days, your biggest enemy is yourself.  Have you ever hit that “Dismiss All” button, or accidentally dismissed a reminder you wanted to keep?

Reminder Dialog box with highlighted Dismiss All button
Oops! I hit the Dismiss All button!

Here’s how to find that reminder, so that you can reset it.

Use the search, but instead of searching by topic, type in modified:<date>

Highlighted Search bar
Use the Search bar in the calendar view
Close up of search entry
Close up of search entry

Use the date when you accidentally dismissed those reminders (causing the modification). Your view will automatically switch into the Search Tools view. If you have multiple calendars you want to search (as I do) make sure the All Calendar Items button is pressed.

Closeup of the Search Tools view
Closeup of the Search Tools view

It will show you all the reminders you’ve modified.

In fact, you don’t need to be too precise about dates. Here’s an example, where only the name of the month was typed in.

Still works!

Search by the month name if you don't know what date.
Search by the month name if you don’t know what date.

You can then reopen the item and reset the reminder.

Fixing an annoyance in Outlook 2010

The scenario:

You like to flag your mail for Follow Up on a specific day. But Outlook always defaults to setting the reminder at the end of the day. Can this be changed to the beginning of the day?

It makes sense that if you want a reminder set for Today, that the default time for that flag is set to one hour before the end of your work day (as defined in your Calendar settings).

It makes sense that flagging a reminder for one of the pre-defined future dates (Tomorrow, This Week or Next Week) uses the start of your work day as the default time.

It makes NO SENSE that flagging a Custom date reminder reverts the default time to one hour before the end of the work day.

To change this default to the start of your work day.

Finding the Quick Click menu
Finding the Quick Click menu

Click on the drop-down arrow on the Follow Up button on the Home Ribbon.

Select Quick Click

The Quick Click dialog box
The Quick Click dialog

The Set Quick Click dialog appears. Choose Tomorrow as the default. Click the OK button.

Done. Future custom reminder times will now default to the start of the work day – not the end.