Date Formulas

Video guide: Learn how to transform dates with formula mode

Date and datetime formulas allow you to work with date and time datapills. These formulas are added to the allowlists for Ruby methods (opens new window), and therefore not all Ruby methods are supported. Reach out to us (opens new window) to add additional formulas to the allowlist.

Date Arithmetic

Use the following keywords to perform arithmetic with date and datetime data:

• seconds
• minutes
• days
• months
• years

When combined with a formula, you can perform addition and subtraction.

Sample usage

Date Arithmetic	Output
"2020-01-01".to_date + 2.days	"2020-01-03"
"2020-01-01".to_date - 2.days	"2019-12-30"
"2020-01-01".to_date + 2.months	"2020-03-01"
"2020-01-01".to_date - 2.months	"2019-11-01"
"2020-01-01".to_date + 2.years	"2022-01-01"
"2020-01-01".to_date - 2.years	"2018-01-01"

---

now

Returns the time and date at runtime in US Pacific Time Zone (PST).

Sample usage

Formula	Result
now	"2022-02-01T07:00:00.000000-08:00"
now + 8.hours	"2022-02-01T15:00:00.000000-08:00"
now + 2.days	"2022-02-03T07:00:00.000000-08:00"

How it works

The formula calculates the timestamp when the a job is being processed. Each step using this formula will return the timestamp at which the step runs.

Output datapill

If you only want the date without the time, try using the today formula instead.

See also

• today: Returns the date at runtime.
• in_time_zone: Converts a time value to a different time zone.

---

today

Returns the date at runtime in US Pacific Time Zone.

Sample usage

Formula	Result
today	"2022-02-01"
today + 8.hours	"2022-02-01T15:00:00.000000-08:00"
today + 2.days	"2022-02-03"

How it works

The formula calculates the timestamp when the a job is being processed. Each step using this formula will return the date at which the step runs.

Output datapill

If you want the date and time, try using the now formula instead.

See also

• now: Returns the time and date at runtime.
• in_time_zone: Converts a time value to a different time zone.

---

from_now

Returns an future timestamp by a specified time duration. The timestamp is calculated at runtime.

Syntax

Unit.from_now

• Unit - A time value to offset.

Sample usage

Formula	Result
30.seconds.from_now	"2022-02-01T07:00:30.000000-08:00"
2.months.from_now	"2022-04-01T07:00:00.000000-08:00"
3.days.from_now	"2022-02-04T07:00:00.000000-08:00"

How it works

The formula calculates the current timestamp and offsets by a specified time duration. This timestamp is calculated when the a job is being processed. Each step using this formula will return a timestamp for each step that runs.

Units

You can use any of these units: seconds, minutes, hours, days, months, or years.

See also

• ago: Returns an earlier timestamp by a specified time duration.
• now: Returns the time and date at runtime.
• today: Returns the date at runtime.

---

ago

Returns an earlier timestamp by a specified time duration. The timestamp is calculated at runtime.

Syntax

Unit.ago

• Unit - A time value to offset.

Sample usage

Formula	Result
2.months.ago	"2020-10-04 14:45:29 -0700"
3.days.ago	"2020-12-01 14:45:29 -0700"
30.seconds.ago	"2020-12-04 14:15:29 -0700"

How it works

The formula calculates the current timestamp and offsets by a specified time duration. This timestamp is calculated when the a job is being processed. Each step using this formula will return a timestamp for each step that runs.

Units

You can use any of these units: seconds, minutes, hours, days, months, or years.

See also

• from_now: Returns an future timestamp by a specified time duration.
• now: Returns the time and date at runtime.
• today: Returns the date at runtime.

---

wday

Returns day of the week. Sunday returns 0, Monday returns 1.

Syntax

Date.wday

• Date - A date or datetime datatype.

Sample usage

Example	Result
today.wday	4
"01/12/2020".to_date(format:"DD/MM/YYYY").wday	2

How it works

The formula calculates the current day when the a job is being processed. The day of the week is converted into an integer output. Sunday = 0, Monday = 1.

Quicktip: Convert to date datatype

This formula only works with date or datetime datatype. Use to_date to convert a string into a date datatype.

See also

