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

# 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
• 4
1 Solution

Commented:
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

Author 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

Author 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

Author 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

Commented:
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

Author 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.