Performing the calculation of dates in Google Sheets is as simple as basic arithmetic since the application views date as integer numbers.
Thus, much like calculating dates before today, calculating if a date is after today in Google Sheets follows similar functions and formulas.
How do I Find if a Date is After Today in Google Sheets?
How to find Today’s Date
The primary condition for our calculation is that the date must be “after today”. So, it is a good idea to see how we can generate today’s date in Google Sheets automatically.
Thankfully, we have the TODAY function to help us out. The function takes no parameters and automatically generates the current date of your computer’s locale.
A situational downside of the TODAY function might be the fact that it is a “running date”. Meaning that the date will always update to the current date every time the worksheet is updated.
So, if you want a static date for today, it is best to type in the current date manually.
Learn More: How to Use Today’s Date in Google Sheets
Simple Formula to Calculate if Date is After Today
As we are done finding “today”, the only condition remaining is “after”. Since we already know that Google Sheets assigns an integer to each date, any date after today will have a greater integer value.
So, if either of these conditions: Today’s Date < Date or Date > Today’s Date is satisfied, we will know that the Date is after today.
Our formula in action:
Adding an IF Condition to our Date Calculation
By adding the IF function to our date calculation, we can present a sensible message in our worksheet.
It may not look like much since we are using it in a basic calculation, but the IF function is a godsend when working with complicated date calculations.
Example: Count Days After Today in Google Sheets
A common date calculation is obviously the count of days. So, by following the gist of this article, we will show how we can calculate the number of days after today in Google Sheets.
Like the comparison, counting days is simplified since all the dates are seen as integers. This, a simple arithmetic subtraction will work just fine.
Date - Today’s Date
As you can see, some numbers are negative. This is because these dates are before today. In such cases, we take advantage of the IF function to make our results more presentable or intuitive to the theme of the worksheet.
=IF((B3-TODAY())<0,"The Date is Before Today",B3-TODAY())
Learn More: Count Days From Today in Google Sheets
Highlight if Date is After Today in Google Sheets Using Conditional Formatting
The most common use of date calculation in Google Sheets is to use them to highlight or present them intuitively. That’s where conditional formatting comes in.
Using our date calculation in conditional formatting, we can highlight cells to meet user demands. Let’s see a couple of examples.
Highlight Date Cells if They are After Today
For our first example, we will highlight the cells that contain dates that are after today. Let’s see this step-by-step.
Step 1: Navigate to the Conditional Formatting window. You can find Conditional formatting under the Format tab of Google Sheets. Format > Conditional formatting
Step 2: Make sure that the format range (Apply to range) is correct. You can select the range before opening conditional formatting or change it from within the window.
Step 3: Set your conditional formatting rules. Our condition is: “highlight dates after today”. We can use the built-in rule set to achieve this.
- Date is after
Alternatively, we can use a custom formula to achieve the same results. Our custom formula is the same formula that we have used to find whether a date is after today or not. Since the results are in Boolean, it works perfectly with conditional formatting.
The benefit of using custom formulas is that we can change our conditions to adapt to the user’s needs.
Step 4: Click Done when you are satisfied with the conditional format to apply it to your worksheet.
Using Multiple Conditions: Today or After Today
Speaking of the benefits of using custom formulas, it allows us to use multiple conditions for conditional formatting.
For example, let’s say we want to highlight dates for both today and after today’s date. So, we have to bring in the OR condition to bring together our two conditions.
Or we can simply use the OR function:
Highlight Another Cell According to Date
A requirement may arise where we want to highlight cells of a different column if the date in the Date column is after today’s date.
For this, we once again look to custom formulas to give us our formatting rules. We also have a major change this time, which is the format range.
The new format range will be the column that we are going to highlight.
Our custom formula is the same as before:
Click Done to apply.
Highlight Entire Row
To highlight the entire row according to the date we have to bring two changes to our conditional formatting rules:
- Change the format range to cover the entire span of the table or dataset.
- Update the custom formula to include an absolute ($) before the column number. Our new formula is:
If you want to add more columns, simply update the format range.
That concludes all the ways we can use to calculate if a date is after today in Google Sheets. With dates seen as integer values, it becomes that much easier to work with dates in Google Sheets, whether it be counting, comparison or even using our calculations in other platforms like conditional formatting.
Feel free to leave any queries or advice you might have for us in the comments section below.