Convert text to time

[kattlees]

I have a table with a couple of text fields that employees put a time in such as 0800 or 1625. The system we input into doesn't allow it to be formatted as a time.

I'm wondering the easiest way to convert it to a time value. I can't just add a column and concatenate with left two characters and right two characters with a colon in the middle as inevitably someone types in a value incorrectly such as 8025 or 0861. Currently I am make three extra columns. One with the left two characters, converting it to a number and one with the right two characters and converting it to a number. Then a third column that checks to make sure the original field is 4 characters, the hour column is less than 24 and the minute column is less than 60 and doing the concatenate that way.

Is there an easier way?

[Anonymous]

there could be a couple of options.

Are you able to attach an excel document with some samples - just so I can be sure to get all scenarios.

[kattlees]

@Anonymous  I just posted an example for Taylor. Does that help?

[Anonymous]

@kattlees You can navigate to the "Modeling" tab on the top ribbon, click on "Data Type" and change to "Time." Hope this helps!

[kattlees]

Thanks taylor_c, I do that after I do the final concatenate but I was hoping to avoid having to create 2 new columns. Maybe I didn't explain well enough

Data comes in as text field

StartTime    0825

I then create

StartTImeHour = left(StartTime,2) to get 08 and convert to a number to get 8

StartTImeMinute = right(StartTime,2) to get 25 and convert to a number to get 25

Then I create

StartTImeFinal = if(Len(StartTIme)=4 && StartTimeHour < 24 && StartTimeMinute < 60, concatenate(Left StartTime,2) & ":",Right(StartTime,2) and convert this to a time datatype

[Anonymous]

I've managed to do this and it works OK. Crucially you need to import this data as text (so it keeps the original '0' whre necessary)



Then change the format of Time in the modelling tab.

[kattlees]

Thanks @Anonymous  This does work nice. What happens if someone has a typo and enters 1262 or only puts in 3 characters like 800? I get an error and the visual won't display because 12:62  and 80:00 aren't valid times.

[Anonymous · Accepted solution]

invalid times are a little more tricky because the left/right still reads them.

but this will fix the length issue: