Can someone help me format this column so tha...

[Brady_Lovell]

Can someone help me format this column so that it displays the time format as HH:mm (1:30 PM)?

I’m using this formula in a virtual column

=CONCATENATE([City_17], ", ", [ETA])

…so that I can have the City and ETA displayed in the same column. But when the time from the ETA shows up, it shows up as hh:mm:ss which is not desired. I have no idea how to achieve this, or

if it’s even possible, so if anyone has any input, it would be greatly appreciated.

Thanks

[Suvrutt_Gurjar]

Hi @Brady_Lovell Please set the time column {ETA} 's attribute “Ignore seconds” to true

[Brady_Lovell]

Thanks @Suvrutt_Gurjar but the “Ignore seconds” option is already set to true, and I can’t figure out why or what to do about it.

[Levent_KULACOGL]

You can also use:

IFS(HOUR([ETA] - “00:00:00”) < 10),“0”),HOUR([ETA] - “00:00:00”) ,":",IFS(MINUTE([ETA] - “00:00:00”) < 10,“0”),MINUTE([ETA] - “00:00:00”)

This expression will generate the [ETA] as 04:00 for 4:00 AM

[Brady_Lovell]

@Levent_KULACOGLU You’ve been a tremendous help, thank you very much!

[Levent_KULACOGL]

@Brady_Lovell the below expression is a bit complex but will yield to result of H:mm AM/PM as you have requested. I have tested the expression and it works.

=CONCATENATE(

[City_17],

" | ",

IFS(

AND(HOUR([ETA] - “00:00:00”) >= 0, HOUR([ETA] - “00:00:00”) < 10 ), HOUR([ETA] - “00:00:00”),

AND(HOUR([ETA] - “00:00:00”) > 12, HOUR([ETA] - “00:00:00”) <=23), HOUR([ETA] - “00:00:00”) - 12

),

“:”,

IFS(

MINUTE([ETA] - “00:00:00”) < 10,“0”

),

MINUTE([ETA] - “00:00:00”),

IFS(

AND(

AND(HOUR([ETA] - “00:00:00”) >= 0, MINUTE([ETA] - “00:00:00”) > 0),

AND(HOUR([ETA] - “00:00:00”) <= 11, MINUTE([ETA] - “00:00:00”) <= 59)

)," AM",

OR(

AND(HOUR([ETA] - “00:00:00”) >= 12, MINUTE([ETA] - “00:00:00”) >=0),

AND(HOUR([ETA] - “00:00:00”) <= 23, MINUTE([ETA] - “00:00:00”) <= 59),

AND(HOUR([ETA] - “00:00:00”) = 0, MINUTE([ETA] - “00:00:00”) = 0)

)," PM"

) )

[Brady_Lovell]

I’ve ran into a problem while testing this. Whenever the [ETA] column is empty, it displays a bunch of random numbers

[Levent_KULACOGL]

@Brady_Lovell that’s normal. Because it’s a TIME type column. In Java there is only DATETIME, not DATE and TIME seperately and when you refer to a time only, Java compiler considers it to be a Joda time which is Dec. 31st, 1899. When you leave the [ETA] column value blank, it considers it Dec. 31st, 1899 12:00:00AM I have added a ISNOTBLANK() control to my expression and it will work. If your [ETA] column is blank, it will appear as "City | - ", if it’s not blank, it will appear as “City | 1:28 PM”.

=IFS(

ISBLANK([ETA]), CONCATENATE(

[City_17],

" | ",

" - "

),

ISNOTBLANK([ETA]), CONCATENATE(

[City_17],

" | ",

IFS(

AND(HOUR([ETA] - “00:00:00”) >= 0, HOUR([ETA] - “00:00:00”) < 10 ), HOUR([ETA] - “00:00:00”),

AND(HOUR([ETA] - “00:00:00”) > 12, HOUR([ETA] - “00:00:00”) <=23), HOUR([ETA] - “00:00:00”) - 12

),

“:”,

IFS(

MINUTE([ETA] - “00:00:00”) < 10,“0”

),

MINUTE([ETA] - “00:00:00”),

IFS(

AND(

AND(HOUR([ETA] - “00:00:00”) >= 0, MINUTE([ETA] - “00:00:00”) > 0),

AND(HOUR([ETA] - “00:00:00”) <= 11, MINUTE([ETA] - “00:00:00”) <= 59)

)," AM",

OR(

AND(HOUR([ETA] - “00:00:00”) >= 12, MINUTE([ETA] - “00:00:00”) >=0),

AND(HOUR([ETA] - “00:00:00”) <= 23, MINUTE([ETA] - “00:00:00”) <= 59),

AND(HOUR([ETA] - “00:00:00”) = 0, MINUTE([ETA] - “00:00:00”) = 0)

)," PM"

)

) )

[Brady_Lovell]

@Levent_KULACOGLU Getting this error when I copy and paste your expression

"IFS function is used incorrectly:Inputs to IFS() must be one or more condition-value pairs. "

[Levent_KULACOGL]

@Brady_Lovell do apologize for this inconvenience. It appears that I have forgotten a comma. I have edited my expression in the below post. Sorry for this.

[Brady_Lovell]

We’ve almost got it!!!

[Levent_KULACOGL]

@Brady_Lovell I will get back to you in an hour. I’m getting on bike and going home. I have to review the eval of my expression.

[Levent_KULACOGL]

@Brady_Lovell I’ve wrapped up the expression a bit and here it is. I have tested it and it seems it’s working properly.

=IFS(

ISBLANK([ETA]), CONCATENATE(

[City_17],

" | ",

" - "

),

ISNOTBLANK([ETA]), CONCATENATE(

[City_17],

" | ",

IFS(

AND(

AND(HOUR([ETA] - “00:00:00”) = 0, MINUTE([ETA] - “00:00:00”) >= 0),

AND(HOUR([ETA] - “00:00:00”) = 0, MINUTE([ETA] - “00:00:00”) <= 59)

),CONCATENATE(HOUR([ETA] - “00:00:00”) + 12,":",IFS(MINUTE([ETA] - “00:00:00”) < 10, “0”),MINUTE([ETA] - “00:00:00”)," AM"),

AND(

AND(HOUR([ETA] - “00:00:00”) >= 1, MINUTE([ETA] - “00:00:00”) >= 0),

OR(

AND(HOUR([ETA] - “00:00:00”) <= 11, MINUTE([ETA] - “00:00:00”) <= 59),

AND(HOUR([ETA] - “00:00:00”) = 12, MINUTE([ETA] - “00:00:00”) = 0)

)

),CONCATENATE(HOUR([ETA] - “00:00:00”),":",IFS(MINUTE([ETA] - “00:00:00”) < 10, “0”),MINUTE([ETA] - “00:00:00”)," AM"),

AND(

AND(HOUR([ETA] - “00:00:00”) = 12, MINUTE([ETA] - “00:00:00”) >= 0),

AND(HOUR([ETA] - “00:00:00”) = 12, MINUTE([ETA] - “00:00:00”) <= 59)

),CONCATENATE(HOUR([ETA] - “00:00:00”),":",IFS(MINUTE([ETA] - “00:00:00”) < 10, “0”),MINUTE([ETA] - “00:00:00”)," PM"),

AND(

AND(HOUR([ETA] - “00:00:00”) >= 13, MINUTE([ETA] - “00:00:00”) >= 0),

AND(HOUR([ETA] - “00:00:00”) <= 23, MINUTE([ETA] - “00:00:00”) <= 59)

),CONCATENATE(HOUR([ETA] - “00:00:00”) - 12,":",IFS(MINUTE([ETA] - “00:00:00”) < 10, “0”),MINUTE([ETA] - “00:00:00”)," PM"),

AND(HOUR([ETA] - “00:00:00”) = 0, MINUTE([ETA] - “00:00:00”) = 0),

CONCATENATE(HOUR([ETA] - “00:00:00”),":",IFS(MINUTE([ETA] - “00:00:00”) < 10, “0”),MINUTE([ETA] - “00:00:00”)," PM")

)

) )

[Levent_KULACOGL]

@Brady_Lovell

In your expression, substitute just the [ETA] part with the expression below:

HOUR([ETA] - “00:00:00”) ,":", MINUTE([ETA] - “00:00:00”)

[Brady_Lovell]

@Levent_KULACOGLU I don’t know how to thank you enough. Nothing short of amazing that these kind of possibilities are available to us. Hopefully one day I’ll gain some of the knowledge that you have so I’ll be able to create complex expressions like this. Again, thank you!

[Levent_KULACOGL]

You’re very welcome @Brady_Lovell, it was my pleasure to help. Good to know that your problem has been solved out. Appreciate your patience.

[Aleksi_Alkio]

If your column is a virtual and all users are using the AM/PM format on their devices, you should be able to read the correct format with the TEXT expression. Then the formula would be like CONCATENATE([City_17]," | ",TEXT([ETA])

[Levent_KULACOGL]

@Aleksi_Alkio you are a true Master really. Thanks for pointing out the difference and use of CONCATENATE and TEXT functions. And as Brady said to me, I can say the same to you: “Hopefully one day I’ll gain some of the knowledge that you have…”

[Brady_Lovell]

@Levent_KULACOGLU This is what I just tried…

=CONCATENATE([City_17], " | “, HOUR([ETA]“00:00:00”),”:",MINUTE([ETA]“00:00:00”))

…and I got the error…

“Parameter 1 of function HOUR is of the wrong type”

[Levent_KULACOGL]

@Brady_Lovell sorry for that but minus sign interpreted as strikethrough text in my reply…I have edited my post, so it’s reflecting the correct syntax now

[Brady_Lovell]

@Levent_KULACOGLU Just missing 1 digit for some reason…

[Levent_KULACOGL]

@Brady_Lovell do you wish to format that time as HH:mm or H:mm?

[Brady_Lovell]

Just so that it shows up like…

1:30 PM 12:30 PM 9:15 AM

[Levent_KULACOGL]

@Brady_Lovell try this:

HOUR([ETA] - “00:00:00”) ,":",IFS(MINUTE([ETA] - “00:00:00”) < 10,“0”),MINUTE([ETA] - “00:00:00”)

[Brady_Lovell]

This sure is a lot better than what we originally started out with. Any way to fine tune it to display like the 4:45 AM or 12:30 PM type format? If not, this is definitely doable.