Rafiul Hasan
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:
- Between two specific dates
- 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.
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
- 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
- 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
- Then, press ENTER and drag the formula down to get the output for all cells.
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
- 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.
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
- Now, drag the formula down and all the cells will return the result.
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
💡 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!
