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.
Arguments
Argument | Required (Y/N) | Description |
timezone | N | Desired timezone of the output |
output_format | N | Date format 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.
Arguments
Argument | Required (Y/N) | Description |
timezone | N | Desired timezone of the output |
output_format | N | Datetime format 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.
Arguments
Argument | Required (Y/N) | Description |
timezone | N | Desired timezone of the output |
output_format | N | Time format 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 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 |
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
Notice in example 3, no input date format is specified, instead the input is inferred by Osmos
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 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 |
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
Notice in example 2, no input date format is specified, instead the input is inferred by Osmos
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 of the input value |
output_format | N | Desired time format 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.
Arguments
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 |
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.
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
Arguments
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). |
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
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 |
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.
Arguments
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. |
Examples
Example 1:
=yearweektodate(2023, 3, "sun") => "2023-01-22"
Example 2:
=yearweektodate(2023, 5) => "2023-02-05"
Example 3:
=yearweektodate(2023, 53) => ERROR
Last updated