DATE_ENDOF function

The Klipfolio DATE_ENDOF function retrieves the end date of the given unit value relative to the given date.

The syntax for the DATE_ENDOF function is:

DATE_ENDOF( values , unit , relative value , first )

Note: If you specify the first value, you must also provide a relative value.

Parameters

values A date or array of dates in Unix time format. number vector
unit

Options include:

week, month, quarter, year.

text scalar
relative value

Use the  "Insert literal string or number" button to type a relative value.  For example, if the unit value is month, a relative value of -1, returns the values for the previous month.

number scalar
first

Optional

Options include:

Sunday (default), Monday, Tuesday, Wednesday, Thursday, Friday, Saturday.

January (default), February, March, April, May, June, July, August, September, October, November, December.

Note: The day of the week options for first only affect calculations when unit is 'day of week' or 'week'. The month options for first only affect calculations when unit is 'day of year' or 'month' or 'quarter'.

text scalar

DATE_ENDOF example

Let's look at a DATE_ENDOF function example.

 

A

1 4/1/14
2 4/5/14
3 4/10/14
4 5/1/14
5 5/5/14
6 5/10/14

The following example is based on the above text in an Excel spreadsheet where today's date is 5/23/14:

SELECT( A:A , ( BETWEEN (DATE( A:A , "M/d/yy" ) , DATE_STARTOF( TODAY( ) , month , -1 ) , DATE_ENDOF( TODAY( ) , month ))))

-> ["4/1/14, 4/5/14, 4/10/14, 5/1/14, 5/5/14, 5/10/14"]

Result: This formula returns the days starting from 1 April 2014 until 31 May 2014.

Related links: