How to Calculate Days with Aging Formula in Excel

Get FREE Advanced Excel Exercises with Solutions!

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.


Aging Formula in Excel to Calculate Days: 2 Practical Cases

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.


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.


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.

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

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


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


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.

Download Practice Workbook

You can download the practice book from the link below.


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.


<< Go Back to Ageing | Formula List | 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.
Rafiul Hasan
Rafiul Hasan

Rafiul Hasan, holding a BSc in Naval Architecture and Marine Engineering from Bangladesh University of Engineering & Technology, contributes significantly to the ExcelDemy project with almost 1.6 years of dedicated work. Currently an Excel and VBA Content Developer, he has a passion for problem-solving. Authoring over 100 articles for ExcelDemy showcases expertise in Microsoft Office Suites and Data Analysis. In addition to content development, Rafiul actively engages with the ExcelDemy forum, offering valuable solutions to user queries and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo