Display time in "hh:mm" in tableau

ozzy t
ozzy t used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Cost Accountant
Top Expert 2012
Commented:
OK, so first split the 'time' field in two with a calculated field:

Calculated Field 'Time1' =
split([Time],"-",1)

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
+":"+
split([Time1],":",2)
+" "+
if int(split([Time1],":",1)) > 12 then "PM" else "AM" end

Open in new window

Repeat for the second time:

Calculated Field 'Time2' =
split([Time],"-",2)

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
+":"+
split([Time2],":",2)
+" "+
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.

ATB
Steve.
SteveCost Accountant
Top Expert 2012

Commented:
NO other comments and suggested solution should do the task.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial