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

# Date Calculation

### 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

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

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

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

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.

• ###### 5. Re: Date Calculation

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

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

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
)