Excel: Adding time

Addingtime1
Automatically adding 30 minutes to the Start Time

In my Social Media spreadsheet I want to add 30 minutes to the starting time for the post.  Why 30 minutes? That’s the default scheduling time in Google Calendar.

To do this, I use the TIME function. The TIME function has 3 arguments; hour, minute and seconds – all three arguments are required. So my formula would look something like this:

=F2+TIME(0,30,0)

So, I’m adding 30 minutes to the value from cell F2.  Easy!

Excel: Find the Weekend

In a previous post I showed how I entered a column of repeating dates when building my Social Media spreadsheet. The next thing I like to do, is colour code those dates so that I can see at a glance when the weekend dates are. For this I use the WEEKDAY function in Excel.

Weekday function example1
Weekday Function Example

Point the WEEKDAY function at a date and it will return a number from 1 thru 7 indicating what day of the week the date is.  In this case the formula reads =weekday(A2,2)

The 2 in the above formula is the return type, and here indicates that the week starts on Monday. This means that Saturday and Sunday will return values of 6 & 7.

This is perfect for using with conditional formatting.

If I plug the following formula into the conditional formatting dialog box
=(WEEKDAY(A2,2))>5

I am testing for values above 5, namely the weekend. So I can use this to put a colour fill in those dates so that they stand out.

Weekday function with Conditional Formatting
Weekday function with Conditional Formatting

Obviously, the Results column isn’t needed because the formula is actually residing in the Edit Formatting Rule dialog box.

This is the second post discussing using Conditional formatting with a Social Media spreadsheet.  Check out this previous post for another example of using conditional formatting.

 

Excel: Sequential Dates in Multiples

When I’m setting up my Social Media spreadsheet in Excel, I like to limit the number of scheduled Facebook entries per day. Over time, I’ve come to think that 4 Facebook entries per day is a reasonable maximum.  This lets the librarian post “live” when things are happening in the library without clogging up our follower’s feeds.

So I want to create a column of dates that looks like this:

Each date is repeated 4 times
Each date is repeated 4 times

 

The quickest way to do this with minimal typing is to use the Fill Series dialog box. Since Excel 2007, you can find it under the Fill menu on the Home tab.

Finding the Fill Series Dialog
Finding the Fill Series Dialog

To use the Fill Series dialog, select the range of cells you want your dates to be entered in. Make sure the first cell in the range has the starting date. Then select the Fill button and choose Series.

The Fill Series Dialog
The Fill Series Dialog

Enter a Step value. In this case, because I want 4 repeats of each date I’m using .25 as the Step value.  If I wanted 5 repeats, I’d use .20 (and so on).

If you don’t feel like calculating how many cells to select when doing this for a date range that spans a couple of months; try using a Stop value. With a Stop Value, the series will stop at the first instance of the date entered into the field. Otherwise, the series will fill the entire selected range.  ( In the picture above the full date is not displayed in the field, it was actually 06/01/2016.)  Using a Stop Value allows you to make a rough selection (say 500 cells) and Excel will stop when the series runs its’ course.

 

 

Email Aliases

I don’t know about you, but I have a number of email addresses.  For example:

  • I have my main work email address.
  • The email address I use for volunteering, because the variant spelling of my first name confuses people.
  • The email address I use for shopping on line when I think the company is going to spam me

These are all forwarding accounts which means mail sent to them is all forwarded into one email account. Personally, since IMAP supports folders, I use folders to organize that email account. This lets me move between my desktop and mobile world with minimal confusion.

But using these email addresses from my iOS devices has previously required that I create multiple email accounts on my iOS device. Which I find confusing; my unread mail counts are wrong because items are counted multiple times, moving mail into folders may or may not work depending on what account I’m using to view the mail, etc.  It just hasn’t been a very satisfactory solution.

Its’ been made more frustrating, since for years in Outlook I’ve been able to send mail using different email addresses.

But yesterday, I tripped across I feature I think is new in iOS 9.1. The ability to add email aliases to my main account.

Some caveats: I have only tested this with an IMAP account, I’m using iOS 9.1 and iOS 9.2 has just been released. And using an email alias does not change the internet headers attached to the email. In other words, anyone who goes under the hood to examine my email can still see that it was handled by my main email account.  Which is fine, I’m not interested in spoofing mail accounts, rather I just don’t want to confuse people by continually responding from a different email address than the one I gave them.

So to set this up go to Settings>Mail, Contacts, Calendars and pick your email account. Open your Account Information:

iOS EMail Account Settings
iOS EMail Account Settings

You’ll notice the > beside the email address, which indicates another dialogue box.

adding a new addressWhen you move to this Email dialogue, you will find the Add Another Email … option which allows you to type in another email address. This can be any email address, as it is not verified. So make sure you spell your email address correctly.

And when you are done (and its embarrassing how long it took me to figure this out) press the RETURN button on the keyboard to confirm the email address you’ve added. Or it will be lost.

The edit button allows you to choose which email address from your list will be the default.

The From line
The From line appears

When you create a new message you’ll find the From: line appears, using your default address. When you want to change the email address the message is from, touch that line in the message ,

Picking your email address
Picking your email address

and a spin button will appear showing the list of alternate email addresses you’ve created.

Easy to use!

 

On with the show

Normally, people save their PowerPoint presentations in the default format. However, once you are on the final version of you presentation consider using the PowerPoint Show format. Saving your PowerPoint presentation as a show is easy.  Use the Save As command and use the Save As Type list to show all the possibilities. Select PowerPoint Show and save as normal.

PowerPoint 2010 Save as Dialog. Not many changes here.
PowerPoint 2010 Save as Dialog. Not many changes here.

The show will be saved in a different file format, using the .ppsx or .pps file extension.

The result is a change in behaviour when the file is opened. Double-click on the file and it will launch immediately into Slide Show view. Much slicker than starting the presentation, allowing the audience to view your notes, finding the slide show icon and starting the presentation. If you have a presentation that uses timed transitions and you are worried about the presentation running away on you, remove the timing from the first slide. Use a mouse click to advance to the rest of your timed slides once you are ready to start.

If you need to edit your presentation, start PowerPoint and use it to open the show. You can edit the file as you would normally. If you wish to convert it back to a regular presentation, use the Save As command and save it in the normal file format.

iOS App Recommendation: Due

I’m recommending an iOS app called Due. I’ve had it on my iPhone, almost from the beginning. The reason is that the iOS reminder functions and calendar alerts are too polite. For things I really need to be reminded of I use Due. You can set the reminder sound to be loud, obnoxiously loud, can hear it in a busy shopping mall loud. Sometimes I need that 😉 . You can also set it to endlessly repeat and snooze reminders as necessary.

Its’ a paid app, but it has been worth it.

Home On The Range

Sometimes, when you’re teaching, its’ not about the complexity of the subject. Sometimes its a very simple piece of information that students get the most “mileage” out of.

When I’m teaching students MS Excel, the simplest thing that I teach them about is Named Ranges. Its the  simplest thing to talk about, but the uses for ranges go on and on.

Excel Spreadsheet Example
A plain spreadsheet.

Above you see a standard Excel spreadsheet. Adding a range name or two (or ten) can help make it much easier to work with.

Adding a Range name to the Cell Address box.
Type the range name into the Cell Address box. Press Enter when done.

A range name can refer to a single cell or a group of cells, here I’ve selected the cell containing the total for the six month period (H11).

Click into the Cell address box (circled in red) and type in the desired name. There are some simple rules about naming ranges; the name can’t start with a number, can’t look like a cell reference (imagine how confusing that would be) and can’t use spaces and special characters (notice I’ve used an underscore to separate words). But after that it is up to you, to make your range name meaningful.

Adding a range name to a group of cells
Adding a range name to a group of cells.

If you are going to add a range name to a group of cells, select them and type the name into the cell address box. The most frequent mistake students make at this point, is that they forget to press the Enter key to confirm the range name.

Now, how do you use these range names?

Navigating your spreadsheet using range names.
Navigating your spreadsheet using range names.

First, you can quickly jump to your named ranges by using the drop-down menu. When you click on the drop-down menu in the cell address box, you’ll see a list of all the ranges you’ve added to your spreadsheet. Regardless of what sheet they are on. So you can use this to quickly jump to those cells that you work with again and again.

Range Names can replace cell references in formulas
Range Names can replace cell references in formulas.

Second, you can replace cell references in a formula with range names. Does =SUM(January) seem easier to read and understand than =SUM(B2:B10)? Then a formula that uses range names will make your spreadsheets easier to read.

Third, you can use range names in conjunction with all sorts of other Excel tools. As an example, try using range names with the Data Validation tool.

A Named Range provides the source for this data validation list.
A Named Range provides the source for this data validation list.

In the sample above, a range name provides the source list for a drop-down list.

Data Validation Result
Data Validation Result

Resulting in this drop-down list. The list will update as the list of animals changes on Sheet1.

This is a more elegant solution for using drop-down lists, since it means your source lists can be kept on another sheet, and not clutter up the working area. This is something that is impossible to do, without using a range name.

So faster navigation, easy to read formulas and access to more powerful features in Excel. What’s not to love about range names?

A suggestion for improvement

One of my great interests is in the interactions between Seniors and technology. As a rule; seniors are not well served by technology, they are a forgotten group. And yet, I believe that tech has a great potential to help in so many ways. In particular, adaptive technologies can do so much to help people with their daily lives, and those technologies can move out to the wider population.

It’s revealing when you find an app or function that would be perfect, if only the designers had kept seniors in mind. Because it shows how much we all don’t want to face growing old and the inevitable changes that time brings.

The app I’m thinking about today, is Day One. Day One has been a well reviewed diary/journal app on iOS for a few years. Yet it misses a really basic feature because its’ designers can’t envision anyone other than their intended audience using their product.

I found Day One for one of my senior clients. One of the things you might have heard about getting older is that sometimes you are a little forgetful. Oh, and you also might have some medical issues. Which might result in more than a few doctors office visits. Which would mean that a nice journaling app could prove useful. Especially one that could support multiple tags on a single entry. Which would mean that you could filter your entries by the name of your specialists, or medications, or illness or test results. It would be excellent if you could export information from the app (Day One allows for PDF exports of entries) and add photographs. So far, so fantastic; Day One does all these things and has beautiful clean interface.

What it doesn’t do, is take advantage of iOS’s accessibility features.

Day One Screen: Magnifiying Glasses Required
Day One Screen: Magnifying Glasses Required

While you can change the size of the typeface in your entries. Day One ignores any preference you have set for Larger Text, Bold Text or Increased Contrast. Which means that people with vision issues are going to struggle with using Day One, if they don’t give up on it altogether.

What I would love to see in this app is a high contrast setting; that would allow the tag icons to resize, adjust the labels and menu items to a typeface that is bolder/larger and that would allow the user to select a high contrast colour scheme.

I also want to see Apple lay down the law with developers and require them to respect user accessibility settings. Designers should look at designing for accessibility as a challenge that will improve the functionality of their apps for everyone. We don’t always interact with our devices under the best visibility circumstances. We may not be facing (yet) the vision problems caused by illness, but we are all going to get older.

Do you have an app that is useful for seniors? Tell me about it.

Well, this is weird …

I’ve had some oddball posts recently on my Twitter account. But I really have to step back for a moment and explain how they got there. I use IFTT to add pins I make to my Pinterest boards to my Twitter account. I enjoy looking at fantastic Steampunk art and costuming and hope that other Twitter users share my enthusiasm.

Steampunk rabbit: made of gears

I noticed a couple of months ago, that items I had not pinned were being posted under my #SteampunkFun hashtag. I kept an eye out for it happening again and it did. What I believe is happening, is that Pinterest is  jumbling up their RSS feeds. In this case, someone else’s humour pins are being attributed to my Steampunk board. But only for short periods of time. But during that time period I suddenly tweet jokes labelled #SteampunkFun – it certainly looks weird.

But what if I’ve been hacked? Wouldn’t I feel silly if I had done nothing to protect myself. This is where a Password Manager comes in handy. I recommend using password managers all the time. There are a number of popular ones out on the market. I use one called PWSafe2 (Apple). I chose PWSafe because I could use it on my Windows, Apple and Android devices. And also because it was developed by Bruce Schneier. PWSafe has a lot of nice features; including the ability generate complex passwords and a notes area.

I use the notes area to write down the lies I tell when asked for security questions. You know, the ones that ask you where you where born or what your mother’s maiden name was. Since this is all public information, I figure a lie is actually more secure than the truth. The problem is remembering what the lie is. Fortunately, the notes section is very helpful with this.

But the feature I made use of this time was the search feature. You see; if I’d been hacked, the problem would be determining which of my many many accounts had been compromised. So I used the search feature to see if I had reused my Pinterest username, email or password on any other accounts. This allowed me to target the most likely accounts and update their information. And because I have everything recorded in PWSafe, updating my accounts was no big deal.

