3 Replies Latest reply on Sep 6, 2013 7:41 AM by philmodjunk

    Date N/A

    hrcap

      Title

      Date N/A

      Post

           Afternoon All

            

           I currently have three fields for entering the three parts of the date; 'day' 'month' & 'year' in the following format  31 January 2013.

            

           These three fields are then compiled into a field titled: expiry date text, which then allows the step below to change the date into the format 31/01/2013.

            

           the field called called 'expiry_date'  converts the expiry date text into the format 31/01/2013. The calculation in this field is as follows:

            

            
           Let ( Month = Ceiling ( Position ( "JanFebMarAprMayJunJulAugSepOctNovDec" ; Left ( expiry Date Text ; 3 ) ; 1 ; 1 ) / 3 ) ;
                   Date ( Month ; MiddleWords ( expiry Date Text ; 2 ; 1 ) ; RightWords ( expiry Date Text ; 1 ) )
                 )

            

           I would like to add to this calculation so if the user enters N/A into the 'day' 'month' or 'year' fields it will make the expiry_date field display "N/A"

            

            

           Any help would be appreciated.

            

           Cheers

            

           Hadleigh

        • 1. Re: Date N/A
          philmodjunk

               If ( PatternCount ( Day & Month & Year ; "NA" ; "NA" ; //put your original calculation here )

               But I recommend that you also set up a calculation such as:

               If ( ISValid ( GetAsDate ( Expiry_date ) ) ; GetAsDate ( Expiry_date ) ) //I am assuming that your locality settings specify the DD/MM/YYYY format

               And select Date as the result type. That will give you a value that works better for sorting and searching by date.

          • 2. Re: Date N/A
            hrcap

                 Hi Phil

                  

                 I set up an additional field titled: expiry_date_portal and used your calculation:

                  If ( IsValid (GetAsDate(expiry_date)) ; (GetAsDate(expiry_date)) ; "N/A")

                  

                 i will now use the expiry_date_portal field in my searchesto look for expired dates.

                  

                  

                 Many thanks for your help

                  

                  

                 Hadleigh

            • 3. Re: Date N/A
              philmodjunk

                   That's not the function that I recommended. The calc I recommended leaves the field empty if the result from the other field is "N/A" as that text is not a valid date and will result in an error result.