0

I'm having formula and logic trouble getting a list validation formula that will force the user to enter four dates in sequential order. I have Date1, Date2, Date3, and Date4. I also need to skip a calculation if a "later" date is blank.

I would be grateful for help. Thanks-

3
  • Could you provide example for "skip calculation"? Are these valid entries: [09/22/2020, 09/23/2020, Blank, 09/24/2020]; [09/23/2020, Blank, 09/24/2020, Blank]? – Matiur Rahman Sep 22 '20 at 17:13
  • Hi Matiur. I am sorry for the confusion. I need the validation to only work (for example) on Date2 when Date1 has a date, or on Date3 when Date2 has a date. So, if the previous date field has a data value, the most recently filled date in the sequence gets the validation. Thank you! – ChasEpes Sep 22 '20 at 21:15
  • posted my answer. Let me know your feedback or if you have any questions – Matiur Rahman Sep 27 '20 at 18:32
1

Try the following formula and see if that works.

=IF(AND(NOT(ISBLANK(Date1)),NOT(ISBLANK(Date2))),IF(DATEDIF(Date1,Date2,"D")=1,IF(AND(NOT(ISBLANK(Date2)),NOT(ISBLANK(Date3))),IF(DATEDIF(Date2,Date3,"D")=1,IF(AND(NOT(ISBLANK(Date3)),NOT(ISBLANK(Date4))),IF(DATEDIF(Date3,Date4,"D")=1,TRUE,FALSE),TRUE),FALSE),IF(AND(NOT(ISBLANK(Date3)),NOT(ISBLANK(Date4))),IF(DATEDIF(Date3,Date4,"D")=1,TRUE,FALSE),TRUE)),FALSE),IF(AND(NOT(ISBLANK(Date2)),NOT(ISBLANK(Date3))),IF(DATEDIF(Date2,Date3,"D")=1,IF(AND(NOT(ISBLANK(Date3)),NOT(ISBLANK(Date4))),IF(DATEDIF(Date3,Date4,"D")=1,TRUE,FALSE),TRUE),FALSE),IF(AND(NOT(ISBLANK(Date3)),NOT(ISBLANK(Date4))),IF(DATEDIF(Date3,Date4,"D")=1,TRUE,FALSE),TRUE)))

Screenshot showing valid entries

enter image description here

2
  • Hi Matiur: The good news is that The validation wondows accepts the code you wrote so that great! The bad news is I'm getting a "Exception from HRESULT: 0xE0040035". I am checking it with our admins but nonetheless, thank you so much. This is brilliant! – ChasEpes Sep 28 '20 at 16:25
  • Hope this works out for you. Only thing I've noticed is that you might see a syntax error when [end-date] is < [start_date] in DATEDIF(start_date,end_date,unit). But this still stops user from submitting the form. Let me know if you have any questions or need any explanation of the formula. – Matiur Rahman Sep 28 '20 at 17:18
0

I don't think it's possible to achieve your requirement by list validation.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.