Calculating the duration of time between two times is a pretty common task. From measuring how long a process took, to entering the hours you put into a project, to creating a timesheet. You will find it everywhere, so working out duration is a hand skill to have in Google Sheets.
However, there are some gotchas when calculating the duration of time. So it is worth a good looking into.
To get a feel of how calculating durations work out in the wild, you can check out the video below for a great example on how I use it to build a Project Task Google Sheets workbook.
I’m offering a whopping 30% off my Google Sheets Mastery course this Black Friday/ Cyber Monday this 2021.
Learn Google Sheets as we walk through three real-life projects. Honestly, taking on real-life examples is the fastest way to learn a skill and understand it deeply.
Now is the perfect time to upskill for the new year.
It can be frustrating when our Google Sheets functions don’t go the way we expect them to.
In this quick tutorial, we cover two common reasons why your COUNT function might not be running the way you expect it to. One beginner issue and one a little more advanced.
Counting Characters
When you count a range of numbers you use the COUNT function. However, if you are counting all the characters in a range you need to use the COUNTA function.
COUNTA counts all the values in a dataset including:
All duplicate values (You can use COUNTUNIQUE for just unique values).
Cells containing white space. That is, cells with spaces in them but no text.
Cells with a text string of zero. This is usually generated as a result of a formula.
To use COUNTA simply select a range or set of ranges.
=COUNTA(Range)
=COUNTA(C2:C25)
Counting the wrong number of values in a range generated from formulas
Quite often we are trying to template our Google Sheets for reuse in other projects or tasks. Take a look at this example:
Click to Expand!
Here in Column K we have a formula that joins the first and last name together from columns I & J if it sees text in column J otherwise it will return an empty string. Here is the formula:
=IF(J2=“”,“”,JOIN(” “,I2:J2))
This formula is then dragged all the way down the column so that it can be used again for another project.
The problem
The problem is that if we try and use COUNTA to get the values in Column K we will return a wrong number.
Why?
Because COUNTA will also count any cell that contains a string of characters with a length of zero (“”). So this will include and count all the cells in our range that contain our formula where we don’t want to display a value.
So how do we fix this?
The solution
Option 1
The most logical option is to try and use another column to reference your count. Unfortunately, this isn’t always possible.
Option 2
If your formula returns an error on an empty cell and you want to hide it, you could use the IFERROR function instead of the IF("","",... option to create an empty cell.
Here you wrap your formula inside the IFERROR function. You then have an optional argument to add what to do if there is an error or leave it blank if you don’t want anything to appear in the cell.
=IFERROR( your formula)
When you run a COUNTA on this column now it will return the correct count.
Option 3
If you can’t do either of these options then you could create a formula that checks the length of each string of data in each cell and if that cell length is greater than zero, count the cell.
First, you can get the length of a cell with the LEN function that takes a cell value. In our example, we will use the first cell item in our full name column K.
=LEN(K2)
We need to be able to use this LEN value on the selected range that we want to count. Here, we can use the ARRAYFORMULA function. This will allow us to put in a range of values in our LEN formula and get the length of each one. The result will be an array containing the length of values for each cell in the range.
=ARRAYFORMULA(LEN(K2:K))
Finally, we can use COUNTIF to count all the values in our new array of lengths that are greater than zero.
=COUNTIF(ARRAYFORMULA(LEN(K2:K)),“>1”)
Conclusion
Check out the video below for more information.
Do you have a clever way of dealing with this problem? I would love to hear about it in the comments below.
Google Sheets Shortcuts Mousepad
Want a fast track to boost your Spreadsheet efficiency? Grab one of these handy Google Sheets Shortcuts mousepads that I created from my store.
Whether you are looking to skill up, make your work-life easier or impress your boss then this course will take you from Google Sheets beginner to producing corporate-level Google Sheets with three detailed projects.
And because this is the opening week, I am offering a whopping 60% off the course for the next 7 days only.
Just use the Coupon Code…
NEWCOURSEDISCOUNT
…on the checkout page and you can start the course today.
If you are already a Google Sheets legend, you know how important it is to learn how to master spreadsheets. If you know someone transitioning industries, just starting out or simply needing to boost their skillset please share this post.
This is a whopper of a discount and not one to miss.
This has been a project I wanted to build for a long time. The best way to learn something is by getting hands-on and building a project. That’s how I know I learn best.
I wanted to build a Google Sheets beginners to pro course that I wanted when I first started in the world of Google Sheets. Something with real-life examples and a set of projects that I can build and maybe even modify and reuse in the future. Something that not only gives me the how-toos but the why I should be building and designing my spreadsheets in a certain way.
Go on, check out the link to find out more about the course and try out the first 5 chapters on me. I know you are going to get a lot out of it.
You’ve probably come to this page feeling pretty frustrated that you’ve received a link to a Google Sheet, Google Slide or Google Doc in an email or found a link to one of these documents in a website or even one of my tutorials, but you just can’t edit it. You can’t update the text and even half of the menu bar is greyed out!
It means that you have been given only View permissions to this document.
Why?
Generally, it’s because any changes in a Google Workspace Document, (Sheets, Slides, Docs) will be displayed for anyone who has access to the document. So if you add or delete something in a document, then other editors, commentators and people with view-only access will see the change in near real-time.
There are three main reasons why an owner of a document will provide View-only access to users:
1. It might have only been meant for you to read-only.
Maybe you get an email from another department from work with a new policy that they have. You don’t really need to make edits to this. You just need to read it, right?
If you think this might be the case, but you want to be able to access the document in your personal folder, you can go to File > Add a shortcut to Drive. This will save a short cut to the file in your own Google Drive to help you to better organise your documents.
You still won’t be able to edit the document, but you don’t need to.
Also, the owner or the editors of the documents can also make copies of the document after you have made the shortcut if they need to.
2. It might be an example document or template.
In my tutorials, I often provide links to templates or example documents. Here again, I only ever provide view access.
However, you can create your own copy of these documents easily, by going to File > Make a copy. You will then be prompted to move the copy into a desired folder in your Google Drive. Once done, a new window will appear in your browser. You will then have your very own copy of the document that is owned by you. You can now edit it and it will not affect the original document, because they are two separate documents.
3. The owner may have forgot to make you an editor.
Maybe the owner of the document was meant to give you edit permission but may have forgot.
Now before you get too excited, there is one thing you might want to check to save yourself some embarrassment. Go up to the top of your sheet and check to see if you are using the correct account for this sheet. You can do that in the top-right corner of the document and confirm you are using the right avatar.
Perhaps the owner has given you edit permission to the document in another one of your accounts, like a work account.
If you are in the wrong account, simply change to your correct account and check to see if you have edit permission.
If you are in the correct account and you are certain you should have edit permission, then you can select the View Only button and then click Request view access. An email will be sent to the owner and they can decide if you need edit access.
Why so much build-up to this last option? Well, you can imagine that it can be a little frustrating to get a bunch of emails from people who don’t actually need edit access.
Fortunately, there is a workaround to receiving a bunch of emails requesting edit access unnecessarily. Check out the tutorial below:
Google Sheets Shortcuts Mousepad
Want a fast track to boost your Spreadsheet efficiency? Grab one of these handy Google Sheets Shortcuts mousepads that I created from my store.
