---
title: Date and time functions
slug: date-and-time-functions
description: Format, parse, and calculate dates and times with timezone support
image: https://archbee-image-uploads.s3.amazonaws.com/oAyFj2GHlBeBVWF5OAir2/6puIOMnW3IMFTr5QH9XWl_domino-zoomin-purple-a-1.png
docTags: 
createdAt: 2025-02-03T13:29:15.598Z
---

Use date and time functions to convert and transform date and time data. For example, you can change the date format, convert time based on timezones, convert text to date or time data, and more. Below is a list of supported date and time functions with descriptions and details for each.

## formatDate (date; format; \[timezone])

**When to use it:** You have a date value that you wish to convert (format) to a text value (textual human-readable representation) like `12-10-2019 20:30` or `Aug 18, 2019 10:00 AM`

### Parameters

The second column indicates the expected type. If different type is provided, [type coercion](docId\:IhFBo_As3zrC346CWrMmO) is applied.

| **Parameter** | **Expected type** | **Description**                                                                                                                                                                                                                                                                                                                                    |
| ------------- | ----------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| date          | date              | Date value to be converted to a text value.                                                                                                                                                                                                                                                                                                        |
| format        | text              | Format specified using [tokens for date/time formatting](docId\:KUmuKVBfixzZTKuZyE7XZ).<br />Example: `DD.MM.YYYY HH:mm`                                                                                                                                                                                                                           |
| timezone      | text              | Optional. The timezone used for the conversion.<br />See [List of tz database time zones](https://en.wikipedia.org/wiki/List_of_tz_database_time_zones#List), column "TZ database name" for the list of recognized timezones.<br />If omitted, Make uses the organization's timezone. You can [edit your time zone](docId\:Bn3S0_8AZJDynxbNzNP_a). |

### Return value and type

Text representation of the given Date value according to the specified format and timezone. Type is Text.

### Example

The Organization's and Web's timezone were both set to `Europe/Prague` in the following examples.

| **Function**                                                | **Result**          |
| ----------------------------------------------------------- | ------------------- |
| `formatDate(1. Date created;` MM/DD/YYYY `)`                | 10/01/2018          |
| `formatDate(1. Date created;` YYYY-MM-DD hh\:mm A `)`       | 2018-10-01 09:32 AM |
| `formatDate(1. Date created;` DD.MM.YYYY HH\:mm `;` UTC `)` | 01.10.2018 07:32    |
| `formatDate(now;` MM/DD/YYYY HH\:mm `)`                     | 19/03/2019 15:30    |

## parseDate (text; format; \[timezone])

**When to use it:** You have a [text](docId\:hdc1mr5JWOaqEIiS266kB) value representing a date (e.g. `12-10-2019 20:30` or `Aug 18, 2019 10:00 AM`) and you wish to convert (parse) it to a [Date](docId\:hdc1mr5JWOaqEIiS266kB) value (binary machine-readable representation).

### Parameters

The second column indicates the expected type. If different type is provided, [Type Coercion](docId\:IhFBo_As3zrC346CWrMmO) is applied.

| **Parameter** | **Expected type** | **Description**                                                                                                                                                                                                                                                                                                                                                                         |
| ------------- | ----------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| text          | text              | Text value to be converted to a date value.                                                                                                                                                                                                                                                                                                                                             |
| format        | text              | Format specified using [tokens for date/time formatting](docId\:KUmuKVBfixzZTKuZyE7XZ).<br />Example: `DD.MM.YYYY HH:mm`                                                                                                                                                                                                                                                                |
| timezone      | text              | Optional. The timezone used for the conversion.<br />See [List of tz database time zones](https://en.wikipedia.org/wiki/List_of_tz_database_time_zones#List), column "TZ database name" for the list of recognized timezones.<br />If omitted, Make uses the organization's timezone. You can [edit your time zone](docId\:Bn3S0_8AZJDynxbNzNP_a).<br />Example: `Europe/Prague`, `UTC` |

### Return value and type

Date representation of the given text value according to the specified format and timezone. Type is date.

### Examples

Please note that in the following examples the returned date value is expressed according to ISO 8601, but the actual resulting value is of type date.

```none
parseDate( 2016-12-28 ; YYYY-MM-DD )
= 2016-12-28T00:00:00.000Z
```

```none
parseDate( 2016-12-28 16:03 ; YYYY-MM-DD HH:mm )
= 2016-12-28T16:03:00.000Z
```

```none
parseDate( 2016-12-28 04:03 pm ; YYYY-MM-DD hh:mm a )
= 2016-12-28T16:03:06.000Z
```

```none
parseDate( 1482940986 ; X )
= 2016-12-28T16:03:06.000Z
```

## addDays (date; number)

Returns a new date as a result of adding a given number of days to a date. To subtract days, enter a negative number.

```none
addDays( 2016-12-08T15:55:57.536Z ; 2 )
= 2016-12-10T15:55:57.536Z
```

```none
addDays( 2016-12-08T15:55:57.536Z ; -2 )
= 2016-12-6T15:55:57.536Z
```

## addHours (date; number)

Returns a new date as a result of adding a given number of hours to a date. To subtract hours, enter a negative number.

```none
addHours( 2016-12-08T15:55:57.536Z ; 2 )
= 2016-12-08T17:55:57.536Z
```

```none
addHours( 2016-12-08T15:55:57.536Z ; -2 )
= 2016-12-08T13:55:57.536Z
```

## addMinutes (date; number)

Returns a new date as a result of adding a given number of minutes to a date. To subtract minutes, enter a negative number.

```none
addMinutes( 2016-12-08T15:55:57.536Z ; 2 )
= 2016-12-08T15:57:57.536Z
```

```none
addMinutes( 2016-12-08T15:55:57.536Z ; -2 )
= 2016-12-08T15:53:57.536Z
```

## addMonths (date; number)

Returns a new date as a result of adding a given number of months to a date. To subtract months, enter a negative number.

```none
addMonths( 2016-10-08T15:55:57.536Z ; 2 )
= 2016-12-08T15:57:57.536Z
```

```none
addMonths( 2016-10-08T15:55:57.536Z ; -2 )
= 2016-08-08T15:57:57.536Z
```

## addSeconds (date; number)

Returns a new date as a result of adding a given number of seconds to a date. To subtract seconds, enter a negative number.

```none
addSeconds( 2016-12-08T15:55:57.536Z ; 2 )
= 2016-12-08T15:57:57.536Z
```

```none
addSeconds( 2016-12-08T15:55:57.536Z ; -2 )
= 2016-12-08T15:53:57.536Z
```

## addYears (date; years)

Returns a new date as a result of adding a given number of years to a date. To subtract years, enter a negative number.

```none
addYears( 2016-12-08T15:55:57.536Z ; 2 )
2018-08-08T15:55:57.536Z
```

```none
addYears( 2016-12-08T15:55:57.536Z ; -2 )
2014-08-08T15:55:57.536Z
```

## setSecond (date; number)

Returns a new date with the seconds specified in parameters. Accepts numbers from 0 to 59. If a number is given outside of this range, it will return the date with the seconds from the previous or subsequent minute(s), accordingly.

```none
setSecond( 2015-10-07T11:36:39.138Z ; 10 )
= 2015-10-07T11:36:10.138Z
```

```none
setSecond( 2015-10-07T11:36:39.138Z ; 61 )
= 2015-10-07T11:37:01.138Z
```

## setMinute (date; number)

Returns a new date with the minutes specified in parameters. Accepts numbers from 0 to 59. If a number is given outside of this range, it will return the date with the minutes from the previous or subsequent hour(s), accordingly.

```none
setMinute( 2015-10-07T11:36:39.138Z ; 10 )
= 2015-10-07T11:10:39.138Z
```

```none
setMinute( 2015-10-07T11:36:39.138Z ; 61 )
= 2015-10-07T12:01:39.138Z
```

## setHour (date; number)

Returns a new date with the hour specified in parameters. Accepts numbers from 0 to 59. If a number is given outside of this range, it will return the date with the hour from the previous or subsequent day(s), accordingly.

```none
setHour( 2015-10-07T11:36:39.138Z ; 10 )
= 2015-08-07T06:36:39.138Z
```

```none
setHour( 2015-10-07T11:36:39.138Z ; 61 )
= 2015-08-06T18:36:39.138Z
```

## setDay (date; number/name of the day in english)

Returns a new date with the day specified in parameters. It can be used to set the day of the week, with Sunday as 1 and Saturday as 7. If the given value is from 1 to 7, the resulting date will be within the current (Sunday-to-Saturday) week. If a number is given outside of the range, it will return the day from the previous or subsequent week(s), accordingly.

```none
setDay( 2018-06-27T11:36:39.138Z ; monday )
= 2018-06-25T11:36:39.138Z
```

```none
setDay( 2018-06-27T11:36:39.138Z ; 1 )
= 2018-06-24T11:36:39.138Z
```

```none
setDay( 2018-06-27T11:36:39.138Z ; 7 )
= 2018-06-30T11:36:39.138Z
```

## setDate (date; number)

Returns a new date with the day of the month specified in parameters. Accepts numbers from 1 to 31. If a number is given outside of the range, it will return the day from the previous or subsequent month(s), accordingly.

```none
setDate( 2015-08-07T11:36:39.138Z ; 5 )
= 2015-08-05T11:36:39.138Z
```

```none
setDate( 2015-08-07T11:36:39.138Z ; 32 )
= 2015-09-01T11:36:39.138Z
```

## setMonth (date; number/name of the month in English)

Returns a new date with the month specified in parameters. Accepts numbers from 1 to 12. If a number is given outside of this range, it will return the month in the previous or subsequent year(s), accordingly.

```none
setMonth( 2015-08-07T11:36:39.138Z ; 5 )
= 2015-05-07T11:36:39.138Z
```

```none
setMonth( 2015-08-07T11:36:39.138Z ; 17 )
= 2016-05-07T11:36:39.138Z
```

```none
setMonth( 2015-08-07T11:36:39.138Z ; january )
= 2015-01-07T12:36:39.138Z
```

## setYear (date; number)

Returns a new date with the year specified in parameters.

```none
setYear( 2015-08-07T11:36:39.138Zv ; 2017 )
= 2017-08-07T11:36:39.138Z
```

### Calculate n-th day of the week in a month

If you need to calculate a date corresponding to the n-th day of week in a month (e.g. 1st Tuesday, 3rd Friday, etc.), you can use the following formula:

```text
{{addDays(setDate(1.date; 1); 1.n * 7 - formatDate(addDays(setDate(1.date; 1); "-" + 1.dow); "E"))}}
```

The formula contains the following items:

| Value    | Description                                                                                                                                          |
| -------- | ---------------------------------------------------------------------------------------------------------------------------------------------------- |
| `1.n`    | n-th day: <br />* `1` for **1**st Tuesday
* `2` for **2**nd Tuesday
* `3` for **3**rd Tuesday
* etc.                                                 |
| `2.dow`  | Day of the week:<br />* `1` for Monday
* `2` for Tuesday
* `3` for Wednesday
* `4` for Thursday
* `5` for Friday
* `6` for Saturday
* `7` for Sunday |
| `1.date` | The date determines the month. To calculate n-th day of week in **current** month use the `now` variable                                             |

If you want to calculate only one specific case, e.g. 2nd Wednesday, you may replace the items `1.n` and `2.dow` in the formula with the corresponding numbers. For 2nd Wednesday in the current month you would use the following values:

- `1.n` = `2`
- `1.dow` = `3`
- `1.date` = `now`
- `setDate(now;1)` returns first of current month
- `formatDate(....;E)` returns day of week (1, 2, ... 6)
- see the [original source](https://exceljet.net/formula/get-nth-day-of-week-in-month) for the rest

### Calculate days between dates

::Image[]{src="https://api.archbee.com/api/optimize/yAufeXqD1oGWOPBNi5MAm-8Msh41cFz4bOfwL3TLvMo-20250226-101332.png" size="50" width="432" height="84" position="flex-start" alt="Calculate days between d" darkWidth="432" darkHeight="84" showCaption="false"}

```text
{{round((2.value - 1.value) / 1000 / 60 / 60 / 24)}}
```

The values of `D1` and `D2` above have to be of type date. If they are of type string (e.g. "20.10.2018"), use the `parseDate()` function to convert them to type date.

The `round()` function is used for cases when one of the dates falls within the daylight savings time period and the other does not. In these cases, the difference in hours is by one hour less/more and dividing it by 24 gives a non-integer results.

### Calculate the last day/millisecond of a month

When specifying a date range (e.g. in a search module) spanning the whole previous month as a closed interval (the interval that **includes** both its limit points), it is necessary to calculate the last day of the month.

2019-09-01 ≤ D ≤ **2019-09-30**

```text
{{addDays(setDate(now; 1); -1)}}
```

In some cases, it is necessary to calculate not only the last day of month, but its last millisecond:

2019-09-01T00:00:00.000Z ≤ D ≤ **2019-09-30T23:59:59.999Z**

```text
{{parseDate(parseDate(formatDate(now; "YYYYMM01"); "YYYYMMDD"; "UTC") - 1; "x")}}
```

If the result should respect your timezone settings, simply omit the UTC argument:

```text
{{parseDate(parseDate(formatDate(now; "YYYYMM01"); "YYYYMMDD") - 1; "x")}}
```

However, it is preferable to use a half-open interval instead (the interval that **excludes** one of its limit points), specifying the first day of the following month instead and replacing the **less or equal than** operator with **less than**:

2019-09-01 ≤ D **\< 2019-10-01**

2019-09-01T00:00:00.000Z ≤ D **\< 2019-10-01T00:00:00.000Z**

### Transform seconds into hours, minutes and second

```text
{{floor(1.seconds / 3600)}}:{{floor((1.seconds % 3600) / 60)}}:{{((1.seconds % 3600) % 60)}}
```

Values of `second` should be number type. This function is suited only if the second value is less than 86400 ( less than a day ).