How to Use Ageing Formula for 30 60 90 Days in Excel (5 Effective Ways)

Get FREE Advanced Excel Exercises with Solutions!

Looking for Excel ageing formula 30 60 90 days? Well, you have come to the right place. Here, we will demonstrate to you 5 methods for Excel ageing formula 30 60 90 days. All these methods are simple and effective.


Excel Ageing Formula for 30 60 90 Days: 5 Methods

In the following article, we describe 5 methods step by step for Excel aging formula 30 60 90 days. Here, we used Excel 365. You can use any available Excel version.


1. Using Ageing Formula for 30 60 90 Days with Conditional Formatting Feature

The following table has Customer, Project, and Date columns. We will use the Conditional Formatting feature to find out the date that is 30,60 and 90 days after today.

Excel Aging Formula 30 60 90 Days

Step-1:

  • First, we will select the entire data from the Date column.
  • After that, we will go to the Home tab >> select Conditional Formatting >> select New Rule.

Step-2:

A New Formatting Rule dialog box will appear.

  • Then, we will select use a formula to determine which cells to format.
  • After that, we will type the following formula in the format value where this formula is true box.
=AND(D5 >= TODAY(), D5 <= TODAY()+30)

Here, we used the AND function where we used two logical conditions for the used Date range. Where the conditions are the date has to be greater than or equal today and less than or equal TODAY()+30. Here, we used the TODAY function to get the date of today. If it fulfills the conditions then it will fill blue color to the respective dates.

  • After that, we will click on Format.

Excel Aging Formula 30 60 90 Days

Step-3:

A Format Cells dialog box will appear.

  • Afterward, we will select Fill >> select a color, here we selected blue color and we can see the  sample.
  • Then, click OK.

Step-4:

  • After that, we will click OK in the New Formatting Rule window.

Now, we will see that all the dates that are 30 days away from today have been marked with blue color.

Excel Aging Formula 30 60 90 Days

Next, we will highlight the dates that are 60 days away from today.

  • Here, we will follow similar steps of Step-2, to bring up the New Formatting Rule dialog box.
  • Next, in the Format value where this formula is true box, we will type the following formula.
=AND(D5 >= TODAY()+30, D5 <= TODAY()+60)

Here, we used the AND function where we used two logical conditions for the used Date range. Where the conditions are the date has to be greater than or equal TODAY()+30 and less than or equal TODAY()+60. Here, we used the TODAY function to get the date of today. If it fulfills the conditions then it will fill green color to the respective dates.

  • After that, by following Step-3, we will choose a color to highlight the cells.
  • Here, we’ve chosen green color.

Finally, we can see dates that are 60 days away from today have been highlighted with Green color.

Now, we will highlight the dates that are 90 days away from today.

  • Here, we will follow similar steps of Step-2, to bring up the New Formatting Rule dialog box.
  • Next, in the format value where this formula is true box, we will type the following formula.
=AND(D5 >= TODAY()+60, D5 <= TODAY()+90)

Here, we used the AND function where we used two logical conditions for the used Date range. Where the conditions are the Date has to be greater than or equal TODAY()+60 and less than or equal TODAY()+90. Here, we used the TODAY function to get the date of today. If it fulfills the conditions then it will fill the yellow color to the respective dates.

  • After that, by following Step-3, we will choose a color to highlight the cells.

Finally, we can see dates that are 90 days away from today have been highlighted with yellow color.

Excel Aging Formula 30 60 90 Days

Read More: How to Calculate Days with Aging Formula in Excel


2. Adding 30, 60 & 90 Days in Excel Aging Formula

In the following table, we will add 30 days, 60 days, and 90 days with the Due Date column.

Steps:

  • First, we will type the following formula in cell E5.
=D5+30

This will simply add 30 days with the date of cell D5.

  • After that, we will press ENTER.

We can see the result in cell E5.

  • Afterward, we will drag down the formula with the fill handle tool.

  • After that, we will type the following formula in cell F5.
=D5+60

This will simply add 60 days with the date of cell D5.

Excel Aging Formula 30 60 90 Days

  • After that, we will press ENTER.

We can see the result in cell F5.

  • Afterward, we will drag down the formula with the Fill Handle tool.

  • After that, we will type the following formula in cell G5.
=D5+90

This will simply add 90 days to the date of cell D5.

Excel Aging Formula 30 60 90 Days

  • After that, we will press ENTER.

We can see the result in cell G5.

  • Afterward, we will drag down the formula with the Fill Handle tool.

Finally, we can see the Excel aging formula 30 60 90 days in the table.

Excel Aging Formula 30 60 90 Days


3. Use of IF, TODAY, and VLOOKUP Functions

For the following table, we will use the combination of IF and TODAY functions to calculate Days Sales Outstanding. After that, we will use the VLOOKUP function to find out the Invoice Status.

Steps:

  • First, we will type the following formula in cell F5.
=IF(TODAY()>E5,TODAY()-E5,0)

