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

    Date N/A



      Date N/A


           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.





        • 1. Re: Date N/A

               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

                 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




            • 3. Re: Date N/A

                   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.