Display time in "hh:mm" in tableau

have a time field like this "17:45:00-19:45:00" i am trying to make it look like this "5:45pm -7:45pm" i have been searching with no answer, does anyone know how to accomplish this ? Thanks for your help.
ozzy tDeveloper/Health InformaticsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SteveCost AccountantCommented:
OK, so first split the 'time' field in two with a calculated field:

Calculated Field 'Time1' =

Open in new window

then convert to the AM/PM format required:

Calculated Field 'APTime1' =
if int(split([Time1],":",1)) > 12 then str(int(split([Time1],":",1)) - 12) else str(split([Time1],":",1))end
+" "+
if int(split([Time1],":",1)) > 12 then "PM" else "AM" end

Open in new window

Repeat for the second time:

Calculated Field 'Time2' =

Open in new window

And convert:

Calculated Field 'APTime2' =
if int(split([Time2],":",1)) > 12 then str(int(split([Time2],":",1)) - 12) else str(split([Time2],":",1))end
+" "+
if int(split([Time2]," ",1)) > 12 then "PM" else "AM" end

Open in new window

then join them back together:

Calculated Field 'Result' =
[APTime1]+" - " +[APTime2]

Open in new window

This will hopefully acheive what you need,
Though it may need a tweak in the date conversion formulae
It could all be put into a single formula... but I feel these 'steps' may help with understanding.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SteveCost AccountantCommented:
NO other comments and suggested solution should do the task.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.