Forum Discussion
Convert text to time
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?
7 Replies
kattlees You can navigate to the "Modeling" tab on the top ribbon, click on "Data Type" and change to "Time." Hope this helps!
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 just posted an example for Taylor. Does that help?
