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.
Read More: IF function in Excel
A hypothetical local community theater, in a certain area, runs a popular comedy show on the 18 May, every year. The bookings are usually made in advance.
The administrator in charge of ticketing and allocating seat sometimes receives 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 the 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:
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 the 28 April 2017, will receive a refund.
Read More: ASC Function(VBA) in Excel
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)
2) Press CTRL-ENTER, and then double-click and send down, since we used relative references.
3) The days between the date of the comedy show and the date the refund was requested is 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 the 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”))
6) Press CTRL-ENTER, and double-click and send down, since we used relative references.
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 show. 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 the 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
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.