Google Sheets form responses contain time-stamps, which can sometimes cause confusion when trying to compare them with normal dates. In this article, we will explore how to effectively compare a time-stamp with a normal date in Google Sheets. So grab your favorite beverage and let’s get started!
The Challenge
Let’s say you have a time-stamp like “31/12/2017 20:46:10” and you want to compare it with a normal date like “31/12/2017”. Logically, these two values are different, and a simple IF test may yield unexpected results. This is because one value includes a time component, while the other is solely a date.
Removing the Time Component
To perform a logical test in Google Sheets that involves comparing a time-stamp with a normal date, it’s important to know how to remove the time from the time-stamp. This will allow us to use the clean date for our logical test.
There are several methods you can use to clean the time from time-stamps and return only the date. I have already covered this topic in detail on my website, Crawlan.com. If you haven’t already, I recommend checking out my tutorial on how to extract the date from a time-stamp in Google Sheets before proceeding.
Example: Comparing Time Stamp with Normal Date
Let’s dive into an example to better understand how to compare a time-stamp with a normal date in Google Sheets. Take a look at the screenshot below:
In this example, we have time-stamps in Column A. In cell B1, I have used an array formula to check the time-stamps in Column A against a normal date. The logical test is as follows:
=ArrayFormula(if(len(A1:A),(if(int(A1:A)>date(2018,1,20),"Late Submission","Accepted")),""))By using the INT function, we convert the time-stamps in Column A to normal dates. If the date in Column A is greater than “20-01-2018”, the formula will return “Late Submission”. Otherwise, it will return “Accepted”.
Understanding the Formula
Now, let’s take a closer look at the formula used to compare the time-stamp with the normal date in Google Sheets. Here is the formula again:
=ArrayFormula(if(len(A1:A),(if(int(A1:A)>date(2018,1,20),"Late Submission","Accepted")),""))Using this formula ensures that the time-stamp and the normal date are treated as the same value for the logical test. If we were to use a regular logical test, it would treat the time-stamp and the date as separate values, leading to incorrect results.
Need Help?
If you are unfamiliar with any of the functions used in the example above, don’t worry! I have a comprehensive guide to Google Sheets functions on my website, Crawlan.com, where you can learn about all the functions you need.
That’s all there is to it! Now you know how to effectively compare a time-stamp with a normal date in Google Sheets. It’s time to put this knowledge into action and make your spreadsheets even more powerful.
Happy comparing!
