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?
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...
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.
Min ( Expiry Date ; Retest Date ) - Manufacturing Date
result type: number
BTW: How can a "Retest Date" be lower than the "Manufacturing Date" ?
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.
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 )
"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.
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 ;