3 Replies Latest reply on May 15, 2012 9:35 AM by Sorbsbuster

    Validate Date / Time



      Validate Date / Time


      I have a table that has four date & four time fields, corresponding with the stages of a job. Let's call them Stage 1, Stage 2, Stage 3 & Stage 4.


      I need to ensure that the stage Stage 4 date & time >= Stage 3 date & time, stage 3 date & time >= stage 2 date & time & so on. I can validate by calculation that one date is >= to another but I can't work out how to do it in conjunction with a time. Can anyone point me in the right direction?



        • 1. Re: Validate Date / Time

          I suggest using time stamp fields instead of separate date and time fields. By recording both the date and the time in the same field, your validation calcualtions will work.

          Otherwise, your time validations calculations get a bit complex

          Stage 2 time would validate like this:

          Stage 2 Date > Stage 1 Date OR Stage 2 Time > Stage 1 Time

          • 2. Re: Validate Date / Time

            Haven't played around too much with validation (Will check on it later today if I have time), but for calculation you'd probably want to use nested "if" statements (multiple if statements in an if statement, such as: If((Stage 4 Date >= Stage 3 Date) and (Stage 4 Time >= Stage 3 time));(if((Stage 3 Date >= Stage 2 Date) and (Stage 3 Time >= Stage 2 Time));(if((Stage 2 Date>= Stage 1 Date) and (Stage 2 Time >= Stage 1 Time)));[result 1]);[result 2]) (I'm probably off by a paranthese or so, but I hope that conveys the general information.

            • 3. Re: Validate Date / Time

              From a purely practical point of view validation may not be the best way.  Sometimes in scheduling it is better to do the 'rough cut' and then give the user the exception report of those jobs that are 'impossible'.  The reason I suggest that is when you are banned from entering the final Stage 4 Date and time because it is before Stage 3 Time, it may be that you don't want to put in a 'correct' date and time for Stage 4 as it is needed for delivery for that date - what you want to do is enter the 'impossible' Stage 4 time and then push the Stage 3, 2, and 1 times back up the pipeline.  Or not.  That's why it often needs to be a human decision, not simply a computerised banning of 'invalid' dates.

              Just a thought.