Tag: Google Sheets VALUE

Google Sheets FILTER function: Dates and Times

If you have landed on this page you may have discovered that dates and times don’t always work as you might expect with the Google Sheets FILTER  function.

In this tutorial, we are going to cover how to use FILTER on dates, times and date and time stamps (DTS).

You can follow through the tutorial in its entirety or use the table of contents below to get directly to what you need.

Continue reading “Google Sheets FILTER function: Dates and Times”

How to create a time sequence in Google Sheets

Whether you are creating a Google Sheets data validation dropdown list of each minute in the day or want to create a daily progress log with 15-minute intervals, learning how to create a list of times in Google Sheets is a pretty solid skill to have.

However, knowing how to create a list of times in a single formula not only makes you a spreadsheets archmage 🧙‍♂️ but also allows you to do cool stuff like:

  • Automatically change your start and end times.
  • Changing your step increments from a minute to, say, every five minutes or an hour.
  • Make these changes quickly straight in your formula or another cell reference or even a formula rule in another cell.

So yeah… like I said… archmage skills.

via GIPHY

Sound cool? Of course it does, it spreadsheets! 🐐

In this tutorial, we will cover two approaches:

  1. A whole day list of times in 1-minute intervals
  2. Selected start and end times with selected intervals

Why can’t we do just one?

Well, there are two slightly different approaches to each. Geez! What’s with the questions?

Let’s get cracking!

Continue reading “How to create a time sequence in Google Sheets”

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”