So a password manager not only helps record your passwords and account information, but it can help you quickly change and update your accounts .

Social Media Spreadsheet – Conditional Formatting

I’m a big fan of Excel’s conditional formatting feature. I use it a lot in my spreadsheets to check on the quality of data, find errors and many other tasks. Here is the first of a couple of examples of how I’m using conditional formatting in my social media spreadsheet.

Just a bit of background on the spreadsheet. I use this spreadsheet to compose Facebook Posts and Tweets for the Redcliff Library. I also use it to schedule when the posts/tweets will be published. This allows me to sit down and plan a coherent sequence of posts/tweets.

I often take the Facebook posts and cut them down to shorter lengths and reuse them on Twitter. Twitter has a character limit of 140 characters. However, I don’t want to use all 140 characters if I can avoid it. Its’ generally recognized that the ideal tweet length is around 120 characters. This length allows others to retweet and add hashtags without having to edit the tweet.

So I have created 4 conditional formatting rules to help me meet this length limit.

  1. The background of the cell turns bright red [STOP] if the tweet is over 140 characters.
  2.  The background of the cell turns dull red if the tweet is over 135 characters.
  3. The background of the cell turns bright orange [WARNING] if the tweet is over 125 characters.
  4. The background of the cell turns dull orange if the tweet is over 120 characters.

Why four rules? I could use 2 warnings only; at 120 and 140 characters respectively. In fact, that is where I started. But, writing tweets can be a tricky thing and I found I needed a little wiggle room to help me when I compose. The other thing to keep in mind is that the conditional format isn’t applied until I finish editing the cell (by pressing the Enter key or the checkmark). It is possible to have an interactive format applied using VBA, but those functions are memory intensive and slow down the whole spreadsheet. Since my writing process seems to involve a lot of pauses to think, stopping to  apply the conditional format isn’t really a big problem for me.

So what does it look like in action?

Conditional Formatting Results
Conditional Formatting Results

As a result, I can quickly identify which tweets need to be edited. Here are the 4 rules as displayed in the Conditional Formatting Dialog box.Conditional Formatting Dialog

These are formula based conditional formats.

closer look at formula
The formula the conditional format is based on.

 

 

A conditional formatting formula must return a value of TRUE in order to fire. The following formula uses the AND, SEARCH and LEN functions

=AND(((SEARCH(“TW”,B1))>0),LEN(H1)>140)

If you were reading this formula in something like english it would read: “If the letters TW appear in column B AND the length of text in this cell is more that 140 characters the result equals TRUE”.

Why am I testing for the presence of TW in the subject column? Remember I said that I had both Facebook and Twitter posts in the same spreadsheet. I don’t want the conditional formatting to flag Facebook posts, which by their nature are longer.

Pro Tip:

When you are writing a formula for conditional formatting, do it in a cell in the spreadsheet first. The dialog for conditional formatting is really cramped and you don’t get any help features. After you are sure the formula works, you can then copy/paste it into the dialog using the Ctrl + V keyboard shortcut. Also, because I planned on apply this conditional format to the entire Description column ($H:$H). I had cell H1 selected when I built the conditional formula. That way the formula will adjust relatively to the entire column. Using absolute and relative references properly is another tricky part of building conditional formatting formulas.

applying the coloured background fill
applying the coloured background fill

Once I have my formula built. I can click the format button and select the background colour fill.

 

 

When I’ve built my first format successfully  I can then use it for the basis of the subsequent formulas. Just changing the length of the text in the cell.

  • =AND(((SEARCH(“TW”,B1))>0),LEN(H1)>140)
  • =AND(((SEARCH(“TW”,B1))>0),LEN(H1)>135)
  • =AND(((SEARCH(“TW”,B1))>0),LEN(H1)>125)
  • =AND(((SEARCH(“TW”,B1))>0),LEN(H1)>120)
The order of the rules and the stop if true flags must be set.
The order of the rules and the stop if true flags must be set.

To make this really successful, the rules need to be placed in the proper order, with the Stop If True flags turned on. Now excel will check to see if the text exceeds 140 characters first, then 135, then 125 and finally 120.  The Stop if True flag doesn’t need to be set on the final rule, because no other rules follow it.

Conditional formats can take time to build, but are extremely useful in many ways.

To find another example of using conditional formatting with a social media spreadsheet check out this post.