cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
kattlees Member
Member

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?

1 ACCEPTED SOLUTION

Accepted Solutions
jbarnes Member
Member

Re: Convert text to time

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

but this will fix the length issue:
Capture.JPG

View solution in original post

7 REPLIES 7
taylor_c Frequent Visitor
Frequent Visitor

Re: Convert text to time

Changing to time.PNG

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

jbarnes Member
Member

Re: Convert text to time

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 Member
Member

Re: Convert text to time

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

kattlees Member
Member

Re: Convert text to time

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

jbarnes Member
Member

Re: Convert text to time

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)

Capture.JPG

Then change the format of Time in the modelling tab.


kattlees Member
Member

Re: Convert text to time

Thanks @jbarnes  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.

jbarnes Member
Member

Re: Convert text to time

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

but this will fix the length issue:
Capture.JPG

View solution in original post

Helpful resources

Announcements
Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 55 members 848 guests
Please welcome our newest community members: