7 Replies Latest reply on Aug 8, 2011 2:07 PM by raybaudi

    Date Calculation

    thong127

      Title

      Date Calculation

      Post

      Hi Experts..

      I Have four (4) fields

      Expiry Date (Date)

      Retest Date (Date)

      Manufacturing Date(Date)

      Elapse Date (Calculation)

      Kindly help me how to figure out the calculation for the Elapse Date base from the Manufacturing Date when one of the EXPIRY DATE or RETEST DATE is given and i want also that the result of the elapse date should be express in years, Month & Days (0 Years, 0 months, 0 Days).

      Thank you very much.

      Thong

        • 1. Re: Date Calculation
          LaRetta_1

          Hi Thong,

          Not sure I follow:

          You want to create a calculation called Elapse Date which subtracts (Expiry Date or Retest Date ) from Manufacturing Date?  Is this correct?

          The result you request is text.  If you might ever need to access this Elapse Date as date, it must be date.

          You would need two fields - your Elapse Date as date field and a conversion-to-text calculation to 'write out' the results or Elapse Date should change its name to Elapse 'something else but date' and just write out your results.

          Can you explain a bit more about Expiry Date and Retest Date so I can get a sense of your User dynamic? And how might the current date (when this field is viewed) play into this equation?

          ADDED:  (Expiry Date or Retest Date )  - is only one of these fields filled in or would we take the lower or higher of the dates if both?

          • 2. Re: Date Calculation
            thong127

            Hi Laretta,

            Thanks for the reply.

            "You want to create a calculation called Elapse Date which subtracts (Expiry Date or Retest Date ) from Manufacturing Date?  Is this correct?" Yes I want to create a calculation date.

             

            "Can you explain a bit more about Expiry Date and Retest Date so I can get a sense of your User dynamic?" We have some Materials in our warehouse that labelled Expiry Date which means if the materials reach that day we gonna throw them away...and some has Retest Date which we can retest after the labeled date.

            Below is the link of my file...

            http://www.4shared.com/file/Z-lbVWib/Materials.html

            • 3. Re: Date Calculation
              raybaudi

               

              The difference from two date fields doesn't return a date, so it could be named: "Elapsed Time" ( not "Elapsed Date" ) and the result could be given in days.

              Try:

              Min ( Expiry Date ; Retest Date ) - Manufacturing Date

              result type: number

              BTW: How can a "Retest Date" be lower than the "Manufacturing Date" ?

              • 4. Re: Date Calculation
                thong127

                 

                Hi Raybaudi,

                Thnks for the reply.

                "BTW: How can a "Retest Date" be lower than the "Manufacturing Date" ?" sorry for that...its my fault

                Below is the link of my file I re-uploaded it again with the actual data and with the use of custom function that I copied from briandunning.com

                actually what i want is the shelf life expiry & shelf life retest will be in one calculation...(it could be name as elapse time)because a single Material has either a retest or expiry date.

                sorry for not making myself clear.

                 

                http://www.4shared.com/file/zT1u0NDY/Materials_2.html

                • 5. Re: Date Calculation
                  raybaudi

                  Your own examplesshow thatthe fieldsExpiry Date and Retest Dateare neversimultaneouslyfilled.
                  Is it always the case?
                  If bothwerepresent,which valueshould prevail ?
                  Which CF do you like better ? ( they are both unaccurate )

                  • 6. Re: Date Calculation
                    thong127

                    Hi raybaudi,

                    "Your own examples show that the fields Expiry Date and Retest Date are never simultaneously filled.
                    Is it always the case?" Yes.

                    I want a calculation that for example if the Retest Date field is empty...it only shows the result for Shelf Life Expiry.

                     

                    Thanks

                    • 7. Re: Date Calculation
                      raybaudi

                      Try:

                      DateDif ( Manufacturing Date ; Min ( Expiry Date ; Retest Date ) ; "T" )

                      but you'll have to modify the CF DateDif ( date1 ; date2 ; flag ) to take in account an empty date with something like:

                      If ( date1*date2 ≠ 0 ;
                      that CF
                      )