• yday: Returns the day number of the year.
• yweek: Returns the week number of the year.

---

yday

Returns day number of the year.

Syntax

Date.yday

• Date - A date or datetime datatype.

Sample usage

Example	Result
today.yday	338
"2020-01-01".to_date(format:"YYYY-MM-DD").yday	1
"2020-02-01".to_date(format:"YYYY-MM-DD").yday	32

How it works

The formula calculates the current day when the a job is being processed. The day of the year is converted into an integer output.

Quicktip: Convert to date datatype

This formula only works with date or datetime datatype. Use to_date to convert a string into a date datatype.

See also

• wday: Returns the day number of the week.
• yweek: Returns the week number of the year.

---

yweek

Returns week number of the year.

Syntax

Date.yweek

• Date - A date or datetime datatype.

Sample usage

Example	Result
today.yweek	49
"2020-01-01".to_date(format:"YYYY-MM-DD").yweek	1
"2020-02-01".to_date(format:"YYYY-MM-DD").yweek	5

How it works

The formula calculates the current day when the a job is being processed. The week of the year is converted into an integer output.

Quicktip: Convert to date datatype

This formula only works with date or datetime datatype. Use to_date to convert a string into a date datatype.

See also

• wday: Returns the day number of the week.
• yday: Returns the day number of the year.

---

beginning_of_hour

Returns datetime for top-of-the-hour for a given datetime.

Syntax

Datetime.beginning_of_hour

• Datetime - An input datetime.

Sample usage

Formula	Result
today.to_time.beginning_of_hour	"2020-12-02T16:00:00.000000-07:00"
"2020-06-01T01:30:45.000000+00:00".beginning_of_hour	"2020-06-01T01:00:00.000000+00:00"
"2020-06-01".to_time.beginning_of_hour	"2020-06-01T00:00:00.000000+00:00"

---

beginning_of_day

Returns datetime for midnight on date of a given date/datetime.

Syntax

Date.beginning_of_day

• Date - An input date or datetime.

Sample usage

Formula	Result
today.beginning_of_day	"2020-12-02T00:00:00.000000-07:00"
"2020-06-01".to_date.beginning_of_day	"2020-06-01T00:00:00.000000+00:00"
"2020-06-01T01:30:45.000000+00:00".beginning_of_day	"2020-06-01T00:00:00.000000+00:00"

---

beginning_of_week

Returns date for the start of the week (Monday) for a given date/timestamp.

Syntax

Date.beginning_of_week

• Date - An input date or datetime.

Sample usage

Formula	Result
today.beginning_of_week	"2020-11-30T00:00:00.000000+00:00"
"2020-06-01".to_date.beginning_of_week	"2020-06-01T00:00:00.000000+00:00"
"2020-06-01T01:30:45.000000+00:00".beginning_of_week	"2020-06-01T00:00:00.000000+00:00"

---

beginning_of_month

Returns first day of the month for a given date/datetime.

Syntax

Date.beginning_of_month

• Date - An input date or datetime.

Sample usage

Formula	Result
today.beginning_of_month	"2020-12-01T00:00:00.000000+00:00"
"2020-06-01".to_date.beginning_of_month	"2020-06-01T00:00:00.000000+00:00"
"2020-06-01T01:30:45.000000+00:00".beginning_of_month	"2020-06-01T00:00:00.000000+00:00"

---

beginning_of_year

Returns first day of the year for a given date/datetime.

Syntax

Date.beginning_of_year

• Date - An input date or datetime.

Sample usage

Formula	Result
today.beginning_of_year	"2020-01-01T00:00:00.000000+00:00"
"2020-06-01".to_date.beginning_of_year	"2020-01-01T00:00:00.000000+00:00"
"2020-06-01T01:30:45.000000+00:00".beginning_of_year	"2020-01-01T00:00:00.000000+00:00"

---

end_of_month

Returns last day of the month for a given date/datetime. This formula will return a date or datetime based on the input data.

Syntax

Date.end_of_month

• Date - An input date or datetime.

Sample usage

Formula	Result
today.end_of_month	"2020-12-31"
"2020-06-01".to_date.end_of_month	"2020-06-30"
"2020-06-01T01:30:45.000000+00:00".to_time.end_of_month	"2020-06-30T23:59:59.999999+00:00"

---

strftime

Returns a datetime input as a user-defined string.

Syntax

Date.strftime(format)

• Date - An input date or datetime.
• format - The format of the user-defined datetime written as a string.

Sample usage

Formula	Result
"2020-06-05T17:13:27.000000-07:00".strftime("%Y/%m/%d")	"2020/06/05"
"2020-06-05T17:13:27.000000-07:00".strftime("%Y-%m-%dT%H:%M:%S%z")	"2020-06-05T17:13:27-0700"
"2020-06-05T17:13:27.000000-07:00".strftime("%B %e, %l:%M%p")	"June 5, 5:13 pm"
"2020-06-05T17:13:27.000000-07:00".strftime("%A, %d %B %Y %k:%M")	"Friday, 05 June 2020 0:00"

Parameters

As shown above, each code (%B, %e, %I etc.) refers to a specific element of datetime. Static text and punctuation can also be added, such as commas (,), slashes (/), and colons (:). Here's a list of commonly used codes in Workato:

Code	Meaning	Example (2020-06-05T17:13:27.000000-07:00)
%Y	Year with century	2020
%m	Month with zero-prefix	06
%B	Full month name	June
%b	Abbreviated month name	Jun
%d	Day of the month with zero-prefix	05
%e	Day of the month without zero-prefix	5
%H	Hour of the day (24-hour)	17
%k	Hour of day without 0 prefix (24-hour)	17
%I (capital i)	Hour of the day (12-hour)	05
%l (lowercase L)	Hour of day without 0 prefix (12-hour)	5
%p	AM or PM	PM
%M	Minute of the hour	13
%S	Second of the minute	27
%L	milliseconds of the minute	000
%z	Time zone offset from UTC	-0700
%:z	Time zone formatted offset from UTC	-07:00
%Z	Time zone abbrev. name	UTC
%A	Full day name	Friday

To access the full list, check out the Ruby documentation (opens new window)

How it works

Allows the user to define a datetime format. Returns the datetime input in the specified format.

Input datatype

The input must be a date or datetime datatype. You can use the to_date formula to convert a string into a date datatype.

See also

• to_date: Returns a date in date datatype.

---

in_time_zone

Converts a date or datetime to a different timezone using timezone names from the IANA time zone database (opens new window). This formula will return a datetime.

Syntax

Date.in_time_zone(format)

• Date - An input date or datetime.
• format - The target timezone. If not specified, this formula will return the timezone defined by the data center your Workato account is hosted in.

Sample usage

Formula	Result
today.in_time_zone("America/New_York")	"2020-12-01T20:00:00.000000-04:00"
today.to_time.in_time_zone("America/New_York")	"2020-12-01T20:00:00.000000-04:00"
"2020-06-01".to_time.in_time_zone	"2020-05-31T20:00:00.000000-04:00"
"2020-06-01T01:30:45.000000+00:00".in_time_zone	"2020-05-31T12:30:00.000000-05:00"

---

dst?

Returns true if the input datetime is within Daylight Savings Time.

Syntax

Datetime.dst?

• Datetime - An input date or datetime.

Sample usage

Formula	Result
today.dst?	false
today.in_time_zone("America/New_York").dst?	true
"2020-06-01".in_time_zone("America/New_York").dst?	true
"2020-09-06T18:30:15.671720-05:00".dst?	true

Regions reference

Refer to the following table for the timezone name to use in the formula.

Region	Timezone to use in formula	UTC zone	DST offset?
International Date Line West	Pacific/Midway	UTC-11
Midway Island	Pacific/Midway	UTC-11
American Samoa	Pacific/Pago_Pago	UTC-11
Hawaii	Pacific/Honolulu	UTC-10
Alaska	America/Juneau	UTC-9	✅
Pacific Time (US & Canada)	America/Los_Angeles	UTC-8	✅
Tijuana	America/Tijuana	UTC-8	✅
Mountain Time (US & Canada)	America/Denver	UTC-7	✅
Arizona	America/Phoenix	UTC-7
Chihuahua	America/Chihuahua	UTC-7	✅
Mazatlan	America/Mazatlan	UTC-7	✅
Central Time (US & Canada)	America/Chicago	UTC-6	✅
Saskatchewan	America/Regina	UTC-6
Guadalajara	America/Mexico_City	UTC-6	✅
Mexico City	America/Mexico_City	UTC-6	✅
Monterrey	America/Monterrey	UTC-6	✅
Central America	America/Guatemala	UTC-6
Eastern Time (US & Canada)	America/New_York	UTC-5	✅
Indiana (East)	America/Indiana/Indianapolis	UTC-5	✅
Bogota	America/Bogota	UTC-5
Lima	America/Lima	UTC-5
Quito	America/Lima	UTC-5
Atlantic Time (Canada)	America/Halifax	UTC-4	✅
Caracas	America/Caracas	UTC-4
La Paz	America/La_Paz	UTC-4
Santiago	America/Santiago	UTC-4	✅
Georgetown	America/Guyana	UTC-4
Newfoundland	America/St_Johns	UTC-3:30	✅
Brasilia	America/Sao_Paulo	UTC-3
Buenos Aires	America/Argentina/Buenos_Aires	UTC-3
Montevideo	America/Montevideo	UTC-3
Greenland	America/Godthab	UTC-3	✅
Mid-Atlantic	Atlantic/South_Georgia	UTC-2
Azores	Atlantic/Azores	UTC-1	✅
Cape Verde Is.	Atlantic/Cape_Verde	UTC-1
Dublin	Europe/Dublin	UTC-1	✅
Lisbon	Europe/Lisbon	UTC+0	✅
Edinburgh	Europe/London	UTC+0	✅
London	Europe/London	UTC+0	✅
Monrovia	Africa/Monrovia	UTC+0
UTC	Etc/UTC	UTC+0
Casablanca	Africa/Casablanca	UTC+1
Belgrade	Europe/Belgrade	UTC+1	✅
Bratislava	Europe/Bratislava	UTC+1	✅
Budapest	Europe/Budapest	UTC+1	✅
Ljubljana	Europe/Ljubljana	UTC+1	✅
Prague	Europe/Prague	UTC+1	✅
Sarajevo	Europe/Sarajevo	UTC+1	✅
Skopje	Europe/Skopje	UTC+1	✅
Warsaw	Europe/Warsaw	UTC+1	✅
Zagreb	Europe/Zagreb	UTC+1	✅
Brussels	Europe/Brussels	UTC+1	✅
Copenhagen	Europe/Copenhagen	UTC+1	✅
Madrid	Europe/Madrid	UTC+1	✅
Paris	Europe/Paris	UTC+1	✅
Amsterdam	Europe/Amsterdam	UTC+1	✅
Berlin	Europe/Berlin	UTC+1	✅
Bern	Europe/Zurich	UTC+1	✅
Zurich	Europe/Zurich	UTC+1	✅
Rome	Europe/Rome	UTC+1	✅
Stockholm	Europe/Stockholm	UTC+1	✅
Vienna	Europe/Vienna	UTC+1	✅
West Central Africa	Africa/Algiers	UTC+1
Bucharest	Europe/Bucharest	UTC+2	✅
Cairo	Africa/Cairo	UTC+2
Helsinki	Europe/Helsinki	UTC+2	✅
Kyiv	Europe/Kiev	UTC+2	✅
Riga	Europe/Riga	UTC+2	✅
Sofia	Europe/Sofia	UTC+2	✅
Tallinn	Europe/Tallinn	UTC+2	✅
Vilnius	Europe/Vilnius	UTC+2	✅
Athens	Europe/Athens	UTC+2	✅
Jerusalem	Asia/Jerusalem	UTC+2	✅
Harare	Africa/Harare	UTC+2
Pretoria	Africa/Johannesburg	UTC+2
Kaliningrad	Europe/Kaliningrad	UTC+2
Istanbul	Europe/Istanbul	UTC+3
Minsk	Europe/Minsk	UTC+3
Moscow	Europe/Moscow	UTC+3
St. Petersburg	Europe/Moscow	UTC+3
Kuwait	Asia/Kuwait	UTC+3
Riyadh	Asia/Riyadh	UTC+3
Nairobi	Africa/Nairobi	UTC+3
Baghdad	Asia/Baghdad	UTC+3
Tehran	Asia/Tehran	UTC+3:30	✅
Volgograd	Europe/Volgograd	UTC+4
Samara	Europe/Samara	UTC+4
Abu Dhabi	Asia/Muscat	UTC+4
Muscat	Asia/Muscat	UTC+4
Baku	Asia/Baku	UTC+4
Tbilisi	Asia/Tbilisi	UTC+4
Yerevan	Asia/Yerevan	UTC+4
Kabul	Asia/Kabul	UTC+4:30	✅
Ekaterinburg	Asia/Yekaterinburg	UTC+5
Islamabad	Asia/Karachi	UTC+5
Karachi	Asia/Karachi	UTC+5
Tashkent	Asia/Tashkent	UTC+5
Sri Jayawardenepura	Asia/Colombo	UTC+5:30	✅
Chennai	Asia/Kolkata	UTC+5:30	✅
Kolkata	Asia/Kolkata	UTC+5:30	✅
Mumbai	Asia/Kolkata	UTC+5:30	✅
New Delhi	Asia/Kolkata	UTC+5:30	✅
Kathmandu	Asia/Kathmandu	UTC+5:45	✅
Astana	Asia/Dhaka	UTC+6
Dhaka	Asia/Dhaka	UTC+6
Almaty	Asia/Almaty	UTC+6
Urumqi	Asia/Urumqi	UTC+6
Rangoon	Asia/Rangoon	UTC+6:30	✅
Novosibirsk	Asia/Novosibirsk	UTC+7
Bangkok	Asia/Bangkok	UTC+7
Hanoi	Asia/Bangkok	UTC+7
Jakarta	Asia/Jakarta	UTC+7
Krasnoyarsk	Asia/Krasnoyarsk	UTC+7
Beijing	Asia/Shanghai	UTC+8
Chongqing	Asia/Chongqing	UTC+8
Hong Kong	Asia/Hong_Kong	UTC+8
Kuala Lumpur	Asia/Kuala_Lumpur	UTC+8
Singapore	Asia/Singapore	UTC+8
Taipei	Asia/Taipei	UTC+8
Perth	Australia/Perth	UTC+8
Irkutsk	Asia/Irkutsk	UTC+8
Ulaanbaatar	Asia/Ulaanbaatar	UTC+8
Seoul	Asia/Seoul	UTC+9
Osaka	Asia/Tokyo	UTC+9
Sapporo	Asia/Tokyo	UTC+9
Tokyo	Asia/Tokyo	UTC+9
Yakutsk	Asia/Yakutsk	UTC+9
Darwin	Australia/Darwin	UTC+9:30
Adelaide	Australia/Adelaide	UTC+9:30	✅
Canberra	Australia/Melbourne	UTC+10	✅
Melbourne	Australia/Melbourne	UTC+10	✅
Sydney	Australia/Sydney	UTC+10	✅
Brisbane	Australia/Brisbane	UTC+10
Hobart	Australia/Hobart	UTC+10	✅
Vladivostok	Asia/Vladivostok	UTC+10
Guam	Pacific/Guam	UTC+10
Port Moresby	Pacific/Port_Moresby	UTC+10
Magadan	Asia/Magadan	UTC+11
Srednekolymsk	Asia/Srednekolymsk	UTC+11
Solomon Is.	Pacific/Guadalcanal	UTC+11
New Caledonia	Pacific/Noumea	UTC+11
Fiji	Pacific/Fiji	UTC+12	✅
Kamchatka	Asia/Kamchatka	UTC+12
Marshall Is.	Pacific/Majuro	UTC+12
Auckland	Pacific/Auckland	UTC+12	✅
Wellington	Pacific/Auckland	UTC+12	✅
Nuku'alofa	Pacific/Tongatapu	UTC+13
Tokelau Is.	Pacific/Fakaofo	UTC+13
Samoa	Pacific/Apia	UTC+13
Chatham Is.	Pacific/Chatham	UTC+13:45	✅

