DCV returns the date of closest value found for the variable and date/time specified. Searches forward and backward and uses the value whose datestamp that is closest to the time being calculated. If there is a tie (e.g. there is a value yesterday and a value tomorrow) it will then use the Stat_If_Tie to calculate the value.
SYNTAX:
DCV(VarNum, MaxDays, StartFlag, Stat_If_Tie, DateFormat)
VarNum: The variable number to search for. Can be a number (i.e. 12) or a cell reference (i.e. C2).
MaxDays: Sets maximum number of days to search before giving up.
StartFlag: Sets when the seach begins. Has the following settings:
1 - Start searching on the start date
2 - Start searching on the end date
3 - Start searching one day before start date
4 - Start searching one day after the end date
11 - Start searching at the 11:59:59 PM of the Start Date.
12 - Start searching at the 11:59:59 PM of the End Date.
13 - Start searching at the 11:59:59 PM of one day before start date
14 - Start searching at the 11:59:59 PM of one day after the End Date.
“Date/Time” - A date/time in quotes to start the search on.
Stat_If_Tie: A quoted string that specifies what to calculate when the 2 values are the same time difference from the slot being calculated.
| Stat |
Description |
| First |
Use the First (earlier) value |
| Last |
Use the Last (later) value |
| Avg |
Average the two values |
| Min |
Use the minimum of the two values |
| Max |
Use the maximum of the two values |
DateFormat: Date Format (i.e. "mm/dd/yyyy"...) to return the date found in.
SEE ALSO: CV, CVT
EXAMPLES:
DCV(11,31,1,"First","m/d/yyyy") Find the date/time of the closest value to the start date of the report (StartFlag = 1). If there is a tie, use the date/time of the first (earlier) value. Search up to 31 days.
DCV(11,90,B4,"MAX","m/d/yyyy hh:mm") Find the date/time of the closest value to the date in cell B4. If there is a tie, use the date/time of the Max value. Search up to 90 days.