3 Replies Latest reply on Feb 9, 2010 8:51 AM by ikaika7

    converting number sequence to dates

    ikaika7

      Title

      converting number sequence to dates

      Post

      Hello, I'm new to FMP and I have version 7. I was wondering how to convert a field containing a number sequence to represent  a specific date. The number sequence can be anywhere from one digit to five digits in length.

       

      This field is divided in the following manner. The 1st digit is the year, the 2nd two digits are the month and the last two digits are the day of the month. Thus, 00616 would represent June 16, 2010. Thus, 00600 would represent June 2010. (NOTE! 10600 would represent June 2011.)

       

      Thank you!

        • 1. Re: converting number sequence to dates
          mrvodka
            

          I dont agree with your numbering system but....

           

          Let ( x = YourField;

                 Date ( Middle ( x; 2; 2 ); Right ( x; 2 ); 2010 + Left ( x; 1 ) )

               )

           

          • 2. Re: converting number sequence to dates
            LaRetta_1
              

            ikaika7 wrote:

            The number sequence can be anywhere from one digit to five digits in length.


            This is concerning.  Unless the number is always 5 digits, how will one know how to parse it?

             

            0122

             

            Year 2010, month December and day 2?  Or year 2010, month February and day 22? 

             

            UPDATE: And I agree with Mr. Vodka - that numbering system isn't a good one.  If you are migrating or translating data, we understand but if you are using this yourself, you might seriously want to consider changing it.  Even if external source, could they provide a better pattern for you?


            • 3. Re: converting number sequence to dates
              ikaika7
                 Thanks for your advice. Yes, I agree, the database I've been given to convert wasn't created with an easy conversion in mind. This is what I have to work with, unfortunately - I don't believe the person obtaining the data understands logical thinking.