Month: January 2022

Importing Range Data From One Google Sheet to Another

One really amazing thing about Google Sheets is how easy it grab live data from one Google Sheet and import it into another. I’m not talking about a simple copy and paste job here. I am talking about real live data. Data that, when updated in the master sheet, will be reflected in the Google Sheet that you have imported the data to.

Everything starts with the IMPORTRANGE Google Sheets function.

But before we get started on the “How to’s,” you might be wondering why you might need to import live Google Sheet data from one sheet to another.

Or check out the table of contents to dive straight into what you need right now!

Continue reading “Importing Range Data From One Google Sheet to Another”

Create a ISO String from date text input intended for UTC date in JavaScript

Let’s say that you receive a date like “14/01/2022”, “14 January 2022”, “Jan, 14 2022” etc, and you need to convert this date to an ISO string in JavaScript while ensuring that the date that is inputted is for UTC (Universal Time Coordinated) timezone – no matter where you are in the world. It would seem easy right?

Your first reaction might be to simply do something like this:

Now unless you are sitting smack-dab in a UTC timezone you might be in for a bit of a surprise.

Right now, my timezone is UTC+11 hours. This means that my result of the code in the example above will report the previous day at 1pm UTC.

That’s not what I am looking for all. I need to set this date to precisely midnight of 14 Jan 2022 UTC time.

The ISO String we need is this: 2022-01-14T00:00:00.000Z

The Solution

Continue reading “Create a ISO String from date text input intended for UTC date in JavaScript”

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”