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

phownz

New Member
Joined
Jul 28, 2021
Messages
15
Office Version
  1. 365
Platform
  1. Windows
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

1634222880408.png


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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,116
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Forum statistics

Threads
1,148,008
Messages
5,744,324
Members
423,862
Latest member
ExcelUserX

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top