# 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](/data-transformations/formulas/date-and-time-formulas/timezone-specifiers.md).

#### Arguments

| Argument       | Required (Y/N) | Description                                                                                                    |
| -------------- | -------------- | -------------------------------------------------------------------------------------------------------------- |
| timezone       | N              | Desired [timezone](/data-transformations/formulas/date-and-time-formulas/timezone-specifiers.md) of the output |
| output\_format | N              | Date [format](/data-transformations/formulas/date-and-time-formulas/date-format-specifiers.md) 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](/data-transformations/formulas/date-and-time-formulas/timezone-specifiers.md).

#### Arguments

| Argument       | Required (Y/N) | Description                                                                                                      |
| -------------- | -------------- | ---------------------------------------------------------------------------------------------------------------- |
| timezone       | N              | Desired [timezone](/data-transformations/formulas/date-and-time-formulas/timezone-specifiers.md) of the output   |
| output\_format | N              | Datetime [format](/data-transformations/formulas/date-and-time-formulas/date-format-specifiers.md) 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](/data-transformations/formulas/date-and-time-formulas/timezone-specifiers.md).

#### Arguments

| Argument       | Required (Y/N) | Description                                                                                                    |
| -------------- | -------------- | -------------------------------------------------------------------------------------------------------------- |
| timezone       | N              | Desired [timezone](/data-transformations/formulas/date-and-time-formulas/timezone-specifiers.md) of the output |
| output\_format | N              | Time [format](/data-transformations/formulas/date-and-time-formulas/date-format-specifiers.md) 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](/data-transformations/formulas/date-and-time-formulas/date-format-specifiers.md) of the input value, this can be left blank in order to infer the date format |
| output\_format | N              | Desired date [format](/data-transformations/formulas/date-and-time-formulas/date-format-specifiers.md) 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](/data-transformations/formulas/date-and-time-formulas/date-format-specifiers.md) of the input value, this can be left blank in order to infer the date format |
| output\_format | N              | Desired datetime [format](/data-transformations/formulas/date-and-time-formulas/date-format-specifiers.md) 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](/data-transformations/formulas/date-and-time-formulas/date-format-specifiers.md) of the input value    |
| output\_format | N              | Desired time [format](/data-transformations/formulas/date-and-time-formulas/date-format-specifiers.md) 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


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.osmos.io/data-transformations/formulas/date-and-time-formulas.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.