# About date formulas

Learn about date formulas.

Date functions are useful when you want to compare data collected between two date periods. Date formulas allow you to apply date related functions to your formulas.

## Date formulas

The date formulas include:

`add_days`

Returns the result of adding the specified number of days to the given date.

Examples
```add_days (01/30/2015, 5) = 02/04/2015
`add_minutes`

Returns the result of adding the specified number of minutes to the given date, datetime, or time.

Examples
```add_minutes ( 01/30/2015 00:10:20 , 5 ) = 01/30/2015 00:15:20
add_minutes ( invoiced , 30 )```
`add_months`

Returns the result of adding the specified number of months to the given date.

Examples
```add_months ( 01/30/2015, 5 ) = 06/30/2015
add_months ( invoiced_date , 5 )```
`add_seconds`

Returns the result of adding the specified number of seconds to the given date/ datetime/ time.

Examples
```add_seconds ( 01/30/2015 00:00:00, 5 ) = 06/30/2015 00:00:05
add_seconds ( invoiced_date , 5 )```
`add_weeks`

Returns the result of adding the specified number of weeks to the given date.

Examples
```add_weeks ( 01/30/2015, 2 ) = 02/13/2015
add_weeks ( invoiced_date , 2 )```
`add_years`

Returns the result of adding the specified number of years to the given date.

Examples
```add_years ( 01/30/2015, 5 ) = 01/30/2020
add_years ( invoiced_date , 5 )```
`date`

Returns the date portion of a date.

Examples
`date (home visit)`
`day`

Returns the number (1-31) of the day of the month for a date.

Examples
```day (01/15/2014) = 15
day (date ordered)```
`day_number_of_quarter`

Returns the number of the day in a quarter for a date. You can add an optional second parameter to specify whether a 'fiscal' or 'calendar' year is used to calculate the result. The default is 'calendar'.

Examples
```day_number_of_quarter (01/30/2015) = 30
day_number_of_quarter (01/30/2015, 'fiscal') = 91  // May 1 is start of fiscal year```
`day_number_of_week`

Returns the number (1-7) of the day in a week for a date. Monday is 1, and Sunday is 7.

Examples
```day_number_of_week(01/15/2014) = 3
day_number_of_week (shipped)```
`day_number_of_year`

Returns the number (1-366) of the day in a year from a date. You can add an optional second parameter to specify whether a 'fiscal' or 'calendar' year is used to calculate the result.

Default

'calendar'

Examples
```day_number_of_year (01/30/2015) = 30
day_number_of_year ( 01/30/2015, 'fiscal' ) = 275 // May 1 is start of fiscal year
day_number_of_year (invoiced)```
`day_of_week`

Returns the day of the week for the given date.

Examples
```day_of week (01/30/2015) = Friday
day_of_week (serviced)```
`diff_days`

Subtracts the second date from the first date and returns the result in number of days, rounded down if not exact.

Examples
```diff_days (01/15/2014, 01/17/2014) = -2
diff_days (purchased, shipped)```
`diff_time`

Subtracts the second date from the first date and returns the result in number of seconds.

Examples
```diff_time (01/30/2014, 01/31/2014) = -86,400
diff_time (clicked, submitted)```
`hour_of_day`

Returns the hour of the day for the given date.

Examples
`hour_of_day (received)`
`is_weekend`

Returns `true` if the date is a Saturday or a Sunday.

Examples
```is_weekend (01/31/2015) = true
is_weekend (emailed)```
`month`

Returns the month from the date.

Examples
```month (01/15/2014) = January
month (date ordered)```
`month_number`

Returns the number (1-12) of the month from a given date. You can add an optional second parameter to specify whether a 'fiscal' or 'calendar' year is used to calculate the result.

Default

'calendar'

Examples
```month_number (09/20/2014) = 9
month_number ( 09/20/2014, 'fiscal' ) = 5 // May 1 is start of fiscal year
month_number (purchased)```
`month_number_of_quarter`

Returns the month (1-3) number for the given date in a quarter. You can add an optional second parameter to specify whether a 'fiscal' or 'calendar' year is used to calculate the result.

Default

'calendar'

Examples
`month_number_of_quarter (02/20/2018) = 2`

In the following example, May 1st is the start of the fiscal year.

`month_number_of_quarter (02/20/2018,'fiscal' ) = 1`
`now`

Returns the current date and time in your locale’s standard date and time format. For example, if your locale is English (United States), it returns `MM/dd/yyyy hh:mm:ss` (04/27/2022 12:34:00).

Examples
`now ()`
`quarter_number`

Returns the number (1-4) of the quarter associated with the date. You can add an optional second parameter to specify 'fiscal' or 'calendar' dates.

Default

'calendar'

Examples
```quarter_number ( 04/14/2014) = 2
quarter_number ( 04/14/2014, 'fiscal' ) = 4 // May 1 is start of fiscal year
quarter_number ( shipped )```
`start_of_month`

Returns `MMM yyyy` for the first day of the month. Your installation configuration can override this setting so that it returns a different format, such as `MM/dd/yyyy`. Speak with your ThoughtSpot administrator for information on doing this.

Examples
```start_of_month ( 01/31/2015 ) = Jan FY 2015
start_of_month (shipped)```
`start_of_quarter`

Returns the date for the first day of the quarter for the date. You can add an optional second parameter to specify whether a 'fiscal' or 'calendar' year is used to calculate the result.

Default

'calendar'

Examples
```start_of_quarter ( 04/30/2014) = Apr 2014
start_of_quarter ( 04/30/2014, 'fiscal') = Feb 2014    // May 1 is the start of the fiscal year
start_of_quarter (sold)```
`start_of_week`

Returns the date for the first day of the week for the given date.

Examples
```start_of_week ( 01/31/2020 ) = 01/27/2020
start_of_week (emailed)```
`start_of_year`

Returns the date for the first day of the year for the date. You can add an optional second parameter to specify whether a 'fiscal' or 'calendar' year is used to calculate the result. The default is 'calendar'.

Examples
```start_of_year (04/30/2014) returns Jan 2014
start_of_year (04/30/2014, 'fiscal')    // May 1 is start of fiscal year
start_of_year (joined)```
`time`

Returns the time portion of a date.

Examples
```time (1/31/2002 10:32) = 10:32
time (call began)```
`today`

Returns the current date in your locale’s standard date format. For example, if your locale is English (United States), it returns `MM/dd/yyyy` (04/27/2022).

Examples
`today ()`
`week_number_of_month`

Returns the week number for the date in a month.

Examples
`week_number_of_month(03/23/2017) = 3`
`week_number_of_quarter`

Returns the week number for the given date in a quarter. You can add an optional second parameter to specify whether a 'fiscal' or 'calendar' year is used to calculate the result.

Default

'calendar'

Examples
```week_number_of_quarter (01/31/2020) = 5
week_number_of_quarter (05/31/2020, 'fiscal') = 5   // May 1 is start of fiscal year```
`week_number_of_year`

Returns the week number for the date in a year. You can add an optional second parameter to specify whether a 'fiscal' or 'calendar' year is used to calculate the result.

Default

'calendar'

Examples
```week_number_of_year (01/17/2014) = 3
week_number_of_year ( 01/17/2014, 'fiscal') = 38.  // May 1 is start of fiscal year```
`year`

Returns the year from a given date. You can add an optional second parameter to specify whether a 'fiscal' or 'calendar' year is used to calculate the result. The default is 'calendar'.

Examples
```year (01/15/2014) = 2014
year (12/15/2013, 'fiscal' ) = 2014.  // May 1 is start of fiscal year
year (date ordered)```

## Calculate date formulas

Calculating date formulas is useful when you want to compare data from different date periods. Here are some examples of using date formulas:

### Example 1

The following example shows you how to create formulas that you can use to compare data from this week to last week.

• The formula for this week is: `week ( today () ) - week (date)`

• The formula for last week is: `diff_days ( week ( today () ) ) , week ( date ) )`

### Example 2

The following example shows you how to calculate the percent increase from the last date period to this period in terms of revenue.

1. Create the formula: `this week revenue = sum ( if ( this week ) then revenue else 0 )`

2. Then create the formula: `last week revenue = sum ( if (last week ) then revenue else 0 )`

3. Use nested formulas to calculate the percent increase by creating a parent formula: `percent increase = ( ( this week revenue - last week revenue) / last week revenue ) * 100`

## Fiscal and Gregorian calendars

For the following date formulas, you can further specify either `fiscal` or Gregorian `calendar` on which to base date calculations. (If you do not specify a calendar type, the formula defaults to standard Gregorian, with the year starting in January.)

• `day_number_of_quarter`

• `day_number_of_year`

• `month_number`

• `month_number_of_quarter`

• `quarter_number`

• `start_of_quarter`

• `start_of_year`

• `week_number_of_quarter`

• `week_number_of_year`

• `year`

Your ThoughtSpot administrator and ThoughtSpot Support can set up a fiscal calendar year to start on any month. If the `fiscal` year is not explicitly configured in the system, `fiscal` defaults to January, the same as the Gregorian `calendar`.

For example, the formula `month_number_of_quarter (05/31/2014)` would return `2` based on the default Gregorian calendar, whereas the formula `month_number_of_quarter (05/31/2014, 'fiscal')` would return `1` if your administrator has configured the fiscal calendar to start in May.

Related information