Date & Time Formulas
Last updated
Was this helpful?
Last updated
Was this helpful?
=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 .
Argument
Required (Y/N)
Description
timezone
N
output_format
N
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])
Argument
Required (Y/N)
Description
timezone
N
output_format
N
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])
Argument
Required (Y/N)
Description
timezone
N
output_format
N
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.
Argument
Required (Y/N)
Description
input
Y
Value in which the formula will attempt to convert to a date format
input_format
N
output_format
N
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
=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.
Argument
Required (Y/N)
Description
input
Y
Value in which the formula will attempt to convert to a datetime format
input_format
N
output_format
N
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
=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.
Argument
Required (Y/N)
Description
input
Y
Value in which the formula will attempt to convert to a time format
input_format
N
output_format
N
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.
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.
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") => 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.
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-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.
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.
Example 1:
=yearweektodate(2023, 3, "sun") => "2023-01-22"
Example 2:
=yearweektodate(2023, 5) => "2023-02-05"
Example 3:
=yearweektodate(2023, 53) => ERROR
Desired of the output
Date of the output
Outputs the current date and time for the timezone and datetime format given. You can find all the timezone specifiers .
Desired of the output
Datetime of the output
Outputs the current time for the timezone and time format given. You can find all the timezone specifiers .
Desired of the output
Time of the output
Date of the input value, this can be left blank in order to infer the date format
Desired date of the output
Date of the input value, this can be left blank in order to infer the date format
Desired datetime of the output
Time of the input value
Desired time of the output