How to convert a text to dateTime

Create a dateTime local variable.
Use todatetime(text!excelDateTimeColumn) to convert text to dateTime.
Use format(local!convertedDateTime, "MM/dd/yyyy hh:mm a") to format the date.
Write the text!desiredOutput variable to the database (formatted date).

Example for converting "2024-01-31 09:14:36" to "1/31/2024 9:14 AM" *

 Step 1: Parse the DateTime text 
!dateTime = parseDateTime("2024-01-31 09:14:36", "yyyy-MM-dd HH:mm:ss");

Step 2: Format the DateTime to desired format 
!formattedDateTime = toFormattedString(!dateTime, "M/d/yyyy h:mm a");

Output the formatted DateTime 
!formattedDateTime

Note:Apply this logic for each dateTime string you extract from your Excel sheet. Remember, before writing to the database, ensure that your CDT/columns designed to hold DateTime values are indeed set to accept DateTime data types.

Hi, thanks for your response. It shows me like format function is not available.

Hi, thanks for your response. I want the output in Date time format, not as a text.

Instead of format, use value with a format string:
value(local!convertedDateTime, "MM/dd/yyyy hh:mm a")

/* Example conversion assuming 'excelDateTimeText' is the Text variable with your date-time string */

/* First, replace the '-' with '/' to match Appian's date format */
!dateString = replace(excelDateTimeText, "-", "/");

/* Then, use the toDatetime() function to convert the string to DateTime data type */
!dateTime = toDatetime(!dateString, "MM/dd/yyyy HH:mm:ss");

/* Now, '!dateTime' is an Appian DateTime data type */

Sorry,we dont have parseDateTime function.

I tried this - value(local!convertedDateTime, "MM/dd/yyyy hh:mm a") but It gives me the output as 1/30/2024 11:14 PM GMT-05:00 for this input "2024-01-31 09:14:36".

Code:

a!localVariables(
local!dateAndTime: split("2024-01-31 09:14:36", " "),
local!date: split(local!dateAndTime[1], "-"),
local!time: split(local!dateAndTime[2], ":"),
local!convertedDateTime:datetime(
local!date[1],
local!date[2],
local!date[3],
tointeger(local!time[1]),
tointeger(local!time[2]),
tointeger(local!time[3]),
),
value(local!convertedDateTime, "MM/dd/yyyy hh:mm a")
)

But my expected output is 1/31/2024 09:14 AM.

docs.appian.com/.../fnc_conversion_todatetime.html

I've written it from scratch in the past, if you want (note it requires Regex Functions plug-in)

Parsing ISO-8601 date/time

RULE_Utility_parseIsoDateTime()

if(
  or(
    isnull(ri!dateTimeString),
    not(regexmatch(
      pattern: "[12]\d\d\d-[01]{0,1}\d-[0123]{0,1}\d [012]\d:[012345]\d:[012345]\d",
      searchString: ri!dateTimeString,
      regexFlags: "s"
    )) 
  ),
  null(),
  
  a!localVariables(
    local!parts: split(ri!dateTimeString, " "),
    local!dateParts: split(local!parts[1], "-"),
    local!timeParts: split(local!parts[2], ":"),
    local!gmtTime: datetime(
      local!dateParts[1],
      local!dateParts[2],
      local!dateParts[3],
      local!timeParts[1],
      local!timeParts[2],
      local!timeParts[3]
    ),
    
    if(
      ri!fromGmt,
      local!gmtTime,
      gmt(local!gmtTime)
    )
  )
)