The Klipfolio DATE_STARTOF function retrieves the start date of a given unit relative to the given date.

The syntax for the DATE_STARTOF function is:

DATE_STARTOF( values , unit , relative value , first )

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


values The data set number vector

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

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 'quarter'.

text scalar


Let's look at a DATE_STARTOF function example.




1 5/16/14 16
2 5/15/14 12
3 2/1/14 14
4 1/1/14 10

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

SUM( SELECT( B:B , ( BETWEEN (DATE( A:A , "M/d/yy" ) , DATE_STARTOF( TODAY( ) , year ) , TODAY( ) ))))

-> ["52"]

Result: This formula sums the values from the beginning of the year until today's date.

Related links: