0

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.

1

1 Answer 1

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
2
  • I love that!! I came up with a solution but it's not as well put together as yours... I used '=ISDATE(B3)' in a helper cell. Came to find out that time values are considered dates by Google sheets.=IF(C3=TRUE,B3,REPLACE(B3,3,0,":")) and finally '=D3'
    – Vexel
    Jan 12, 2023 at 9:03
  • 1
    Be aware that Google Sheets will automatically coerce values like 13 10 to dates like 10/13/2023. The value will look similar to the time 13:10 but the underlying value is different. See Working with date and time values in Google Sheets. Jan 12, 2023 at 10:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.