Google Sheets form responses contain time-stamp. Also, some people use a script to input time stamp in Google Sheets. Here I am not talking about that. My question here is how to compare a time-stamp with a normal date in Google Sheets as one is pure date and the other is a date with time. So the values are entirely different for a logical IF test.

I mean a time stamp “31/12/2017 20:46:10” is not equal to “31/12/2017”. So a logical test may return unexpected results.

When you want to perform a logical test in a cell or array containing time-stamp, you should know how to remove the time from a time-stamp. Then you can use this clean date within the logical test.

You can adopt different methods to clean time from time stamps and return the only date. I’ve already detailed it.

Must Read: How to Extract Date From Time Stamp in Google Sheets

Hope you have checked the above Google Sheets tutorial. Now back to our topic and example.

Example to Compare Time Stamp with Normal Date in Google Sheets

In this screenshot, you can clearly see that Column A contains time-stamps. I have an array formula in B1 which checks the time-stamps in Column A. The logical test is like this.

You May Like: Learn all date functions in Google Sheets

If column A date is greater than “20-01-2018”, the formula should return “Late Submission” else “Accepted”.

Now I am bringing your attention to the below two formulas. The first one is the original formula which I’ve used to compare the time-stamp with the normal date in Google Sheets. See this formula in the above screenshot.

Formula 1.

=ArrayFormula(if(len(A1:A),(if(int(A1:A)>date(2018,1,20),"Late Submission","Accepted")),""))

In this formula, I’ve converted the time stamp in Column A to normal date with the help of the INT function.

If you use the below formula, which is a normal logical test, it would return a wrong result in row 2 as it treats the time stamp “20/01/2018 19:30:22” in row 2 and date “20/01/2018” as two different values.

Must Check: Google Sheets all time-related functions in one place

Formula 2.

=ArrayFormula(if(len(A1:A),(if(A1:A>date(2018,1,20),"Late Submission","Accepted")),"")

If you are unfamiliar with using any of the functions used in the above example, feel free to check my Google Sheets Functions guide to pick the functions that you want to learn. That’s all.

This way you can compare time-stamp with a normal date in Google Sheets.