Excel formula IF DATEVALUE is greater than, then use default date otherwise do nothing

[phownz]

Hi all,

I've been struggling to find/create a formula that will take a date entered into a cell (I assume this will be copy and pasted if that makes a difference) If the date is greater than a specific date, lets say 2025-05-31, I would like that set as a top limit and return the date "2025-05-31" if however it is not greater than that date I would like no action and the date entered to remain.

I tried a couple of things with regards to using =DATEVALUE, I've since wiped my test formulas but I think it was something along these lines:

=IF(AA6>DATEVALUE("2025-05-31"), DATEVALUE("2025-05-31"))

This is how my cells are formatted

But so far I've not been successful apart from having the input date remain (whether higher than the default date limit or not)

If you can point me in the right direction, that would be appreciated.

Thanks in advance.

 

[Joe4]

It is not a limitation on a certain formula, it is more how Excel works - you cannot put both formulas and hard-coded values in a single cell at the same time.
Also, you usually do not want circular references (formulas in cells that reference the cells the formula is actually located in), unless you are intentionally during it using iteration.

If you have any issue applying the other methods, just let us know and we can help you out.