4 Replies Latest reply on Nov 29, 2013 3:52 PM by Tod

    Changing a date in text format to sortable date or numeric format

    Tod

      Title

      Changing a date in text format to sortable date or numeric format

      Post

           I'm working with an excel file of membership info for a social club and I'm trying to extract each member's birthday so a list can be published in the monthly newsletter.  All the data in the excel file are in text format.  The dates in particular are in "26-Jul" format.   I've tried using the inspector to change these to date format but it always wants to add a year.  I don't relish the thought of going through each record and specifying something like "1900" as the year for everyone just to include a year.  In the inspector I've tried various date formats, including the "custom" where one can specify which parts of the date are displayed.  Quite often the dates now appear as "?" even in Preview.  Huh?

           So is there a calculation that would accept the "26-Jul" date and transform it into a real date (without the year component?)

           Thanks!

           -Tod

            

            

        • 1. Re: Changing a date in text format to sortable date or numeric format
          philmodjunk

               I would guess that this data is intended to record birth days without recording birth dates.

               Dates are actually integers storing the number of days from the date shown in the field to the date 12/31/0000. What you specify in the Inspector are just different ways to display that date--it does not change how the data is entered.

               You will need a year entered for every date, but this need not be done manually one record at a time and you can specify a date format to hide the year and thus still get the display that you want.

               But

               26-Jul-1900

               will not be interpreted as a valid date. You'll need to use a calculation to produce the needed date so that it can be treated as a date.

               Let ( [ dtxt = YourTextField ;
                         dy = Leftwords ( dtxt ; 1 ) ;
                         mn = Ceiling ( Position ( "janfebmaraprmayjunjulaugsepoctnovdec" ; RightWords ( dtxt ; 1 ) ; 1 ; 1 ) / 3 )
                        ] ;
                         Date ( mn ; dy ; 1900 )
                     )

          • 2. Re: Changing a date in text format to sortable date or numeric format
            Tod

                 @PhilModjunk:  Your guess is correct.  No one wants their year of birth displayed :)  Thanks for the calculation; I'll enter it later and see how it works.  I see where the year is necessary but can be masked off.  Do I create a new field that takes the data from the original "B-day" field?

                 One follow-on question:  If I get raw data on new members, will I be able to tab to that field and enter something like 1-1-1900 and it will be displayed as 1-Jan?

                  
            • 3. Re: Changing a date in text format to sortable date or numeric format
              philmodjunk

                    Do I create a new field that takes the data from the original "B-day" field?

                   You'll need two fields as the data types aren't the same. Your original data is text and the end result is a Date. You can set up a date field with the expression I suggested as an auto-enter calculation and enable auto-enter options during import. That way, you have a regular date field that you can edit, but you can import your data without needing to manually edit them to get your needed dates.

                   

                        will I be able to tab to that field and enter something like 1-1-1900 and it will be displayed as 1-Jan?

                   Yes. It's even possible to use the OnObjectValidate script trigger to run a script that adds in the year so that all you need do is specify the month and day.

              • 4. Re: Changing a date in text format to sortable date or numeric format
                Tod

                     @Phil....

                     Again, thanks for the added info.  Now I've got some work to do.

                     Background:  I've been an FMP user (dreaming up the occasional simple database) since the 1980s but I am a total dunce at trying to figure out certain solutions, as you can see.  I really appreciate the time taken by those much brighter than I to look at such problems and come up with working solutions in what seems to be an instant.