DAYS Function
It is categorized as a date and time function that returns the number of days between two dates.
What is the Excel DAYS Function?
The DAYS function in Excel calculates the number of days between two dates, providing a simple way to determine the duration or time gap between them.
This handy little function will do all the heavy lifting for you, providing the number of days between any two dates with just a few simple clicks.
But the function isn't just for basic date calculations. Need to know how many days it's been since your last haircut? Want to find out how many days until your next vacation? The DAYS function has got you covered.
So why wait? Start using the function today and say goodbye to tedious date counting. Trust us; your fingers (and toes) will thank you.
Key Takeaways
- The DAYS function returns the difference between two given dates in terms of the days that separate them.
- The function makes it much easier to determine the days until deadlines if the target date is known. For example, if two dates exist in the dataset, the DAYS function can be used.
- The DATEDIF is another function that can find the difference between two given dates. Unfortunately, the function was last documented in the Excel 2000 version. However, it is still available for compatibility use.
- To return the difference in days, months, and years, we use ‘d’, ‘m’, and ‘y’ initials inside the quotation marks of the DATEDIF function.
Understanding the DAYS function
This function in Excel is categorized as a date and time function that returns the number of days between two dates.
For example, if the dates are January 1, 2023, and January 14, 2023, then the function returns the number of days between them as 13.
A single year has 365 days in general. So, the function grabs the numerical equivalent of the date, 1 and 14, respectively. Then the function subtracts those numbers to give the result as 13 finally.
The syntax for the function is:
=DAYS(end_date, start_date)
where,
- end_date - (required) the end date
- start_date - (required) the start date.
The result will be a positive number if the end_date is later than the start_date and a negative number if the end_date is earlier than the start_date.
For example, if you wanted to find the number of days between January 1, 2020, and January 10, 2020, you could use the following formula:
=DAYS("1/10/2020", "1/1/2020")
This would return 9, as there are nine days between these two dates.
Note that the DAYS function only considers the number of days and does not consider the time of the day or the time zone. If you want to calculate the number of days, hours, minutes, or seconds between two dates, you can use the DATEDIF function.
How to use the DAYS Function in Excel?
The dates used in the DAYS function can either be a hardcoded value or a direct cell reference. If you regularly work on a large amount of data, you might prefer referencing the date values instead of hardcoding them.
To use the DAYS function in Excel, follow these steps:
1. Open your Excel spreadsheet and select the cell where you want to display the result of the DAYS function.
2. Type the following formula into the selected cell: =DAYS(end_date, start_date)
Replace "end_date" with the cell reference or date for the end date and "start_date" with the cell reference or date for the start date.
For example, if you wanted to find the number of days between December 22, 2022, and January 3, 2023, and the dates are in cells C2 and C3, respectively, you could use the following formula:
=DAYS(C3, C2)
3. This would return 12, as there are twelve days between these two dates.
4. If the dates changed, say 1st December 2022, in cell C3, the result automatically becomes
5. As stated earlier, if the end_date is before the start_date, we get a negative number for the DAYS function.
Examples of the DAYS Function in Excel
This section will show examples of using the function in real-life scenarios. Just remember that if there are two or more dates available in a given dataset, you can use the DAYS function without a doubt.
Example #1: Suppose you are evaluating the portfolio of stocks and need to determine how many days it has been since buying each of those stocks.
The data looks as illustrated below:
We will use the formula =DAYS(TODAY(),C3) in cell D3 and drag it down till cell D7, which gives the result:
Using the function, we see that it has been almost a month since we last purchased the Tesla stock.
The TODAY() function can automatically return the current date and assign it to the end_date argument. On the other hand, the start_date becomes the purchase date in column C.
However, since the TODAY() function is highly volatile, ensure that you use Ctrl + C and Ctrl + Alt + V and then Paste Special to avoid the change in value the next day when you open the file.
Example #2: A teacher must evaluate the additional days students take to submit their assignments. The data looks as below:
Firstly, we are evaluating the days on two different questions -
- Is the submission date beyond the deadline date?
- How many days beyond the deadline date is the submission date?
To answer both questions, we will use the formula =IF(DAYS(D3,C3)<0,-DAYS(D3,C3)&" days beyond deadline", "submitted on time"), which gives the result as
The formula can be understood by breaking it down into three steps:
- Firstly the DAYS(D3,C3) > 0 evaluates all the days wherein the number is negative. We have set the deadline date as the end_date while the submission date is start_date. If the start_date is later, then it will yield a negative number.
- Next, we will add the formula inside the IF statement so that if the mentioned condition is fulfilled, Excel performs A task or else performs B task.
- Finally, we will use a bit of concatenation for the TRUE argument and a customized text string for the FALSE argument.
Example #3: Let’s understand the function with a simple final example. Suppose you are planning a trip to the Bahamas. You are leaving on 24th January 2023 and expect to return by 8th February 2023 back to America.
How many days has it been since you went on the trip?
To answer that simple question, we will use the formula =DAYS(C3,B3), which gives the result:
This means that it will be a 15-day trip to the Bahamas, after which you expect to return to America.
What is the DATEDIF Function?
The function that comes closest to the functionality of the DAYS function is the DATEDIF. It derives its name from the longer version ‘Date Difference’ wherein the bolded letters are concatenated to derive the function’s name.
Wait, did you say you couldn’t find DATEDIF in the function’s library? Even in the compatibility section?
The thing is that DATEDIF was replaced by a newer version, its counterpart DAYS, and was documented only in the Excel 2000 version. However, it is still available to users in all subsequent versions of Excel.
The DATEDIF is primarily categorized as a Date and Time function that returns the difference between two dates in the year, months, or even dates.
For example, if the two dates are 14th January 2023 and 28th January 2025, and you need to determine the difference between the two dates in terms of months, then the function returns the result as 24.
Similarly, the result in terms of year and days would be 2 and 745, respectively. But, again, this means two years or 745 days, separating the two given dates.
It seems like we have piqued your interest, and you want to know how the function works. It's rather quite simple; just ensure that you know the syntax for the function since Excel won't help you with predefined functions.
The syntax for the function is
=DATEDIF(start_date, end_date, unit)
where
- start_date - the starting date
- end_date - the ending date
- unit - this argument determines in what unit the result would be returned, i.e., days, months, or years.
For example, assume we need to find the difference between two given dates in all three different time units. Then, the data looks as illustrated below:
To get the difference between the two dates in days, we will use the formula =DATEDIF(B3,C3,"d"), which gives the result as 287 days.
Similarly, to find the difference in months and year equivalent, we will use the formula as =DATEDIF(B3,C3,"m") in cell E3 and =DATEDIF(B3,C3,"y") in cell F3, respectively to give:
What changed was the unit mentioned as the initial character inside the quotation marks. For example, ‘y’ stands for a year, whereas ‘d’ stands for the day.
The number of months that separate the two dates is nine, whereas the date is separated by zero years since both dates fall in the same year, 2023.
Conclusion
Excel's Days feature gives users an effective tool for organizing dates and completing calculations quickly.
Excel's DAYS function proves to be invaluable for users in a variety of industries and professions, whether they are tracking project deadlines, managing financial calendars, or just calculating durations.
Through proficiency with this feature, users can improve productivity, optimize workflows, and make well-informed decisions supported by precise date-based insights.
To guarantee accurate findings, it is crucial to be aware of potential hazards, such as taking leap years into account or comprehending the constraints of date formats.
Excel is still an essential tool for managing and analyzing data as technology develops, and its powerful features—such as the DAYS function—help to explain why it is still relevant in today's digital world.
Free Resources
To continue learning and advancing your career, check out these additional helpful WSO resources:
or Want to Sign up with your social account?