Mursalin Ibne Salehin
In this article, we will learn to find if a date is greater than 365 days with a formula in Excel. Sometimes, users need to find if a date is greater than 365 days from the current date to check the validity time of different things. Today, we will demonstrate 4 ideal examples. Using these examples, you can easily check if a date is greater than 365 days with a formula in Excel. So, without further delay, let’s start the discussion.
Download Practice Book
You can download the practice book from here.
4 Ideal Examples of Excel Formula If Date Is Greater Than 365 Days
To explain the examples, we will use a dataset containing some projects’ End Dates. Here, we will find if the End Date is greater than 365 days from the current date. The current date is basically today’s date. It means it is the date when you are trying to apply the formula in the sheet.
1. Use Excel TODAY Function to Show If Date Is Greater Than 365 Days
In the first example, we will use the TODAY function to show if a date is greater than 365 days in Excel. We will compare the date with today’s date. If a date is greater than 365 days, then the formula will show TRUE. Otherwise, it will show FALSE. So, let’s pay attention to the steps below to see how we can show if a date is greater than 365 days.
STEPS:
- In the first place, select Cell D5 and type the formula below:
=C5<(TODAY()-365)
Here, TODAY()-365 returns the date 29-08-21. If the date of Cell C5 is less than this date, then the formula will show TRUE. Otherwise, it will be FALSE.
- After that, press Enter and drag the Fill Handle down.
- Finally, you will see results like the picture below.
Note: You can also use the formula below for the same purpose.
=DAYS(TODAY(),C5)>365
But the End Dates should be prior to today’s date. Here, the DAYS function returns the number of days between today’s date and Cell C5.
Read More: Excel Formula If Date Is Greater Than 2 Years (3 Examples)
2. Print Specific Value If Date Is Greater Than 365 Days with Excel Formula
We can also use the IF function to create a formula to print a specific value if a date is greater than 365 days in Excel. In the previous example, if a date is greater than 365 days, then, the output was TRUE. Otherwise, it showed FALSE. We need to use the IF function to show a specific value. Generally, the IF function has 3 arguments; logic, value_if_true, and value_if_false. Here, we will use the same dataset. So, let’s pay attention to the steps below to see how we can implement this example.
STEPS:
- First of all, select Cell D5 and type the formula below:
=IF(C5<(TODAY()-365),"Greater Than 365 Days", "X")
If the condition is true, then the output of this formula will be Greater Than 365 Days. Otherwise, it will show X. You can see we have used the formula of Example 1 in the first argument.
- After that, press Enter.
- Now, drag the Fill Handle down.
- Finally, you will see the desired results in Column D.
Read More: Excel Formula If One Date is Greater Than Another Date
Similar Readings
- How to Find If Date Is Within 3 Months in Excel (5 Easy Ways)
- If Cell Contains Date Then Return Value in Excel (5 Examples)
- Excel Conditional Formatting for Date Within 3 Months (3 Methods)
3. Check If Difference Between Two Dates Is Greater Than 365 Days with Formula in Excel
In the third example, we will check if the difference between two dates is greater than 365 days with a formula in Excel. For that purpose, we will use the DAYS function. In the dataset, you can see the Start Date and End Date of some projects. Here, we will calculate the difference between these two dates and check if it’s greater than 365 days.
Let’s observe the steps below to see how we can check if the difference is greater than 365 days.
STEPS:
- Firstly, select Cell E5 and type the formula below:
=DAYS(D5,C5)>365
In this formula, the DAYS function finds the number of days between two days where the first argument denotes the start date and the second argument represents the end date. After finding the number of days, the formula will check if it is greater than 365 or not. If the difference is greater than 365 days, then it will show TRUE. Otherwise, it will print FALSE.
- In the following step, press Enter and drag down the Fill Handle.
- Finally, you will see results like the picture below.
Read More: How to Use COUNTIF for Date Greater Than 30 Days in Excel
4. Apply Excel Conditional Formatting with Formula to Show If Date Is Greater Than 365 Days
In the last example, we will apply Conditional Formatting with a formula to show if a date is greater than 365 days. We don’t need a helper column to check the validity time this time. We can apply the formula directly in the Conditional Formatting box. So, let’s follow the steps below to learn the example properly.
STEPS:
- First of all, select the range C5:C9.
- Secondly, go to the Home tab and select Conditional Formatting. A drop-down menu will appear.
- Select New Rule from there. It will open the New Formatting Rule dialog box.
- In the New Formatting Rule box, select ‘Use a formula to determine which cells to format’ from the Select a Rule Type section.
- After that, click inside the ‘Format Values where this formula is true’ box and type the formula below:
=C5<(TODAY()-365)- Now, click on the Format option. It will open the Format Cells window.
- In the Format Cells window, click on the Fill tab and choose a color from the Background Color section.
- Then, click OK to proceed.
- Finally, Excel will highlight the cells that contain dates greater than 365 days from today.
Read More: Conditional Formatting Based on Date Older Than 1 Year in Excel
Conclusion
In this article, we have 4 ideal examples to Find If a Date Is Greater Than 365 Days with Formula in Excel. I hope this article will help you to perform your tasks efficiently. Furthermore, we have also added the practice book at the beginning of the article. To test your skills, you can download it to exercise. Also, you can visit the ExcelDemy website for more articles like this. Lastly, if you have any suggestions or queries, feel free to ask in the comment section below.
