Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I've got three fields that I'm trying to combine and make one hour field if someone has the best way for this to work.
These are my three fields:
I'm trying to get it to display 7:00 PM.
Hi,
use the below
Time#(MakeTime('7','00'),'hh:mm:ss')
try Maketime()
= MakeTime( StartTimeHour + if(StartTimeAM_PM='PM',12,0) , StartTimeMinute)
Hi,
StartTimeHour &':'&StartTimeMinute &' '&StartTimeAM_PM as Time
(OR)
MakeTime( StartTimeHour + if(StartTimeAM_PM='PM',12,0) , StartTimeMinute)
Use Maketime() or you can individually use fields
Load
Hour(StartTimeHour)&':'&Minute(StartTimeMinute)&' '&StartTimeAM_PM as HourTime
From Location;
Or
Load
Maketime( Hour(StartTimeHour), Minute(StartTimeMinute) )&''&StartTimeAM_PM as HourTime
From Location;
Regards
Anand
I agree with Shruti, but you might also change the SET Timeformat to SET TimeFormat='h:mm TT'; to avoid the seconds at the end and display 7:00 PM only
Alternative from above that drops the if() but still reading PM vs AM
= time(time#( StartTimeHour & ':' & StartTimeMinute & ' ' & StartTimeAM_PM,'hh:mm TTT'))
= MakeTime( StartTimeHour + if(StartTimeAM_PM='PM',12,0) , StartTimeMinute)
If you have and sample data file then provide or please see this small example
Tab:
LOAD * Inline
[ StartTimeHour,StartTimeMinute,StartTimeAM_PM
7,0,PM
8,15,PM
1,15,AM ];
New:
Load
Hour(time#(StartTimeHour,'hh')) as Hour,
Minute(time#(StartTimeMinute,'mm')) as Minute,
Hour(time#(StartTimeHour,'hh'))&':'&Minute(time#(StartTimeMinute,'mm'))&' '&StartTimeAM_PM as HourTime
Resident Tab;
Regards
Anand
Hi Pat Buchanan,
Try this also............
Timeconvert:
LOAD
*,
time(time#(PurgeChar(MakeTime(StartTimeHour,StartTimeMinute),'AMP')&StartTimeAM_PM,'hh:mm:ss TT'),'hh:mm TT') as merge
;
LOAD * Inline [
StartTimeHour,StartTimeMinute,StartTimeAM_PM
7,0,PM
8,30,AM
6,04,AM
5,01,PM
12,04,PM
];
Please mark this as Answered if you have found solution.
Regards,
Lokeshwari