---

to_date

This formula converts the input data into a date. Returns the date formatted as YYYY-MM-DD.

Syntax

String.to_date(format: format)

• String - An input datetime or a string that describes a date or datetime.
• format - (optional) The date format of the input written as a string. If not specified, Workato will parse the input string automatically.

Sample usage

Formula	Result
"23-01-2020 10:30 pm".to_date(format: "DD-MM-YYYY")	"2020-01-23"
"01-23-2020 10:30 pm".to_date(format: "MM-DD-YYYY")	"2020-01-23"
"2020/01/23".to_date(format: "YYYY/MM/DD")	"2020-01-23"

How it works

Converts the input data into a date datatype.

Input data best practice

It is recommended to specify the input data format. If the format is not specified, Workato will automatically parse the input string.

The input string must resemble a date for this formula to work.

See also

• strftime: Returns datetime is a custom format.
• to_time: Converts a string to an ISO timestamp.

---

to_time

Converts a string to an ISO timestamp. The response will use the UTC timezone (+00:00).

Syntax

String.to_time(format)

• String - An input string that describes a date or datetime.
• format - The format of the user-defined datetime written as a string.

Sample usage

Formula	Result
"2020-04-02T12:30:30.462659-07:00".to_time	"2020-04-02T19:30:30.462659+00:00"
"2020-04-02".to_time	"2020-04-02T00:00:00.000000+00:00"

