Download the featured file here (including completed formula) excels-if-function.com/2020/03/02/if-statement-between-two-dates/
This video demonstrates how to write an IF statement where you want to test for dates that occur between two dates. The video features three functions: IF, AND and DATEVALUE.
The video looks at three ways you can achieve the IF statement result for date ranges.
This video will be useful to you if you are asking the following questions:
How do you write an IF statement between two dates?
How do you use IF in a date range?
How do I return a value if date within range?
If date is between two dates then how do I return a value?
How do I use an IF function in Excel for dates?
How do you find the value if the date falls between two dates?
All enrolments and purchases help this channel (a commission is received at no extra cost to you):
😎5 FREE Excel Templates😎
😎Enrol in the Excel University – FREE taster course available😎
😎Get 30% Discount on Simple Sheets Templates and Courses
Use Discount Code BLUE😎
Hi in this video we're going to perform an if statement that is going to have a logical test, but it's going to ask the question is a date between two dates: is it within a date range? So here's my date range here between the 9th of March and the 13th of March and in this column, I just want to refer yes or no to that.
I just want to return yes or no to that question, but it's going to look at three different ways of doing this.
This test is actually to test because you're asking whether the date is equal to or after the ninth of the third or is it equal to or before the 13th of the third now to run two tests simultaneously, which is what we're gonna have to do here, we're going to use the and function the N function returns.
True, if all of its tests are map, so the first test logical one would be- is this date greater than or equal to the start date and I, wouldn't lock that reference, the F, Corp f4 key, because up-sell I'm going to copy this formula down? The second test would be: is this date less than or equal to the end date? Okay and then I'm going to close the bracket.
So I'm asking two questions and the end statement will only return true if both of these tests amount.
So if I copy this down, you can see that I get a true where ever I get a date in that date range now to convert these to yeses and noes or whatever you want to do.
You'd put the own statement within an if so the and becomes the logical test, and your value of true could be whatever you like, I'm just going to say yes and I don't force it's going to be no again, that could be whatever you like.
So if I copy that down around highlights the ones that are relevant now, you could also use this logical test as the basis of conditional formatting.
It's another way of achieving the same thing really so I'm just going to copy that logical tape and let's select these dates, then we go up to conditional, formatting and going to go to new rule and you use a formula to determine which cells to format- and it says, format, cells where this formula is true, I pasted it in a formula and type in equals, and then I choose a format.
So once we just choose, maybe background color and a different font, color, okay and it highlights those states and obviously, if I change these dates up here we change which which cells got the highlight which I've got the format now I would say this version of the.
If statement is the preferred version, because what you're not doing is hard coding dates into your formula or a phone to dates within cells so as much it makes it much easier.
If you eventually need to change your dates, you can just change them in these cells, and your formulas will automatically update.
Now there may be scenarios where that's just not possible and you need to hard code.
Your dates into your formula now.
This is where it gets a little bit tricky so well, look how this would work.
So what I'm going to do is I'm going to say.
Is this thing greater than or equal to you and you'd think that what you could do is maybe just type in the date like that is the is this date greater than or equal to 9th of the 3rd 2020, and that really won't work, because what Excel will actually test is whether the date in a2 is greater than or equal to 9 divided by 3 by 2020, which will always be true.
So one way to get around this is to use a function called date value and what that does is return, the serial number for a date that you specified rather than doing this funny division, so that will work properly.
So if I then said, is this state less than or equal to date fail? You referred in 2020, okay and then I need another close bracket at the end there for the end, so I'm using the date value to return.
The serial number of these dates notice how the dates have to go in quotation marks.
So, if I copy that down you'll see that it works in the same way and also I can put that within the new return, yes and no struggling with the caps lock today, if some known- and it would do the proper thing there- one other way, which is just worth knowing how of interest in my end again now this time, what I'm going to do is I'm going to say, is the state greater than or equal to I'm going to put the date directly into the formula, but in quotation marks, but this time I'm not using day value, essentially the date you value function.
What that did is it returned the serial number behind the day, every date that you store in Excel as a serial number behind it? The numbering starts from the first for the first 1900, which is equal to one now to convert this date into a number.
What you do is you add a zero and I'll do the same thing for the other day, so I'll say: is it less than or equal to 13 to the 3rd, 2020 plus 0 copy that down I get my trees and my forces all won't convert it to an F bit.
You get the idea.
This is your best bet.
If you can use this, if you can refer to cell dates within cells, that's by far the best formula, but if you are forced to hard-code the date in you're, gonna have to either use the date value function as I've shown there or just refer to the date in quotation marks and add a 0, and then it will work.
Okay, that's all this video is going to cover.
Thank you very much for listening and don't forget to subscribe.
If you find these tips and tricks' useful.