Count the Number of Selected Days of the Week in Google Sheets

Google Sheets: DAYS, NETWORKDAYS.INTL, NETWORKDAYS, VALUE

A very common spreadsheet task is to get the total count of the days between two dates. We might need this information to:

  • Find the number of days absent of staff or students.
  • Find the total days worked or attending a class.

Some times we need to avoid including weekends, national holidays or certain days of the week in our total account. Fortunately for us, Google Sheets has some custom functions to help us easily work this all out.

This tutorial will cover how to find the total number of days over a range minus any selected days you don’t want to be included. We’ll also cover how to get the total count of specific days of the week over a range.

NOTE! I have the dates set to the British standard e.g. 5 May 2020. You can change the formatting around to any gregorian date format for these examples.

Let’s get cracking!

Continue reading “Count the Number of Selected Days of the Week in Google Sheets”

Google Sheets: Get the Number of Words or Sentences in a Cell (Paragraph)

Google Sheets: ROUND, SPLIT, COUNTA

Have you ever woken up in the middle of the night thinking – phawh! I’d really like to know how many words there are in a paragraph – Or stopped mid-chew on some arbitrary item carelessly left in the paddock by the boss and thought – geez! If there was only a way to count the number of sentences in a paragraph in a cell in Google Sheets!?

Well, you’re a bit weird, but I guess you are in the right place.

In this short tutorial, we will show you how to get the total count of words in a cell and the total count of sentences in a cell in Google Sheets. Hey, we will even throw in a how-to on getting the average sentence length in a paragraph, because we are nice like that – and we are a bit weird too.

We’ll give you the formulas for you to jump off on your own project straight away. We’ll also give you a detailed explanation of the formulas so you can figure out how it all works using our example.

Navigate through the table of contents to get to what you need or read on!

Continue reading “Google Sheets: Get the Number of Words or Sentences in a Cell (Paragraph)”

Create a seat booking form with Google Forms, Google Sheets and Google Apps Script

Google Forms, Google Sheets, Google Apps Script: SpreadsheetApp, FormApp

In this tutorial, we are going to build a simple seat booking Google Form. It will contain a registeree’s name and the session that they wish to attend.

Each time a registeree submits a request to book a seat for a session, that seat is taken from the list displaying only the remaining seats for that session for when the next user submits the form.

Take a look at the example below (click to expand the image):

Basic Seat Booking Form Google Forms

If all the seats have been booked for a seminar, then that seminar will not appear on the form. If all seats have been booked for all seminars, then the Google Form will close.

We will even create a  live list of attendees that we can embed on our website using Google Sheets.

Google Forms can’t do this for you out of the box. We will need to use Google Sheets to store our bookings and do some manipulation of the data. Then, we will use Google Apps Script to update the Google Form with the number of seats each time the form is submitted.

The Google Apps Script script has been prepared in a way that someone with limited coding skills can use it as a template by plugging in a few global variables. Alternatively, a more advanced user can incorporate the code into a larger project.

I’ll assume you have the basic abilities to create a simple Google Form and have used Google Sheets.

This tutorial can be followed as a useful step-by-step guide or if you are more confident, you can simply jump to the code using the contents below.

Continue reading “Create a seat booking form with Google Forms, Google Sheets and Google Apps Script”

Google Sheets: How to create a task completion check sheet. (Updated Feb 2022)

Google Sheet: IMPORTRANGE, IF, TODAY, conditional formatting, tick box, alternating colors, protect sheet and ranges, share sheet 

Sometimes you have teams, staff or students who all need to complete reports and tasks by a certain date, but you need a way to keep track of when the reports are complete so that you can compile them later.

The following tutorial is a simple tick box-based Google Sheet that the user ticks when they have finished writing their quarterly report. I use something like this in my day to day to keep track of a number of reporting processes.

This is very much a follow-along guide for Google Sheet Beginners to help develop some basics skills and think about how to design Sheets for the workplace. Feel free to read what you need or skip to the end for a link to the google sheet for you to make a copy of.

Here is what we will complete by the end of the tutorial.

Task completion check sheet google sheets

If the user’s tick box had not been ticked in Column ‘C’, Column ‘D’ will automatically report Overdue in red and Column ‘E’ will report the number of days overdue.

There is a space set aside for administration to note the reminders that they have sent to the user and the last date the reminder was sent. Admin can easily copy the emails and send a message to those staff who have not completed their report by the deadline.

Let me walk you through the workflow for creating this:

Continue reading “Google Sheets: How to create a task completion check sheet. (Updated Feb 2022)”

Google Sheets: How to hide formula error warnings where there is no data or the data divides by zero

Google Sheets, IFERROR, ISBLANK, IF

Have you ever set up a Google Sheet with formulas that you drag all the way down to the bottom? See all those messy error values littering your otherwise immaculate spreadsheet when those formulas don’t reference a value? It sucks, right?

Formual errors referencing empty values Google Sheets

What if I told you that you could hide all those messy errors? What if I told you that you could truly have that pristine nirvana that you have been imagining that your Google Sheet project would truly be?

Drink the Kool-Aid and strap in, my friends, as your very own guru in a goat takes you on a journey of enlightenment into hiding formula error when:

Continue reading “Google Sheets: How to hide formula error warnings where there is no data or the data divides by zero”

%d bloggers like this: