Date & Time Formulas
=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 TuesdayExample 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 TuesdayExample 2:=currentdate() = 2021-05-06 02:01:49.317275816=NOW() = 2021-05-06 02:01:49.317275816Example 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:36Example 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-03Example 2:=date($"Birthdays", "%m/%d/%C", "%Y-%m-%d") = 1982-06-03=datevalue($"Birthdays", "%m/%d/%C", "%Y-%m-%d") = 1982-06-03Example 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:00Example 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:00Example 2:= time($"Time of Purchase")=03:16:09Example 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.
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 |
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
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). |
Example 1:=datedif("2023-02-01", "2023-02-02", "D") => 1Example 2:=datedif("2030-01-01", "2020-01-01", "Y") => -10Example 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.
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 |
Example 1:=EDate("2023-02-01", 3) => 2023-05-01Example 2:=EDate("2023-02-01", -30) => 2018-02-01Example 3:=EDate("2030-01-01", ($"Years" * 12))
Last modified 14d ago