• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1015
  • Last Modified:

Lotus Formula: Simple question on date Validation

Hi

I have to date fields which use the calendar time control, one is a Job In the other a Job Due Date, how do I set a field validation formula that prompts a user if he tries to enter an earlier date  in the Due Date field?

Many thanks
0
captain
Asked:
captain
  • 4
1 Solution
 
Bill-HansonCommented:
From the Designer Help:

"Dates can be compared and subtracted. Subtraction yields a numeric value representing seconds. To measure the difference between two dates in days, divide the result by 86,400, which is the number of seconds in a day."

So, your validation formula for the "JobDueDate" field might look something like this:

@If(
      !@IsDocBeingSaved;
            @Success;

      @Date(JobDueDate) - @Date(JobIn) < 0;
            @Failure("Job Due Date cannot be before Job In date.");

@Success)
0
 
captainAuthor Commented:
Hi Bill

Get the error "Incorrect data type for operator or @function", which is similar to what I tried using simple formula calcs earlier, do you think this is due to time control rather than using notes style.

Also this validation only runs on save I want this to run on input.

up with the points
0
 
captainAuthor Commented:
Hi Bill

oooooppppssss, typo in my field name, humble apologies.

Removed the first argument: so my formula is now:
@If(@Date(D_REQ) - @Date(D_IN) < 0;
            @Failure("Due Date cannot be before Job In date.");
@Success)

All hunky dory

Thanks
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
captainAuthor Commented:
Clarify to not get Time/Date expected error:

@If(D_REQ="";@Success;
@Date(D_REQ) - @Date(D_IN) < 0;
            @Failure("Due Date cannot be before Job In date.");

@Success
0
 
qwaleteeCommented:
This also works,  a little simpler:
@If(D_REQ="";@Success;
@Date(D_REQ) < @Date(D_IN);
            @Failure("Due Date cannot be before Job In date.");
@Success)

And this is even simpler:
@IfError(
   @If(D_REQ < D_IN; "Due Date cannot be before Job In date."; @Success);
   @Success)
0
 
captainAuthor Commented:
Thanks qwaletee, this is good help for me to understand and learn about formula language.

I am sure be back with q's soon, watch my posts

All the best
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now