How it works

Converts the input string into a datetime datatype. The output datetime will be converted to the UTC timezone (+00:00).

Autofill time

If the input data does not include the time, the output will default to 00:00:00.000000 +00:00.

See also

• strftime: Returns datetime is a custom format.
• to_date: This formula converts the date-like input into a date. Returns the date formatted as YYYY-MM-DD.

Parameters

As shown above, each code (%B, %e, %I, etc.) refers to a specific element of datetime. Static text and punctuation can also be added, such as commas (,), slashes (/), and colons (:). Refer to the following list for frequently used codes:

Code	Meaning	Example (2020-06-05T17:13:27.000000-07:00)
%Y	Year with century	2020
%m	Month with zero-prefix	06
%B	Full month name	June
%b	Abbreviated month name	Jun
%d	Day of the month with zero-prefix	05
%e	Day of the month without zero-prefix	5
%H	Hour of the day (24-hour)	17
%k	Hour of day without 0 prefix (24-hour)	17
%I (capital i)	Hour of the day (12-hour)	05
%l (lowercase L)	Hour of day without 0 prefix (12-hour)	5
%p	AM or PM	PM
%M	Minute of the hour	13
%S	Second of the minute	27
%L	milliseconds of the minute	000
%z	Time zone offset from UTC	-0700
%:z	Time zone formatted offset from UTC	-07:00
%Z	Time zone abbrev. name	UTC
%A	Full day name	Friday

To access the full list, check out the Ruby documentation (opens new window)

---

to_i

Convert datetime into epoch time. Returns an epoch time in UTC (+00:00).

Syntax

Datetime.to_i

• Datetime - An input datetime.

Sample usage

Formula	Result
today.to_time.to_i	1645660800
now.to_i	1645714000

How it works

Converts the input datetime into an integer, it will return epoch time in seconds, not milliseconds. The output datetime will be converted to the UTC timezone (+00:00).

Converting between Epoch time to datetime

Convert time formats easily with Workato formulas.

How to Convert Human Readable Time to Epoch Time

Use to_i to convert a datetime datapill to epoch time (in UTC). Learn more about how it works.

How to Convert Epoch Time to Human Readable Time

Use the formula below to convert an epoch time to human readable datetime.

Note that the output will be in UTC timezone (+00:00).

"1970-01-01".to_time +Epoch time.seconds

If you would like to convert epoch time to a specific timezone, you must specify it with in_time_zone.

"1970-01-01".to_time.in_time_zone("US/Pacific") +Epoch time.seconds

Wrong datatype: undefined method `to_i`

Epoch time requires a datetime datapill. If you are using a date datapill, it will cause an error.

Use to_time to convert a date into a datetime before converting to epoch time.

See also

• to_time: Converts a string to an ISO timestamp.
• to_date: This formula converts the date-like input into a date. Returns the date formatted as YYYY-MM-DD.
• in_time_zone: Converts a time value to a different time zone.