How to create time from 3 fields?

[patbuchanan]

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:

• StartTimeHour = 7
• StartTimeMinute = 0
• StartTimeAM_PM = PM

I'm trying to get it to display 7:00 PM.

[#2]

Hi,

use the below

Time#(MakeTime('7','00'),'hh:mm:ss')

[jpenuliar]

try Maketime()

[JonnyPoole]

=  MakeTime(  StartTimeHour + if(StartTimeAM_PM='PM',12,0) , StartTimeMinute)

[#5]

Hi,

StartTimeHour &':'&StartTimeMinute &' '&StartTimeAM_PM as Time

(OR)

MakeTime(  StartTimeHour + if(StartTimeAM_PM='PM',12,0) , StartTimeMinute)

[its_anandrjs]

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

[danieloberbilli]

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

[JonnyPoole]

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)

[its_anandrjs]

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

[#10]

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