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]

You forgot the FALSE clause of your IF statement, in which you tell it what to return if your conditions is NOT met.
As long the dates entered into cell AA6 are really entered as dates (and not text), try this:

=IF(AA6>DATE(2025,5,31),DATE(2025,5,31),AA6)

Note that I used "DATE" instead of "DATEVALUE", as "DATE" is not subject to regional settings and should work regardless of what your regional settings might be (so its more universal).
As long as the entry made is a valid date, the format of your date is inconsequential. Formatting just affects the appearance, not the underlying value.

 

[Joe4]

Alternatively, you could also use the formula:

=MIN(AA6,DATE(2025,5,31))

 

[phownz]

Hi Joe4,

Thanks for the quick reply and explanation...

I've tried both on a new blank Excel sheet and I can't seem to get it to work. Just wanted to check I understand you correctly when you say

As long the dates entered into cell AA6 are really entered as dates (and not text)

I've attempted to type and copy and paste dates in in the format "2029-05-31" and "31/05/2029" to test but each time the value remains unchanged.

Am I missing something?

Thanks again.

 

[Joe4]

If you enter this formula in any blank cell, what does it return?

=ISNUMBER(AA6)

 

[phownz]

Hi Joe4,

It returns "FALSE"

Thanks

 

[Joe4]

Hi Joe4,

It returns "FALSE"

Thanks

In Excel, properly entered dates are stored as numbers (the number of days since January 0, 1900). So dates are just numbers with date formatting.
The fact that the ISNUMBEr function returns FALSE tells us that the entry in cell AA6 is NOT a valid number, meaning it is NOT a valid date. So your date comparison formulas will not work on it.
You have entered the data as Text. You need to convert it to numbers to do comparisons on it.

How are the values being entered into cell AA6
If you change the format of the cells AFTER the data is already in there, it will NOT convert them, unless you do something like a "Text to Columns" afterwards, choosing the appropriate date structure.

You could try accounting for it right in your formula, and see if this works:

=MIN(DATEVALUE(AA6),DATE(2025,5,31))

 

[phownz]

Hi Joe4,

I misunderstood your message before this and was doing it a blank Excel, apologies. The value now returns TRUE.

Below is how the date format appears in the cell with the formula:

=IF(AA6>DATE(2025,5,31),DATE(2025,5,31),AA6)

However if I type or copy paste "31/05/2029" or "2029-05-31" it still results in the end result being "31/05/2029"

This option is enabled for the cell:

And the date format:

Thanks

 

[Joe4]

However if I type or copy paste "31/05/2029" or "2029-05-31" it still results in the end result being "31/05/2029"

Where are you copying this from?
Does the formula:

=ISNUMBER(AA6)

still return true AFTER you type/copy this date value in cell AA6?

If you are typing the date in, are you typing it WITHOUT the double-quotes (") around it?
Are you sure that you are putting the value in cell AA6?

Lastly, do you have the calculation mode set to "Automatic" or "Manual"?
If you press F9, does the formula update to the correct value?

 

[phownz]

Hi Joe4,

Where are you copying this from?

Notepad (or simply typing)

It is still "TRUE" after the value is entered.

If you are typing the date in, are you typing it WITHOUT the double-quotes (") around it?

Yes correct, no quotes

Are you sure that you are putting the value in cell AA6?

Definitely

Lastly, do you have the calculation mode set to "Automatic" or "Manual"?
If you press F9, does the formula update to the correct value?

Automatic and nothing happens pressing F9

Are you able to replicate it successfully? Even in a blank sheet using:

=IF(A1>DATE(2025,5,31),DATE(2025,5,31),A1)

Thanks