IF Statements with Dates

Question

Date in cell A5 is 6/29/22Need an IF statement that compares the date field in about 200 rows to this date.Argument would be if Date in cells C10 thru C210 is greater than the date in cell A5, then convert to 7/31/22In short, I want all dates = to 6/30/22 (or alternatively greater than 6/29/22) converted to 7/31/22.&nbsp;Thank you

mathetes · Answer

BobWhiteJax&nbsp;&nbsp;If I understood you correctly, this formula (also in the attached spreadsheet) will do what you're seeking.=IF(C10&gt;$A$5,DATEVALUE("7/31/22"),C10)Just copy that down in the column adjacent to column C&nbsp;I was a little confused by this statementIn short, I want all dates = to 6/30/22 (or alternatively greater than 6/29/22) converted to 7/31/22.and am assuming you meant&nbsp;&nbsp;&lt;6/30/22&nbsp;In any event, it's easily modified.

bobwhitejax · Answer

Thanks. No - don't want all dates &lt; than 6/30/22 as this a last depreciation date. If for instance the date in a cell is 4/30/22, it needs to stay at that date. More precisely is I want any date = to 6/30/22 to advance to 7/31/22. That is why I suggested &gt; than 6/29/22 as that can only be the 6/30/22 cells. Thank you.

mathetes · Answer

BobWhiteJax&nbsp;&nbsp;As I said, the formula I gave is easily modified to do what you want. Did you look at the spreadsheet I attached?&nbsp;Frankly I still find your words confusing. For example.No - don't want all dates &lt; than 6/30/22 as this a last depreciation date. If for instance the date in a cell is 4/30/22, it needs to stay at that date.The formula I gave you does do what your second sentence says; I don't understand what your first sentence says, especially the "don't want all dates &lt; 6/30/22" part. Do you or don't you? Anyway, the syntax of the formula I gave you will work.More precisely is I want any date = to 6/30/22 to advance to 7/31/22. That is why I suggested &gt; than 6/29/22 as that can only be the 6/30/22 cells.Here's your original, speaking of "about 200 rows"Need an IF statement that compares the date field in about 200 rows to this date.Argument would be if Date in cells C10 thru C210 is greater than the date in cell A5, then convert to 7/31/22The logical inference from this original is that there's a range of dates, some before, some after 6/29/22. Any after (i.e., greater than) are to be converted to 7/31/22.&nbsp;THAT is what the formula does. If that's not what you want, or if the data set in fact has no date greater than 6/30/22...but perhaps a whole bunch of them...the formula will still work.&nbsp;Or it's still not clear what you're looking for.

argylecs · Answer

mathetes&nbsp;I'm attempting to do a business plan with rent increase in DecemberI've tried using IF cell = (or &lt;&gt;?), another cell (which is a VLookup result, then&nbsp; multiple by another cell in another spreadsheet with variables.eg:=IF(J8=DATEVALUE("1/12/24"),J33*Variable!H13,"")so, J8 is the result of a VLookup; and J33 is the current rent; and Variable!H13 is the amount of rent increase each December....Am I in fairy land or can this be done?

mathetes · Answer

argylecs&nbsp;&nbsp;Am I in fairy land or can this be done?&nbsp;I see no reason why it can't be done. The more important question: have you tried it?&nbsp;Especially at fairly basic levels, Excel is reasonably intuitive. And the folks who created it did a very thorough job. A rule of thumb I came up with when first learning spreadsheets was, "If I think thus and so should be possible, it is 99.99% likely that it is possible; I just need to find how they programmed it." Given that rule of thumb, the answer to your question "can this be done" is a resounding "Yes!"&nbsp;The challenge is to figure out how.The&nbsp;function names generally are descriptive of what they do.&nbsp;One of the most important things a person can learn about learning Excel is that you can always try, try again, looking at each component in a multiple component formula separately, etc. In other words, give yourself permission to experiment. You're not going to break anything.&nbsp;If you still want help, rather than to figure it out, come back and ask another question, but if at all possible, please provide a copy of&nbsp;the actual worksheet you've created--posting it on OneDrive or GoogleDrive with a link pasted here that grants access. Even if it doesn't work, you'd be showing us how you've arrayed your information, which often gives an indication of the best way to approach it.&nbsp;(By the way, another truth of Excel that you'll learn as you progress: there are always multiple ways to get from A to B in Excel. In other words, there is not only one workable way to accomplish what you're wanting to accomplish, not only one "correct" answer. Part of the fun of Excel is realizing the beauty of several solutions, some more elegant than others, but still...more than one way to get from A to B.)

Forum Discussion

IF Statements with Dates

5 Replies

Resources