Date & Time Formulas
Last updated
Last updated
=currentdate([timezone], [output_format])
=TODAY([timezone], [output_format])
Outputs the current date for the timezone and date format given. You can find all the timezone specifiers here.
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([timezone], [output_format])
=NOW([timezone], [output_format])
Outputs the current date and time for the timezone and datetime format given. You can find all the timezone specifiers here.
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([timezone], [output_format])
Outputs the current time for the timezone and time format given. You can find all the timezone specifiers here.
Example 1:
=currenttime() = 14:26:36
Example 2:
=currenttime("America/Chicago", "%H:%M:%S") = 02:01:49
=date(<input>, [input_format], [output_format])
=datevalue(<input>, [input_format], [output_format])
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.
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
Notice in example 3, no input date format is specified, instead the input is inferred by Osmos
=datetime(<input>, [input_format], [output_format])
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.
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
Notice in example 2, no input date format is specified, instead the input is inferred by Osmos
=time(<input>, [input_format], [output_format])
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.
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(<input>,<input_timezone>,<output_timezone>,[output_format])
Attempts to convert the input value into another timezone.
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(<input1>,<input2>,<output_unit>)
=DateDiff(<input1>,<input2>,<output_unit>)
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.
Note: Both DateDif and DateDiff are supported.
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
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(<input1>,<input2>)
Calculates a date based on an initial date value and a count of Months.
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(<input1>,<input2>)
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.
Example 1:
=yearweektodate(2023, 3, "sun") => "2023-01-22"
Example 2:
=yearweektodate(2023, 5) => "2023-02-05"
Example 3:
=yearweektodate(2023, 53) => ERROR
Argument
Required (Y/N)
Description
timezone
N
Desired timezone of the output
output_format
N
Date format of the output
Argument
Required (Y/N)
Description
timezone
N
Desired timezone of the output
output_format
N
Datetime format of the output
Argument
Required (Y/N)
Description
timezone
N
Desired timezone of the output
output_format
N
Time format of the output
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 of the input value, this can be left blank in order to infer the date format
output_format
N
Desired date format of the output
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 of the input value, this can be left blank in order to infer the date format
output_format
N
Desired datetime format of the output
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 of the input value
output_format
N
Desired time format of the output
Argument
Required (Y/N)
Description
input
Y
Value in which the formula will attempt to convert to a new timezone.
input_timezone
Y
Timezone of the input
output_timezone
Y
Desired timezone output
output_format
N
Datetime format of the output
Argument
Required (Y/N)
Description
input1
Y
Date value which serves as the starting value for the date evaluation.
input2
Y
Date value which will be "subtracted" from input1 to determine the difference between the dates
output_unit
Y
Desired output unit of measure. Accepts Y (years), M (months), or D (days).
Argument
Required (Y/N)
Description
input1
Y
Date value which serves as the initial value for the date evaluation
input2
Y
An integer representing Months to be added to the initial date value
Argument
Required (Y/N)
Description
input1
Y
Year which serves as the year for the week calculation.
input2
Y
An integer representing Week to determine the initial value for the date.