Formula Breakdown

  • E5 is the invoice date.
  • TODAY() function will return today’s date which is 14-06-22.
  • IF function will return 0 if the difference between Today() and E5 is negative, otherwise the Days Sales Outstanding value will be equal to the difference between Today() and E5.
    • Output: 39
  • After that, press ENTER.
  • Then, we will drag down the formula with the Fill Handle tool.

We can now see the complete Days Sales Outstanding column.

Excel Aging Formula 30 60 90 Days

Now, we want to find out the Invoice Status.

  • For this, we have created the Days Category table. This has the categories of the invoice in the Category column according to their Days Sales Outstanding column to dictate the condition. We will use this Days Category table as table_array in the VLOOKUP function.

  • Afterward, we will type the following formula in cell G5.
=VLOOKUP(F5,$J$4:$K$10,2,TRUE)

With this formula, we will be able to identify the conditions of the invoice by looking up the values of Days Sales Outstanding.

Excel Aging Formula 30 60 90 Days

Formula Breakdown

F5 is the lookup_value that we are going to look up in the Category named range.

  • $J$4:$K$10 is the table_array.
  • 2 is the col_index_num.
  • TRUE is for an approximate match.
    • Output: 31-60 Days.
  • After that, press ENTER.
  • Then, we will drag down the formula with the Fill Handle tool.

Finally, we can see the Excel aging formula 30 60 90 days in the following table.

Now, we will insert a pivot table to show the Excel aging formula 30 60 90 days.

Steps:

  • First, we will go to the Insert tab >> select PivotTable >> select From Table/Range.

A PivotTable form table or range dialog box will appear.

  • Then, we will click on the upward arrow marked with a red color box to select Table/Range.

Now, we can see the Table/Range.

  • After that, we will mark New Worksheet.
  • Then click OK.

 

A PivotTable Fields window appears.

  • Afterward, we will drag down the Customer to the Rows area, Units to the Values area, and Invoice Status to the Columns area.

Finally, we can see the pivot table with the Excel aging formula 30 60 90 days.

Excel Aging Formula 30 60 90 Days

Read More: How to Use Multiple If Conditions in Excel for Aging


4. Applying Addition & Excel TODAY Function to Find Upcoming Days

Here, we will add 30 days, 60 days, and 90 days with today using the TODAY function.

Steps:

  • First, we will type the following formula in cell C6.
=TODAY()+30

Formula Breakdown

  • TODAY() → returns the date for today which is 14 June 2022.
  • TODAY()+30 →  adds 30 days with 14 June 2022.
    • Output: 7/14/2022
  • After that, press ENTER.
  • Afterward, we will type the following formula in cell C7.
=TODAY()+60

Formula Breakdown

  • TODAY() → returns the date for today which is 14 June 2022.
  • TODAY()+60 → adds 60 days with 14 June 2022.
    • Output: 8/13/2022
  • After that, press ENTER.
  • Afterward, we will type the following formula in cell C8.
=TODAY()+90

Excel Aging Formula 30 60 90 Days

Formula Breakdown

  • TODAY() → returns the date for today which is 14 June 2022.
  • TODAY()+90 →  adds 90 days with 14 June 2022.
    • Output: 9/12/2022
  • Afterward, press ENTER.

Finally, we can see the Excel aging formula 30 60 90 days.

Read More: Aging Formula with Current Date in Excel


5. Employing Subtraction & TODAY Function to Find Previous Days

Here, we will subtract 30 days, 60 days, and 90 days from today using the TODAY function.

Steps:

  • First, we will type the following formula in cell C6.
=TODAY()-30

Excel Aging Formula 30 60 90 Days

Formula Breakdown

  • TODAY() → returns the date for today which is 14 June 2022.
  • TODAY()-30 →  subtracts 30 days from 14 June 2022.
    • Output: 5/152022
  • After that, press ENTER.
  • Afterward, we will type the following formula in cell C7.
=TODAY()-60

Formula Breakdown

  • TODAY() → returns the date for today which is 14 June 2022.
  • TODAY()-60 →  subtracts 60 days from 14 June 2022.
    • Output: 4/15/2022
  • After that, press ENTER.
  • Afterward, we will type the following formula in cell C8.
=TODAY()-90

Excel Aging Formula 30 60 90 Days

Formula Breakdown

  • TODAY() → returns the date for today which is 14 June 2022.
  • TODAY()-90 →  subtracts 90 days from 14 June 2022.
    • Output: 3/16/2022
  • After that press ENTER.

Finally, we can see the Excel aging formula 30 60 90 days.


Practice Section

In the practice section of your sheet, you can practice the explained methods of Excel aging formula for 30 60 90 days.

Excel Aging Formula 30 60 90 Days


Download Practice Workbook


Conclusion

Here, we tried to show you the Excel ageing formula 30 60 90 days. Thank you for reading this article, we hope this was helpful. If you have any queries or suggestions, please let us know in the comment section below.


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Afia Kona
Afia Kona

Hello, I am Afia Aziz Kona. I graduated in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology (BUET). I have an immense interest in technical writing and content development, therefore, I am working as a content developer at Exceldemy. In my spare time, I travel, watch movies, and cook different dishes.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo