3 Replies Latest reply on Jul 20, 2009 9:16 AM by thong127

    Elapse Date

    thong127

      Title

      Elapse Date

      Post

      Hi to everybody,

       

      Thanks to Mr_Vodka for your suggestion regarding my auto fill problem...your suggestions using looked-up it works for me.

       

      Now, I have again another problem, I want to know how many years, months, and days remaining before a certain product reach its expiration date base from present date.

       

      In my table I have an "Expiration Date" field and I have also a field "No. of Days to Expire" with a calculation of                 

      (Let([

       

      today = Get (CurrentDate ) ;

      days = Expiry Date - today ;

      md = Date (0 ; days + 1 ; Year ( Expiry Date ) ) ;

      y = Int ( days / 365)];

       

      y & "years," & Month ( md ) & "months, & Day ( md ) & "days"

       

      Is

       

      Which  I based from Danny Leung.

       

      But when I tried to enter data into the field "Expiration Date" 8/9/2009 and the prresent date is July 18, 2009 (7/18/2009) the result must be 22 days but it returns to ) 0 years, 12 months, & 23 days. Please help me to organize the calculation.

       

      Thank you very much and more power

        • 1. Re: Elapse Date
          mrvodka
             Why not leave it as days left? M,D,Y is relative to your defininition of it. For example, what is considered a month in terms of 75 days?
          • 2. Re: Elapse Date
            etripoli
              

            Calculation doesn't look quite right.  Try:

             

            Let( [

            yearlength = 365.25;

            monthlength = 365.25/12;

            daysleft = Expiry Date - get ( currentdate );

            years =  Div ( daysleft; yearlength );

            months =  Div ( mod ( daysleft; yearlength ); monthlength );

            days = int ( mod ( mod ( daysleft; yearlength ); monthlength ) )

            ];

            case (  years > 0; years & " years, " ) &

            case ( months > 0; months & " months, " ) &

            days & " days"

            )










            1 of 1 people found this helpful
            • 3. Re: Elapse Date
              thong127
                

              Thank you so much to etripoli.....It works.

               

              More power...

               

               

              Best regards,

               

              thong