LCVND returns the next due date for Lab Cal Sample for the specified variable(s).   

SYNTAX:

LCVND(StartDate,DateFormat,VarNum1,..VarNum99)

or

LCVND(StartDate, DateFormat, Where Clause)

StartDate: The date to start searching from.  Can be a quoted date, cell reference to a date cell, or a date formula.  NOTE: If blank (i.e. "") current date is used.

DateFormat:  The format that the date will be outputted.  See here for format explanation.

VarNum1..VarNum99: A comma separated list of variables that sets which variables to calculate from.

Where Clause: Instead of providing a list of variables, you can use an SQL Where clause against the VarDesc,Location table to get the list of variables.  I.E. "Units = 'mg/L'" wound return all variables with Units set to mg/L. See Examples.

EXAMPLES:

=LCVND("","Short Date",4571) returns 6/11/2019. 

Find the next date of the Quarterly Metals sample, start looking on the current date 5/24/2019.   The sample Effluent Metals is scheduled for 6/11/2019.  The sample has Copper, Lead, Cadmium, and Chromium.  You can pick any one variable to detect the sample, in this case we used 4571 Effluent Copper.

 

=LCVND("","Short Date","LVL2_ID=34 AND ANALYTECODE = '1030'") returns 1/1/2022.

We want to find the next Lead and Copper sample that is scheduled at any distribution site.  We have setup our schedule as once every 3 years on Jan 1st starting in Jan 2019.  The current date is 5/24/2019, therefore the next samples are due on Jan 1st 2022 (note this is off the Lab Cal Calendar which only goes 9 months into the future).   We have 20 different sites/locations setup, each with it's own pair of Lead and Copper variables.  Use the Analyte Code for lead (1030) which we will use as the trigger var to detect the sample.  Also, we only want variables that have Lead and Copper as it's Parent (i.e. LVL2_ID of 34).