Home : DLV
Q11217 - INFO: DLV
 DLV returns the date/time of the Last Value found.   

SYNTAX:

DLV(VarNum, Offset, StartFlag, MaxDays, DateFormat)

VarNum:  The variable number to get the average for.

Offset: Sets which value to return.  A setting of 1 will return the 1st value found, 2 will return the 2nd value and so forth.  Searches backwards in time.

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. 

MaxDays:  Sets maximum number of days to search before giving up. If the value of MaxDays is negative, this function will search ahead to find the next value in the future.

DateFormat: Date Format (i.e. "mm/dd/yyyy"...) to return the date found in.

NOTES:

Returns a Text string.  Cell type will be changed to Text when using this formula.

See Tip: Get the average of the last x values for an example of using DLV with a Group Summary Function (GAVG).

EXAMPLES:

DLV(1,3,1,30,"mm/dd/yy") Returns 3rd Date for variable 1 before the start date searching for 30 days

DLV(2,1,4,-31,"mm/dd/yy") Find the 1st (Offset 1) Date for variable 2 starting one day after the end date of the report and search forward in time up to 31 days. 

DLV(2,1,”10/15/04 08:15”,10, "mmm")    Finds the first value found for variable 2 on or before 10/15/04 08:15 AM.  NOTE: if a daily detail value exists at exactly 10/15/04 08:15 it will find that value, i.e. it searches for date/times <= 10/15/04 08:15.

Related Articles
No Related Articles Available.

Article Attachments
No Attachments Available.

Related External Links
No Related Links Available.
Help us improve this article...
What did you think of this article?

poor 
1
2
3
4
5
6
7
8
9
10

 excellent
Tell us why you rated the content this way. (optional)
 
Approved Comments...
No user comments available for this article.
Created on 5/10/2008 7:52 AM.
Last Modified on 9/29/2016 8:18 AM.
Last Modified by Scott Dorner.
Article has been viewed 4052 times.
Rated 4 out of 10 based on 3 votes.
Print Article
Email Article