Nela817

New Member
Joined
Feb 24, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows
Hello,
I am trying to create an expression that will control a users date input in reference to a pervious field.

This is what I currently have: [Field 1]<=[Field 2]<=[Field 3]<=[Field 4]<=[Field 5]

Field 1 has an expression/Validation rule of: <=Now()

Essentially, I want the first field to have a date input of today or earlier (which I have), then each field after that to have a the same rule but instead of the 'Now' I want it from the previous date that was inputted. The above long expression does not work, and I do not know how to fix this.

So example if today I finished field 1, I would put today's date, if I finished field 2 today, I would put todays date, HOWEVER field 2 can not be finished before field 1 (same goes for the rest of the fields). So all fields can be finished on the same day or later from each other. BUT not in the future, so today I am on field 2 and I anticipate finishing tomorrow, but I have to wait till tomorrow to put the date in.

Please help!
 

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
3,302
Office Version
  1. 365
Platform
  1. Windows
I am trying to place this in a table.

Then the answer is in post 3 where I asked where you were doing this. I also said this would work in form code; you'd have to use the syntax from post 2.
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  1. Windows
Would it be better to do this as a form rule?
I'm still new with this, I appreciate your patience.
Yes I think as Micron explained, table validation rules won't let you compare fields. It would be better to have a form for inputting data and add your logic to the form. Form validation is a topic you can find many examples of if you search, but in short, you can create a Form_BeforeUpdate() event that tests the important logic and can cancel the update is the rules aren't met. Or you can create a Control_AfterUpdate() event on the individual fields that tests the logic and provides similar functionality to prevent invalid data. Or both.

A simple rule that might work is just to say that all these fields cannot be entered with data earlier than the current date. Then you wouldn't be able to backdate any data (unless some special way to override is provided) but it would ensure that the latest dates are always newer dates. Again, that would probably have to be done in a form as a form validation rule for data input.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,043
Messages
5,835,099
Members
430,341
Latest member
kelstnmate

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