# Date & Time Formulas

## CurrentDate (TODAY)

#### Formula

`=currentdate([timezone], [output_format])`

`=TODAY([timezone], [output_format])`

#### **Description**

Outputs the current date for the timezone and date format given. You can find all the timezone specifiers [here](https://docs.osmos.io/data-transformations/formulas/date-and-time-formulas/timezone-specifiers).

#### Arguments

| Argument       | Required (Y/N) | Description                                                                                                                      |
| -------------- | -------------- | -------------------------------------------------------------------------------------------------------------------------------- |
| timezone       | N              | Desired [timezone](https://docs.osmos.io/data-transformations/formulas/date-and-time-formulas/timezone-specifiers) of the output |
| output\_format | N              | Date [format](https://docs.osmos.io/data-transformations/formulas/date-and-time-formulas/date-format-specifiers) of the output   |

#### Examples

> **Example 1:**
>
> \=currentdate("America/Chicago", "The day of the week is %A") = The day of the week is Tuesday
>
> \=TODAY("America/Chicago", "The day of the week is %A") = The day of the week is Tuesday
>
> **Example 2:**
>
> \=currentdate() = 2021-05-04UTC
>
> \=TODAY() = 2021-05-04UTC

## CurrentDateTime

#### Formula

`=currentdatetime([timezone], [output_format])`

`=NOW([timezone], [output_format])`

#### **Description**

Outputs the current date and time for the timezone and datetime format given. You can find all the timezone specifiers [here](https://docs.osmos.io/data-transformations/formulas/date-and-time-formulas/timezone-specifiers).

#### Arguments

| Argument       | Required (Y/N) | Description                                                                                                                        |
| -------------- | -------------- | ---------------------------------------------------------------------------------------------------------------------------------- |
| timezone       | N              | Desired [timezone](https://docs.osmos.io/data-transformations/formulas/date-and-time-formulas/timezone-specifiers) of the output   |
| output\_format | N              | Datetime [format](https://docs.osmos.io/data-transformations/formulas/date-and-time-formulas/date-format-specifiers) of the output |

#### Examples

> **Example 1:**
>
> \=currentdate("America/Chicago", "The day of the week is %A") = The day of the week is Tuesday
>
> \=NOW("America/Chicago", "The day of the week is %A") = The day of the week is Tuesday
>
> **Example 2:**
>
> \=currentdate() = 2021-05-06 02:01:49.317275816
>
> \=NOW() = 2021-05-06 02:01:49.317275816
>
> **Example 3:**
>
> \=currentdatetime("America/Chicago", "%Y %m %d %H:%M:%S") = 2021 05 25 17:58:25
>
> \=NOW("America/Chicago", "%Y %m %d %H:%M:%S") = 2021 05 25 17:58:25

## CurrentTime

#### Formula

`=currenttime([timezone], [output_format])`

#### **Description**

Outputs the current time for the timezone and time format given. You can find all the timezone specifiers [here](https://docs.osmos.io/data-transformations/formulas/date-and-time-formulas/timezone-specifiers).

#### Arguments

| Argument       | Required (Y/N) | Description                                                                                                                      |
| -------------- | -------------- | -------------------------------------------------------------------------------------------------------------------------------- |
| timezone       | N              | Desired [timezone](https://docs.osmos.io/data-transformations/formulas/date-and-time-formulas/timezone-specifiers) of the output |
| output\_format | N              | Time [format](https://docs.osmos.io/data-transformations/formulas/date-and-time-formulas/date-format-specifiers) of the output   |

#### Examples

> **Example 1:**
>
> \=currenttime() = 14:26:36
>
> **Example 2:**
>
> \=currenttime("America/Chicago", "%H:%M:%S") = 02:01:49

## Date (DateValue)

#### Formula

`=date(<input>, [input_format], [output_format])`

`=datevalue(<input>, [input_format], [output_format])`

#### **Description**

Attempts to convert the input value into a date format. A partial date, e.g. one that is missing one or more of \[year, month, day], will be an error.

#### Arguments

| Argument       | Required (Y/N) | Description                                                                                                                                                                                   |
| -------------- | -------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| input          | Y              | Value in which the formula will attempt to convert to a date format                                                                                                                           |
| input\_format  | N              | Date [format](https://docs.osmos.io/data-transformations/formulas/date-and-time-formulas/date-format-specifiers) of the input value, this can be left blank in order to infer the date format |
| output\_format | N              | Desired date [format](https://docs.osmos.io/data-transformations/formulas/date-and-time-formulas/date-format-specifiers) of the output                                                        |

#### Examples

> **Example 1:**
>
> \=date($"DOB") = 1982-06-03
>
> \=datevalue($"DOB") = 1982-06-03
>
> **Example 2:**
>
> \=date($"Birthdays", "%m/%d/%C", "%Y-%m-%d") = 1982-06-03
>
> \=datevalue($"Birthdays", "%m/%d/%C", "%Y-%m-%d") = 1982-06-03
>
> **Example 3:**
>
> \=date($"PurchaseDate", ,"%m/%d/%Y") = 06/22/2020
>
> \=datevalue($"PurchaseDate", ,"%m/%d/%Y") = 06/22/2020

{% hint style="info" %}
Notice in example 3, no input date format is specified, instead the input is inferred by Osmos
{% endhint %}

## DateTime

#### Formula

`=datetime(<input>, [input_format], [output_format])`

#### **Description**

Attempts to convert the input value to a datetime format. A partial datetime, e.g. one that is missing one or more of \[year, month, day, hour, minute], will be an error.

#### Arguments

| Argument       | Required (Y/N) | Description                                                                                                                                                                                   |
| -------------- | -------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| input          | Y              | Value in which the formula will attempt to convert to a datetime  format                                                                                                                      |
| input\_format  | N              | Date [format](https://docs.osmos.io/data-transformations/formulas/date-and-time-formulas/date-format-specifiers) of the input value, this can be left blank in order to infer the date format |
| output\_format | N              | Desired datetime [format](https://docs.osmos.io/data-transformations/formulas/date-and-time-formulas/date-format-specifiers) of the output                                                    |

#### Examples

> **Example 1:**
>
> \=datetime("10/20/2020 10:22 PM") = 2020-10-20 22:22:00
>
> **Example 2:**
>
> \=datetime(currentdatetime(),,"%Y-%m-%d %H:%M:%S") = 2021-04-06 14:19:16

{% hint style="info" %}
Notice in example 2, no input date format is specified, instead the input is inferred by Osmos
{% endhint %}

## Time

#### Formula

`=time(<input>, [input_format], [output_format])`

#### **Description**

Attempts to convert the input value to a time format. A partial time, e.g. one that is missing one or more of \[hour, minute], will be an error.

#### Arguments

| Argument       | Required (Y/N) | Description                                                                                                                            |
| -------------- | -------------- | -------------------------------------------------------------------------------------------------------------------------------------- |
| input          | Y              | Value in which the formula will attempt to convert to a time format                                                                    |
| input\_format  | N              | Time [format](https://docs.osmos.io/data-transformations/formulas/date-and-time-formulas/date-format-specifiers) of the input value    |
| output\_format | N              | Desired time [format](https://docs.osmos.io/data-transformations/formulas/date-and-time-formulas/date-format-specifiers) of the output |

#### Examples

> **Example 1:**
>
> \=time("10/20/2020 10:22 PM") = 22:22:00
>
> **Example 2:**
>
> \= time($"Time of Purchase")=03:16:09
>
> **Example 3:**
>
> \=time(concat($"hours",":", $"minutes",":", $"seconds"))= 09:33:18

## ConvertTimeZone

#### Formula

`=converttimezone(<input>,<input_timezone>,<output_timezone>,[output_format])`

#### **Description**

Attempts to convert the input value into another timezone.&#x20;

#### Arguments

<table data-header-hidden><thead><tr><th width="242.66666666666666">Argument</th><th>Required (Y/N)</th><th>Description</th></tr></thead><tbody><tr><td>Argument</td><td>Required (Y/N)</td><td>Description</td></tr><tr><td>input</td><td>Y</td><td>Value in which the formula will attempt to convert to a new timezone.</td></tr><tr><td>input_timezone</td><td>Y</td><td>Timezone of the input</td></tr><tr><td>output_timezone</td><td>Y</td><td>Desired timezone output</td></tr><tr><td>output_format</td><td>N</td><td>Datetime format of the output</td></tr></tbody></table>

#### Examples

> **Example 1:**
>
> \=converttimezone("2022-08-29T15:48:24", "PST", "GMT") = "2022-08-29 22:48:24"
>
> **Example 2:**
>
> \=converttimezone("2022-08-31T01:00:00", "America/Los Angeles", "UTC", , "%m/%d/%Y %H:%M:%S %Z") = "08/31/2022 08:00:00 UTC"
>
> **Example 3:**
>
> \=converttimezone("2022-08-31 01:00:00 PDT", "America/Los Angeles", "UTC"): = "2022-08-31 08:00:00"

## DateDif

#### Formula

`=DateDif(<input1>,<input2>,<output_unit>)`

`=DateDiff(<input1>,<input2>,<output_unit>)`

#### **Description**

Calculates the difference between two dates. Only complete units are considered, so all results are rounded down towards zero. i.e. a difference of 5 days will equate to 0 Months.  &#x20;

Note: Both DateDif and DateDiff are supported.

{% hint style="info" %}
It is recommended that this formula be used with full date values rather than shortened dates, regardless of date format.  i.e. 12/31/1999 or 2023-02-08 rather than 12/31/99 or 23-02-08
{% endhint %}

#### Arguments

<table data-header-hidden><thead><tr><th width="242.66666666666666">Argument</th><th>Required (Y/N)</th><th>Description</th></tr></thead><tbody><tr><td>Argument</td><td>Required (Y/N)</td><td>Description</td></tr><tr><td>input1</td><td>Y</td><td>Date value which serves as the starting value for the date evaluation. </td></tr><tr><td>input2</td><td>Y</td><td>Date value which will be "subtracted" from input1 to determine the difference between the dates</td></tr><tr><td>output_unit</td><td>Y</td><td>Desired output unit of measure. Accepts Y (years), M (months), or D (days).</td></tr></tbody></table>

#### Examples

> **Example 1:**
>
> \=datedif("2023-02-01", "2023-02-02", "D") => 1
>
> **Example 2:**
>
> \=datedif("2030-01-01", "2020-01-01", "Y") => -10
>
> **Example 3:**
>
> \=datedif("01/01/2023", "03/15/2023", "M") => 2

## EDate

#### Formula

`=EDate(<input1>,<input2>)`

#### **Description**

Calculates a date based on an initial date value and a count of Months.

#### Arguments

<table data-header-hidden><thead><tr><th width="242.66666666666666">Argument</th><th>Required (Y/N)</th><th>Description</th></tr></thead><tbody><tr><td>Argument</td><td>Required (Y/N)</td><td>Description</td></tr><tr><td>input1</td><td>Y</td><td>Date value which serves as the initial value for the date evaluation</td></tr><tr><td>input2</td><td>Y</td><td>An integer representing Months to be added to the initial date value</td></tr></tbody></table>

#### Examples

> **Example 1:**
>
> \=EDate("2023-02-01", 3) => 2023-05-01
>
> **Example 2:**
>
> \=EDate("2023-02-01", -30) => 2018-02-01
>
> **Example 3:**
>
> \=EDate("2030-01-01", ($"Years" \* 12))

## YearWeekToDate

#### Formula

`=yearweektodate(<input1>,<input2>)`

#### **Description**

Creates a date from a given year, week and weekday. The weekday defaults to Friday. The week needs to be between 1 and 53. The weekday needs to be either the three letter abbreviation, e.g `Fri` or the full name, e.g `Friday` of the day of the week.&#x20;

#### Arguments

<table data-header-hidden><thead><tr><th width="242.66666666666666">Argument</th><th>Required (Y/N)</th><th>Description</th></tr></thead><tbody><tr><td>Argument</td><td>Required (Y/N)</td><td>Description</td></tr><tr><td>input1</td><td>Y</td><td>Year which serves as the year for the week calculation.</td></tr><tr><td>input2</td><td>Y</td><td>An integer representing Week to determine the initial value for the date. </td></tr></tbody></table>

#### Examples

> **Example 1:**
>
> &#x20;\=yearweektodate(2023, 3, "sun") => "2023-01-22"
>
> **Example 2:**
>
> &#x20;\=yearweektodate(2023, 5) => "2023-02-05"
>
> **Example 3:**
>
> &#x20;\=yearweektodate(2023, 53) => ERROR