5 Replies Latest reply on Apr 16, 2013 3:16 PM by philmodjunk

    Converting Text to Dates

    bterrell721

      Title

      Converting Text to Dates

      Post

           Hello All,

           Just need some quick help coming up with a functioning calculation for a particularly tricky conversion (at least for me!).  I have a text field that represents dates either in the MM/DD or MM/DD/YYYY formats (single or double digit months and days could be present) and I need to create a new field that is a calculation resulting in a Date that is five days prior to the one listed in the original text field using the current year.  

           For example:  

           Text: 4/21 should convert to Date: 4/16/2013

           Text: 04/02/1989 should convert to Date: 3/28/2013

           etc.

           Thanks in advance for assistance on making this happen for me!

        • 1. Re: Converting Text to Dates
          philmodjunk

               If I were you, I'd replace that text field with a field of type date. It will make for more consistent data entry. There's a real possibility here in your text data, that one or more records will have invalid data. You can use a date field even if the year is not significant. Once you have a date field much simpler calculations can compute the date  that is "5 days prior".

               Let ( [ td = YourTable::YourTextField ;
                         p = Position ( td ; "/" ; 1 ; 1 ) ;
                         m = Left ( td ; p - 1 );
                         d = GetAsNumber ( middle ( td ; p + 1 ; 2 ) )
                        ];
                        If ( p ; Date ( m ; d ; year ( Get ( CurrentDate ) ) ) - 5 ; "error" )
                       )

               Specify date as the result type. If there is no / in the text field, a question mark will be returned.

               but data entry errors can be an issue here. 2/30, for example, will produce a result of: 2/25/2013

          • 2. Re: Converting Text to Dates
            bterrell721

                 Perfect, as always Phil...thank you so much.   And I would actually much prefer to use a date field as my original entry field, but when I did that filemaker was forcing my users to enter a year which caused problems for me when they didn't know what it was.  Is there a way to change this to allow entry of months and days only in a single date field?

            • 3. Re: Converting Text to Dates
              philmodjunk

                   If you are using FIleMaker 11 or newer, you can use the OnObjectValidate script trigger to supply the missing year before FileMaker's data validation rejects the input as an invalid date.

              • 4. Re: Converting Text to Dates
                bterrell721

                     That much I could do, the unfortunate part is that in many cases I actually don't know what the year should be.  This is capturing birthday and anniversary dates and often clients do not wish to reveal the actual year, only the month and day.  I have to account for this in our system, but still be able to utilize these dates for searches and reports regardless of whether or not a year has been given.  As far as I have found there isn't a way to use a Date field without giving it a year to work with and supplying erroneous data.

                • 5. Re: Converting Text to Dates
                  philmodjunk

                       It seem that the year doesn't matter. That only the month and day are significant. In which case you can enter any year you want--the current year or just a constant value. You then have a valid date entered in the date field, but you can format the display of this date to only show the month and day.