IF Statements with Dates

Occasional Contributor

Date in cell A5 is 6/29/22

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/22

In short, I want all dates = to 6/30/22 (or alternatively greater than 6/29/22) converted to 7/31/22.

 

Thank you

3 Replies

@BobWhiteJax 

 

If I understood you correctly, this formula (also in the attached spreadsheet) will do what you're seeking.

=IF(C10>$A$5,DATEVALUE("7/31/22"),C10)

Just copy that down in the column adjacent to column C

 

I was a little confused by this statement

In 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  <6/30/22

 

In any event, it's easily modified.

Thanks. No - don't want all dates < 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 > than 6/29/22 as that can only be the 6/30/22 cells. Thank you.

@BobWhiteJax 

 

As I said, the formula I gave is easily modified to do what you want. Did you look at the spreadsheet I attached?

 

Frankly I still find your words confusing. For example.

No - don't want all dates < 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 < 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 > 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/22

The 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.

 

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.

 

Or it's still not clear what you're looking for.