Excel Formula If Date Is Greater Than 365 Days (4 Ideal Examples)

Get FREE Advanced Excel Exercises with Solutions!

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.


Excel Formula If Date Is Greater Than 365 Days: 4 Ideal Examples

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.

excel formula if date is greater than 365 days


1. Use the Excel TODAY Function to Show If a 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)

Use Excel TODAY Function to Show If Date Is Greater Than 365 Days

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.

Use Excel TODAY Function to Show If Date Is Greater Than 365 Days

  • 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.


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")

Print Specific Value If Date Is Greater Than 365 Days with Excel Formula

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.

Print Specific Value If Date Is Greater Than 365 Days with Excel Formula

  • Finally, you will see the desired results in Column D.


3. Check If the Difference Between Two Dates Is Greater Than 365 Days with a 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.

Check If Difference Between Two Dates Is Greater Than 365 Days with Formula in Excel

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

Check If Difference Between Two Dates Is Greater Than 365 Days with Formula in Excel

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.

Check If Difference Between Two Dates Is Greater Than 365 Days with Formula in Excel

  • Finally, you will see results like the picture below.


4. Apply Excel Conditional Formatting with a Formula to Show If the 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.

Apply Excel Conditional Formatting with Formula to Show If Date Is Greater Than 365 Days

  • 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.

Apply Excel Conditional Formatting with Formula to Show If Date Is Greater Than 365 Days

  • 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.

Apply Excel Conditional Formatting with Formula to Show If Date Is Greater Than 365 Days

  • 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.


Download Practice Book

You can download the practice book from here.


Conclusion

In this article, we have 4 ideal examples to Find If a Date Is Greater Than 365 Days with formulas 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. Lastly, if you have any suggestions or queries, feel free to ask in the comment section below.


<< Go Back to Dates | Compare | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mursalin Ibne Salehin
Mursalin Ibne Salehin

Mursalin Ibne Salehin holds a BSc in Electrical and Electronics Engineering from Bangladesh University of Engineering and Technology. Over the past 2 years, he has actively contributed to the ExcelDemy project, where he authored over 150 articles. He has also led a team with content development works. Currently, he is working as a Reviewer in the ExcelDemy Project. He likes using and learning about Microsoft Office, especially Excel. He is interested in data analysis with Excel, machine learning,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo