Nifty trick: Pulling just 1 year’s data

Last week I was focusing on how to use incremental formulas to determine the most recent time something was used, but in the process of adding that to the example spreadsheet I also ended up making a couple of other changes that are worth discussing.

Change 1: Limited to the current year

You might have noticed that although there were dates from January 2012 through May 2013, on the table showing classes taught by month that there aren’t any entries after May.  That’s because I’ve set it to only pull the dates from the current year.  Here’s a generic explanation as to do that. Continue reading Nifty trick: Pulling just 1 year’s data

Project: Use tracking

There’s another type of incremental formula that can be used to track how recently someone did a particular thing, or how recently a particular thing was used. These things could be almost anything, though it works best with finite sets. It could be used to track equipment usage by tracking how recently each item was used, and who used it. It could be what household chores and been done, and who’s been doing them. As an example I’ve made a spreadsheet that tracks how recently a given professor has brought their class in for library instruction. It’s a modification of the one I did as a countif() example. Continue reading Project: Use tracking

Wild cards and regular expressions

If I’m going to discuss finding and replacing, I really need to also discuss wild cards. Most people are probably familiar with the concept of wild cards, in one form or another. I assume the term comes from card games, where jokers can be used to represent any other card that’s needed. People may also be familiar with the concept, if not the term, from Scrabble, where there are blank tiles that can represent any other letter tile.

Wild cards in spreadsheets (and in word processors, and in internet searching, etc.) are characters that can represent other characters – often more than just one. That is to say, if * is a wildcard then educat* could be educate, education, educating, educator, educatxyz, and so on. Continue reading Wild cards and regular expressions