How to Use the Excel DAYS Function with a Practical Example

Get FREE Advanced Excel Exercises with Solutions!

Excel has a variety of date and time functions that are very useful when working with calculations involving dates and times.

The DAYS function, not to be confused with the DAY function, returns the number of days between two dates.

Let’s get started with a simple example, in order to illustrate how to use the DAYS function.

Introduction

A hypothetical local community theater, in a certain area, runs a popular comedy show on 18 May, every year. The bookings are usually made in advance.

The administrator in charge of ticketing and allocating seats sometimes receive cancellations of bookings and subsequent requests for refunds. The theater has a policy, though, that no refunds are allowed if the refund request is received within the 20-day period before the show, no refunds will be issued. In other words, if the client cancels and requests a refund, before 28 April 2017, they will receive a refund.

The administrator has received a sheet, noting the name of the client, the invoice number, the date of the show, and the date the refund was requested. The source data is shown below:

Community Theater Cancellation and refund logging sheet in Excel

The administrator wants to calculate the days between the date of the comedy event, and the date the refund request was logged using the DAYS Function. The administrator is then going to use an IF Function, in order to determine whether or not the client is entitled to a refund based on the theater policy of: if the client requests a refund within the 20 days before the show, they will not receive a refund. However, any refund request made before 28 April 2017, will receive a refund.

1) So, let’s get started with the DAYS function.  The DAYS function has the syntax = DAYS (end_date, start_date), so one puts the later date first in other words.

In cell E6 enter the following function:

=DAYS (C6, D6)

Applying DAYS function in Excel

2) Press CTRL-ENTER, and then double-click and send down, since we used relative references.

Days function in Excel Image 2

3) The days between the date of the comedy show and the date the refund was requested are calculated and shown. If the client did not request a refund, that is recorded in the date refund requested column and thus the DAYS function delivers a #VALUE! error, since it does not have an actual start date, but a text format instead.

4) Now, in Column F, will evaluate whether a refund is allowed or not. We will use the IF Function to evaluate the three different situations. We basically have one situation, where the client did not cancel their booking and thus will still be attending the show. This was the case where the DAYS function returned the #VALUE! error. We then have another situation where a client canceled their booking, within the 20 day period directly before the show, and thus are not entitled to the refund they requested. In the third case, we have a situation where the client canceled before 28 April and thus this client is entitled to a refund.

5) So in Cell F6, we input the following formula:

=IF(ISERROR(E6),”Client, still attending show”, IF (E6<=20,”Cancellation allowed, but no refund”, “Cancellation allowed, with refund”))

Using ISERROR Function with the Days function in Excel

6) Press CTRL-ENTER, and double-click and send down, since we used relative references.

Community theater cancellation and refund log sheet in Excel Image 2

7) We then have our IF Function delivering the needed values for each of the different conditions. Namely, if the client did not cancel their booking and thus will still be attending the show (this was the case where the DAYS function returned the #VALUE! error), then the IF function delivers Client still attending shows. In the case where the client canceled their booking, within the 20 day period directly before the show, and the DAYS function gave a value of 20 or fewer days, the client would not be allowed to claim a refund. If the client canceled before 28 April (in other words the DAYS function delivered a value greater than 20 days) then they are entitled to a refund.

And there you have it!

Download Working File

Conclusion

The DAYS function can be used to calculate the number of days between two dates. It is very useful when used in combination with other functions as well.

Please feel free to comment and tell us whether you use the DAYS Function and other date and time functions that have proven useful.


Related Articles

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

Taryn is a Microsoft Certified Professional, who has used Office Applications such as Excel and Access extensively, in her interdisciplinary academic career and work experience. She has a background in biochemistry, Geographical Information Systems (GIS), and biofuels. She enjoys showcasing the functionality of Excel in various disciplines. She has over ten years of experience using Excel and Access to create advanced integrated solutions.

2 Comments
  1. Very helpful. Thanks for sharing

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo