How to add a missing : to an input

Question

I'm trying to make a military time converter using Slack Workflows. It works by pulling up a form when they press the button in the channel.

It asks them one question.

What is the time in military time? (Format XX:XX)

Value then imports itself into a Google Sheet and uses a different cell formatted for standard time.

The workflow system then reads the value of the two cells and puts them together into a text string.

Due to the limited nature of Slack Workflows. I cannot simply restrict their inputs to only match my formatting, so people are putting in things like.

1314 or 13, rather than 13:14 or 13:00. The issue is that the cellular input no longer reads as a time in the Google Sheet, meaning I'm getting returns like this.

1314 is 12:00 am in standard time.

To solve this, I used the REPLACE function in Google Sheets to add missing values.

=REPLACE(B3,3,0,":")

Or

=REPLACE(B3,3,0,":00")

Which works fine if they're entering the text in the incorrect format.

But if they enter the text in the format that the tool tells them to, the system then errors out and gives the following return.

13::00 is 12:00 am in standard time.

I tried or search function to only fire the replace function if it's not formatted correctly but has been unsuccessful thus far, getting errors like #Value, #Error, etc.

Before anybody asks, unfortunately, I cannot make a Slack application due to security restrictions, and I cannot access sheets/excel for similar reasons.

Answer 1

It can be surprisingly complex to check that a free-form text string is a valid time value. Use regular expressions and ifs(), like this:

=lambda( 
  digits, error, 
  lambda( 
    result, 
    ifs( 
      not(regexmatch(result, "^([012][0-9]:[0-5][0-9])$")), error, 
      regexmatch(result, "^(2[5-9])"), error, 
      true, result 
    ) 
  )( 
    ifs( 
      len(digits) = 4, left(digits, 2) & ":" & right(digits, 2), 
      len(digits) = 3, left(digits, 2) & ":0" & right(digits), 
      len(digits) = 2, left(digits, 2) & ":00", 
      true, error 
    ) 
  ) 
)( 
  regexreplace(trim(B3), "[^\d]", ""), 
  "not any valid time" 
)

Sample results:

input	result
13:45	13:45
13:45 pm	13:45
1345	13:45
1305	13:05
135	13:05
13:5	13:05
13 5	13:05
13 05	13:05
13	13:00
13:65	not any valid time
113:45	not any valid time
25:45	not any valid time
13:450	not any valid time