Edit this page

# Date formulas

Workato supports a variety of date and datetime formulas. Formulas in Workato are whitelisted Ruby methods, and therefore not all Ruby methods are supported. You can always reach out to us to add additional formulas to the whitelist. Syntax and functionality for these formulas are generally unchanged. Take note that most formulas will return an error and stop the job if it tries to operate on nulls (expressed as nil in Ruby), except for present?, presence and blank?.

You can refer to the complete Ruby documentation for Time here (opens new window).

# Basics

# now

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

# Sample usage

Formula Result
now "2020-12-02 14:45:29 -0700"
now + 2.days "2020-12-04 14:45:29 -0700"
now + 8.hours "2020-12-02 22:45:29 -0700"

# 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 "2020-12-02"
today + 2.days "2020-12-04"
today + 8.hours "2020-12-02 08:00:00 -0700"

# 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 want the date and time, try using the now formula sinstead.

# 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
2.months.from_now "2021-02-04 14:45:29 -0700"
3.days.from_now "2020-12-07 14:45:29 -0700"
30.seconds.from_now "2020-12-04 15: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

  • 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.

Quickip: 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.

Quickip: 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.

Quickip: 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.

# Date arithmetics

We can make use of certain keywords such as days, months, years, minutes and seconds to perform date arithmetics to add or subtract days, months, years, minutes and seconds from dates and datetimes.

# 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"

# Getting first/last timestamp of the current/next periods

Using a combination of date formulas, and date arithmetics, we can easily obtain the first and last days of a current or subsequent time period using some helper formulas. Not all time periods are supported just yet.

# 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 of the previous monday for a given date/datetime.

# 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.beginning_of_month

  • Date - An input date or datetime.

# Sample usage

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

# Display conversion

# 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:13PM"
"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 time value to a different timezone. This formula will return a date or datetime based on the input data.

# Syntax

Date.in_time_zone(format)

  • Date - An input date or datetime.
  • format - (optional) The target timezone. If not specified, this formula will return the timezone defined by your Workato accounts.

# Sample usage

Formula Result
today.in_time_zone "2020-12-02"
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"

# How it works

This formula uses the list of timezone names from the IANA time zone database. The output will be an equivalent time in a different timezone. Find out more here (opens new window)

# dst?

Returns true if the input datatime 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 ✅

# Converting datetime to date

To convert a date data type into a datetime data, or vice versa, use the to_date or to_time formulas.

# to_date

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

# Syntax

String.first(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:30PM".to_date(format: "DD-MM-YYYY") "2020-01-23"
"01-23-2020 10:30PM".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

  • String - An input string that describes a date or datetime.

# 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 input data into a date. Returns the date formatted as YYYY-MM-DD.

# Conditionals

# blank?

This formula checks the input string and returns true if it is an empty string or if it is null.

# Syntax

Input.blank?

  • Input - An input datapill. It can be a string, number, date, or datetime datatype.

# Sample usage

Formula Result
"Any Value".blank? false
123.blank? false
0.blank? false
"".blank? true

# How it works

If the input is null or an empty string, the formula will return false. For any other data, it returns true.

# See also

  • presence: Returns the data if it exists, returns nil if it does not.
  • present?: Returns true if there is a valid input.

# present?

This formula will check the input and if there is a value present, it will return true. If the input is nil, an empty string or an empty list, the formula will return false.

# Syntax

Input.present?

  • Input - An input datapill. It can be a string, number, date, or list datatype.

# Sample usage

Formula Result
"Any Value".present? true
123.present? true
0.present? true
"2017-04-02T12:30:00.000000-07:00".present? true
nil.present? false
"".present? false
[].present? false

# How it works

If the input is null, an empty string or an empty list, the formula will return false. For any other data, it returns true.

Evaluating a list with nil values

  • Only an empty list will return false.

[].present? returns false.

  • A list with nil and empty string will return true.

[nil,""].present? returns true.

# See also

  • presence: Returns the data if it exists, returns nil if it does not.
  • blank?: Returns nil if the the data does not exist or if the string consist of only white spaces.

# presence

Returns the data if it exists, returns nil if it does not.

# Syntax

Input.presence

  • Input - An input datapill. It can be a string, number, date, or datetime datatype.

# Sample usage

Formula Result
nil.presence nil
"".presence nil
"Any Value".presence "Any Value"
45.0.presence 45.0
0.presence 0

# How it works

If the input is null or an empty string, the formula will return nil. For any other data, it returns the orignal input data.

# See also

  • blank?: Returns nil if the the data does not exist or if the string consist of only white spaces.
  • present?: Returns true if there is a valid input.

← List formulas →

Didn’t find what you are looking for?
Search our resource library View getting started guides, tutorials, training videos, webinars and FAQ Ask our community View questions and answers from other users and Workato experts. File a support ticket File a support ticket for critical issues that require a higher level of support.