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]

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

Both this formula and my "MIN" formula correctly return the 2025 date for me.

Try this, enter:

=DATE(2025,5,31)

in any cell and change the format of the cell to "General", and tell me what it returns.

Now, go to cell AA6 AFTER you have entered the value, and change the format of that cell to "General", and tell me what that returns.

 

[phownz]

Hi Joe4,

That's really odd but I appreciate you sticking with it... When entering the below in any cell the value once changed to "General" returns 45808

=DATE(2025,5,31)

And typing the date 31/05/2025 then changing the format of that cell to "General" returns 45808

Thanks

 

[Joe4]

I thought you were entering a different date in cell AA6, i.e.

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"

I want you to do that (changing the format to General) after you enter that value into AA6, and see what it returns.

Also, check to see if you have he "Use 1904 date system" setting checked. See: Date systems in Excel

 

[phownz]

Hi Joe4,

Correct, in sticking with 31/05/2029 the value returned is 47269

Thanks

 

[Joe4]

Correct, in sticking with 31/05/2029 the value returned is 47269

OK, those appear to be the correct values.

I will explain the purpose of having you do that. Excel actually stores dates as numbers (the numbers you listed here). It is the number of days since 1/0/1900.
So all dates in Excel really are is long numbers with date formats on them. So this last exercise was just to check to see how Excel sees those dates to see if the underlying values look correct, which they do. So that is not the problem.

I don't know if you saw it, but I had edited my previous reply. Can you please check your Date system setting, as shown in the link I provided, and tell me if it is checked?
Though it says those directions are for Excel 2007, they work for 365 too (I tried it myself).

 

[phownz]

Hi Joe4,

That makes sense and I could see why you were asking. And you are correct it appears to be working as you would expect.

I checked my date system setting (I'm on Windows) and on 1900

I've attempted to apply the same formula in the spreadsheet that I want it to work in and a blank Excel using A1 and both result in the same problem if that helps rule out that it isn't some sort of conflict within the Excel I have created.

Thanks

 

[Joe4]

I have pretty much exhausted every idea I can think of. I cannot reproduce the behavior you are reporting (the only thing left I can think of is worksheet or application corruption).
The only recourse left would be to get access to a file that is having this issue (if it happens on a new file for you, you can just share that one).

Are you able to upload a file with this issue to a file sharing site (like DropBox, OneDrive, etc) and share a link to it here for us to download and take a look at?
Be sure to specify what cell you are putting the formula in, if it is not obvious (i.e. if there is a lot of other information/formulas on the sheet).

 

[phownz]

Hi Joe4,

I really appreciate you trying to troubleshoot it. I can't work out where the conflict lies either.

Dropbox

www.dropbox.com

Let me know if you can access that link, I've just replicated the same formula on a blank Excel and get the same result whereby the value I enter seems to override it or simply the formula doesn't do any calculation.

Thanks

 

[Joe4]

You have a circualr reference!
I did not realize that you were trying to put the formula in the same cell that you are putting the value in.
You cannot do that. You can only have a hard-coded value or a formula in a cell, not both.

You would have to put the value in one cell, and the formula in another cell (they cannot be in the same cell).
If you wanted to "limit" what they can enter in a cell, you can do that using Data Validation or VBA.

 

[phownz]

Hi Joe4,

I'm so sorry for sending you on a wild goose chase with a circular reference, I thought it could be done but there may be limitations on certain formulaes. I really do appreciate the time you took to assist, glad we found the issue just a shame I overlooked this otherwise it would have been a quicker resolution.

Again, thanks for all the help! I will look into the other methods mentioned.

Thanks and have a great day/evening.