If Then Statement with Dates and Vlookup

[cmschmitz24]

I have a date being vlookup'd into column H from a different spreadsheet and then I need to determine if that date is greater than or less than 04/01/21 in column J. If it's older than 04/01/21, I need to have column J return 04/01/21. If it's newer than 04/01/21 then I need to have column J return the date vlookup'd into column H. My formulas are not calculating correctly. Please help.

The formula I have in column J is: =IF(H2>DATEVALUE("04/01/2021"),H2,"04/01/2021")

However, it works for the first row, the next rows are not returning the correct date wanted. Here's an example of what I'm getting -

row 2, column J should return 06/01/2021
rows 3-5, column J should return 04/01/2021

 

[Fluff]

How about

+Fluff 1.xlsm
	H	I	J
1
2	01/06/2021		01/06/2021
3	01/01/2021		01/04/2021
4	01/09/2020		01/04/2021
5	01/07/2021		01/07/2021
Summary

Cell Formulas
Range	Formula
J2:J5	J2	=MAX(H2,DATE(2021,4,1))

 

[cmschmitz24]

How about

+Fluff 1.xlsm
	H	I	J
1
2	01/06/2021		01/06/2021
3	01/01/2021		01/04/2021
4	01/09/2020		01/04/2021
5	01/07/2021		01/07/2021
Summary

Cell Formulas
Range	Formula
J2:J5	J2	=MAX(H2,DATE(2021,4,1))

I assume that is in a DD/MM/YYYY format? The format I have above is MM/DD/YYYY.

 

[Fluff]

Yes the dates are dd/mm, but the formula should work regardless.

 

[cmschmitz24]

Hmm that isn't pulling in the correct date. Is it because there is a vlookup formula in column H?

 

[Fluff]

The Vlookup shouldn't make a difference, try formatting col H as general, do you still see dates?

 

[cmschmitz24]

I still get the same results when changing to general.

 

[Fluff]

But does col H look like, is it dates or 5 digit numbers?

 

[cmschmitz24]

Column H looks like dates.

 

[Fluff]

In that case they are text & not real dates. The best option would be change them to real dates in the column that the vlookup is pulling them from.