How to Calculate Days with Aging Formula in Excel

Looking for a way to calculate days in Excel? Excel has several built-in functions to calculate days. This tutorial will give you a clear idea about the functions and their uses. In this article, I will show you how to calculate days with the aging formula in Excel.


Download Practice Workbook

You can download the practice book from the link below.


2 Practical Cases to Calculate Days with Aging Formula in Excel

There are two distinct situations where you may need to calculate days:

  1. Between two specific dates
  2. Between the current date and a specified date

In this section, you will find the 2 most practical cases to calculate days with the aging formula in Excel. I will demonstrate them one by one here. Let’s check them now!


1. Calculate Days from a Specific Date

Let’s say we have a sample dataset of names of some customers who have ordered some products from a shop, the order date, and the delivery date.

Dataset to calculate days with aging formula in excel

We want to calculate the days taken to deliver the products to the customers. Here, we have to calculate the days between these specific two dates. Let’s check the ways we can perform this task.


1.1. Subtracting Days

We can calculate the days between these two dates just by subtracting the following date from the previous date. Let’s proceed with the following steps.

📌 Steps:

  • First of all, create a new column, select a cell and apply the following formula to the selected cell.

=D5-C5

Here,

  • D5 = Delivery Date
  • C5 = Order Date

Subtracting to calculate Days with aging Formula in Excel

  • Then, drag the Fill Handle tool to Autofill the formula down to the next cells and all the cells will calculate the days taken for delivery and return the results.

Read More: How to Do Aging Analysis in Excel (with Quick Steps)


1.2. Calculate Using DAYS Function

The DAYS function returns the number of days between two specific days. So, for the sample dataset, applying the DAYS function will serve your purpose of calculating the days taken to deliver the product to the customers.

Follow the steps below.

📌 Steps:

  • Firstly, select a cell and type the following formula into the selected cell.

=DAYS(D5,C5)

Here,

  • D5 = Start Date
  • C5 = End Date

DAYS function to calculate Days with Aging Formula in Excel in

  • Then, press ENTER and drag the formula to the next cells to copy the formula and calculate the days.

Read More: How to Use Multiple If Conditions in Excel for Aging (5 Methods)


1.3. Using DAYS360 Function

The DAYS360 function returns the days between two dates based on a 360 days year. So, it takes every month of a year as a 30 days month. When you calculate the difference of days between two dates of a month of 30 days, you can apply this function. In order to do so, just proceed with the steps below.

📌 Steps:

  • Firstly, select a cell and type the following formula into the selected cell.

=DAYS360(C5,D5)

Here,

  • D5 = Delivery Date
  • C5 = Order Date

DAYS360 function to calculate days with Aging Formula in Excel

  • Then, press ENTER and drag the formula down to get the output for all cells.

Note: This function calculates days on the basis of 30 days/month. If you try to calculate the difference between the 1st and 31st day, it will show a little error and it will return 30.

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


1.4. Applying DATEDIF Function

The DATEDIF function allows a user to calculate the difference between two dates in any unit (Days/Months/Years). The arguments needed to implement this function are start_date, end_date, and unit. Let’s start the process like the one below.

📌 Steps:

  • First of all, type the following formula in a selected cell.

=DATEDIF(C5,D5,"D")

Here,

  • D5 = Delivery Date
  • C5 = Order Date
  • “D” = Unit in Days

DAYS360 function to Calculate Days with Aging Formula in Excel

  • Then, press ENTER and the cell will return you the result.
  • Now, drag the formula down and all the cells will calculate the days between the two dates and return you the output.

Note: After applying the function, the cell may show the result in date format. In this case, you need to change the format of the value.

Read More: Aging Formula in Excel Using IF (4 Suitable Examples)


1.5. With YEARFRAC Function

The YEARFRAC function generally calculates the fraction of the year described by the number of whole days between two days on the basis of the day count (360/365). In order to apply the function to calculate days between two days, proceed like the steps below.

📌 Steps:

  • At first, type the following formula into a cell where you want to calculate the difference of days.

=YEARFRAC(C5,D5,3)*365

Here,

  • D5 = Delivery Date
  • C5 = Order Date
  • 3 = Actual/365 day count basis

  • Then, press ENTER to let the cell show the result.

 💡 Formula Breakdown

YEARFRAC(C5,D5,3) returns the actual year considering 365 days as the basis is used 3 (365 day count basis).

Output=> 0.0356164383561644

YEARFRAC(C5,D5,3)*365 = 0.0356164383561644*365 will convert the year into days.

So, final Output=> 13

Note: After applying the function, the cell may show the result in date format. In this case, you need to change the format of the value.
  • Now, drag the formula down and all the cells will return the result.

YEARFRAC Function to Calculate Days with Aging Formula in Excel


2. Aging Formula to Calculate Days from Current Date

Another case for calculating days when one date is specified and another date is the current date. Let’s say, in our previous dataset, if the product is delivered today, the scenario would be like the following screenshot below.

Now, we will see two more different approaches for calculating days between a specified date and the current date.


2.1. Applying TODAY Function

The TODAY function returns the current date of today. In order to calculate days using the TODAY function, proceed like the steps below.

📌 Steps:

  • Firstly, select a cell and type the following formula into the cell.

=TODAY()-C5

Here,

  • C5 = Order Date

  • Here, as the TODAY function returns the current date, subtracting the specified date from this function results in the difference between them.

Read More: Applying Formula to Find Defect Aging in Excel (with Easy Steps)


2.2. Combining YEARFRAC and TODAY Functions

A combination of YEARFRAC and TODAY functions will also allow a user to calculate days between two dates. Just apply the following formula to serve your purpose.

=YEARFRAC(C5,TODAY(),3)*365

Here,

  • C5 = Order Date
  • 3 = Actual/365 day count basis

Combining YEARFRAC & TODAY Function to Calculate Days in Excel

 💡 Formula Breakdown

TODAY() function returns the date of today.

YEARFRAC(C5,TODAY(),3) returns the actual year considering 365 days as the basis is used 3 (365-day count basis).

YEARFRAC(C5,TODAY(),3)*365 will convert the year into days.

So, final Output=> 26

Read More: How to Make Inventory Aging Report in Excel (Step by Step Guidelines)


Things to Remember

  • Change the format of the cell after applying the formula if the cell shows the result in date format.
  • Use the DAYS360 function for the month that consists of 30 days.

Conclusion

In this article, I have tried to show you some methods to calculate days with the aging formula in Excel. I hope this article has shed some light on your way. If you have better techniques, questions, or feedback regarding this article, please don’t forget to share them in the comment box. For more queries, kindly visit our website ExcelDemy. Have a great day!


Related Articles

Rafi

Rafi

Hey there! I am Md. Rafiul Hasan. Currently I am working as an Excel & VBA content developer. I like new ideas and want to explore the field of innovation. Excel has saved our worktime and made it easy for us to quick calculations. I am trying to make it easier for you to overcome the obstacles you face while working on Excel. Stay connected!

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo