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.

Duotone Photo Technique

Duotone Photos

I’ve been showing you how to use PowerPoint to quickly create stencil and lace effects.  Now, let’s look at creating duotone photos. In addition to making a photo look very modern, duotone is a useful technique for using less than stellar photos.

a photo of a cat in a kitchen
Cat in the Kitchen

While the cat might be photogenic, the background is not. I want to move from the photo above to the duotone below, which is suitable for adding a quote.

Cats have it all quote by Rod McKuen
So true

The first step is to crop the picture as closely as possible.

Close up of cat's face
Just the handsome face here – no clutter

But unfortunately, once enlarged you see the photo is a little blurry. This won’t be a problem going forward and it shows how this technique can cope with less than perfect photos.

Going to Picture Corrections:
Brightness was set to 65%
Contrast to 100%

Picture Color: Saturation was set to zero.

Photo after adjustment
The Adjusted Photo

There is a bit of guesswork here, as I had to bring up Brightness enough to wash out the dark corner of the chair the cat is on, yet leave as much detail as possible. You’ll note that this brings out a lot of light spots on the pupils as well.

Why not just Recolor the picture to Black and White? In this case, I felt that recoloring removed too much detail from the photo. In the case of a different photo, recoloring might be the quickest and easiest method.  I’d definitely try it first and see if I liked the results.

I’ve drawn a rectangle and filled it with a bright colour for contrast, this has been placed under the photo.

Now I can make the white portion of the photo transparent, by selecting Picture Tools>Format>Color>Set Transparent Color  and clicking on a white portion of the picture.

Showing the black portion of the cat photo
The black portion of the photo remains.

What’s also hard to see in the above picture is that the photo has a lot of small grey artifacts in the borders of the fur. This is exactly what we added in when making the lace picture earlier, but here it is unwanted. An additional step is required for this photo (again for some photos it might be unnecessary).

But before I do that – I’m going to use the Ink command and touch up the pupils to remove some of the glints. Ink is only available in Office 365.

excess glint repair using Ink tool
Showing Glint repair using ink tool.

After filling in the glints on the pupils, I grouped the ink layer with the photo. Then I copied and pasted the photo (and ink layer) as a picture.  PowerPoint remembers all the photo editing done to a picture (which is why the Reset command works) and applies those steps cumulatively. I want to start fresh and apply the Recolor command to strip out the grey artifacts without losing a lot of detail. After recoloring the photo to 25% Black and White I set the White color to transparent

photo after removing artifacts
The same photo, but now with a crisper look

Again, I grouped the photo with bright background rectangle, pasted it as a picture and this time set the black portion as transparent. This is similar to the photo stencil.

transparent cat
Photo with transparent cat

In the final step, set a gradient fill in your chosen colour scheme to colour the duotone.

Using the background to colour the duotone
Setting the background of the slide to colour the duotone

The main elements of this technique are applicable to a number of photo effects. Try them out and see what you get!