Desired Output: HH:MMAM-HH:MMAM
Type: text
FieldType: String
Inputs:
${time_open_Sunday}
${time_close_Sunday}
Type: time
FieldType: null
In the S123 Connect Designer this works and returns what I expect:
concat(if((format-date(${time_open_Tuesday}, '%H') * 1) > 12, string((format-date(${time_open_Tuesday}, '%H') * 1) - 12), if((format-date(${time_open_Tuesday}, '%H') * 1) = 0, "12", string(number(format-date(${time_open_Tuesday}, '%H'))))), ":", format-date(${time_open_Tuesday}, '%M'), if((format-date(${time_open_Tuesday}, '%H') * 1) >= 12, "PM", "AM"), "-", if((format-date(${time_close_Tuesday}, '%H') * 1) > 12, string((format-date(${time_close_Tuesday}, '%H') * 1) - 12), if((format-date(${time_close_Tuesday}, '%H') * 1) = 0, "12", string(number(format-date(${time_close_Tuesday}, '%H'))))), ":", format-date(${time_close_Tuesday}, '%M'), if((format-date(${time_close_Tuesday}, '%H') * 1) >= 12, "PM", "AM"))
But "format-date" returns "invalid date" or "NaN" in S123 Web App... 😐 So it sounds like Designer/Field App converts time to Epoch, but web app turns it to 13:36:00.000-05:00-15:34:00.000-05:00
So alternatively tried this which returns the hours, but it won't return and concat the minutes and I'm going crazy: 1:PM-3:PM. I've tried so many iterations.
concat(
if(substr(${time_open_Sunday}, 0, 2) > 12, substr(${time_open_Sunday}, 0, 2) - 12, if(substr(${time_open_Sunday}, 0, 2) = 0, 12, substr(${time_open_Sunday}, 0, 2))),":", substr(${time_open_Sunday}, 3, 2),if(substr(${time_open_Sunday}, 0, 2) >= 12, "PM", "AM"),"-",
if(substr(${time_close_Sunday}, 0, 2) > 12, substr(${time_close_Sunday}, 0, 2) - 12, if(substr(${time_close_Sunday}, 0, 2) = 0, 12, substr(${time_close_Sunday}, 0, 2))),":", substr(${time_close_Sunday}, 3, 2), if(substr(${time_close_Sunday}, 0, 2) >= 12, "PM", "AM")
)
tried setting to int...
concat(
if(int(substr(${time_open_Sunday}, 0, 2)) > 12, int(substr(${time_open_Sunday}, 0, 2)) - 12, if(int(substr(${time_open_Sunday}, 0, 2)) = 0, 12, int(substr(${time_open_Sunday}, 0, 2)))),
":",
substr(${time_open_Sunday}, 3, 2),
if(int(substr(${time_open_Sunday}, 0, 2)) >= 12, "PM", "AM"),
"-",
if(int(substr(${time_close_Sunday}, 0, 2)) > 12, int(substr(${time_close_Sunday}, 0, 2)) - 12, if(int(substr(${time_close_Sunday}, 0, 2)) = 0, 12, int(substr(${time_close_Sunday}, 0, 2)))),
":",
substr(${time_close_Sunday}, 3, 2),
if(int(substr(${time_close_Sunday}, 0, 2)) >= 12, "PM", "AM")
)
so I'm stumped. I can't even get this work in the web app no matter what I do:
substr(${time_open_Sunday}, 3, 2)
Solved! Go to Solution.
Wow!! With the help of Copilot AI and my suggesting decimal-time and watching for rounding this is what we came up with to concat two time question types together in a range. This seems very intense!!! 😭😮😎
Stored output is now: 3:04PM-3:12PM
if(decimal-time(${time_close_Monday}) > 0,
concat(
if(int(decimal-time(${time_open_Monday}) * 24) > 12,
int(decimal-time(${time_open_Monday}) * 24) - 12,
if(int(decimal-time(${time_open_Monday}) * 24) = 0, 12, int(decimal-time(${time_open_Monday}) * 24))),
":",
if(int(((decimal-time(${time_open_Monday}) * 24 + 0.0001) - int(decimal-time(${time_open_Monday}) * 24)) * 60) < 10,
concat("0", int(((decimal-time(${time_open_Monday}) * 24 + 0.0001) - int(decimal-time(${time_open_Monday}) * 24)) * 60)),
int(((decimal-time(${time_open_Monday}) * 24 + 0.0001) - int(decimal-time(${time_open_Monday}) * 24)) * 60)),
if(int(decimal-time(${time_open_Monday}) * 24) >= 12, "PM", "AM"),
"-",
if(int(decimal-time(${time_close_Monday}) * 24) > 12,
int(decimal-time(${time_close_Monday}) * 24) - 12,
if(int(decimal-time(${time_close_Monday}) * 24) = 0, 12, int(decimal-time(${time_close_Monday}) * 24))),
":",
if(int(((decimal-time(${time_close_Monday}) * 24 + 0.0001) - int(decimal-time(${time_close_Monday}) * 24)) * 60) < 10,
concat("0", int(((decimal-time(${time_close_Monday}) * 24 + 0.0001) - int(decimal-time(${time_close_Monday}) * 24)) * 60)),
int(((decimal-time(${time_close_Monday}) * 24 + 0.0001) - int(decimal-time(${time_close_Monday}) * 24)) * 60)),
if(int(decimal-time(${time_close_Monday}) * 24) >= 12, "PM", "AM")
),
""
)
Wow!! With the help of Copilot AI and my suggesting decimal-time and watching for rounding this is what we came up with to concat two time question types together in a range. This seems very intense!!! 😭😮😎
Stored output is now: 3:04PM-3:12PM
if(decimal-time(${time_close_Monday}) > 0,
concat(
if(int(decimal-time(${time_open_Monday}) * 24) > 12,
int(decimal-time(${time_open_Monday}) * 24) - 12,
if(int(decimal-time(${time_open_Monday}) * 24) = 0, 12, int(decimal-time(${time_open_Monday}) * 24))),
":",
if(int(((decimal-time(${time_open_Monday}) * 24 + 0.0001) - int(decimal-time(${time_open_Monday}) * 24)) * 60) < 10,
concat("0", int(((decimal-time(${time_open_Monday}) * 24 + 0.0001) - int(decimal-time(${time_open_Monday}) * 24)) * 60)),
int(((decimal-time(${time_open_Monday}) * 24 + 0.0001) - int(decimal-time(${time_open_Monday}) * 24)) * 60)),
if(int(decimal-time(${time_open_Monday}) * 24) >= 12, "PM", "AM"),
"-",
if(int(decimal-time(${time_close_Monday}) * 24) > 12,
int(decimal-time(${time_close_Monday}) * 24) - 12,
if(int(decimal-time(${time_close_Monday}) * 24) = 0, 12, int(decimal-time(${time_close_Monday}) * 24))),
":",
if(int(((decimal-time(${time_close_Monday}) * 24 + 0.0001) - int(decimal-time(${time_close_Monday}) * 24)) * 60) < 10,
concat("0", int(((decimal-time(${time_close_Monday}) * 24 + 0.0001) - int(decimal-time(${time_close_Monday}) * 24)) * 60)),
int(((decimal-time(${time_close_Monday}) * 24 + 0.0001) - int(decimal-time(${time_close_Monday}) * 24)) * 60)),
if(int(decimal-time(${time_close_Monday}) * 24) >= 12, "PM", "AM")
),
""
)