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

(Brady Lovell) #1

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) #2

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

(Brady Lovell) #3

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 KULAÇOĞLU) #4

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) #5

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

(Levent KULAÇOĞLU) #6

@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) #7

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

(Levent KULAÇOĞLU) #8

@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 :slight_smile: 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) #9

@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 KULAÇOĞLU) #10

@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) #11

We’ve almost got it!!!

(Levent KULAÇOĞLU) #12

@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 KULAÇOĞLU) #13

@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 KULAÇOĞLU) #14

@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) #15

@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 KULAÇOĞLU) #16

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) #17

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 KULAÇOĞLU) #18

@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) #19

@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 KULAÇOĞLU) #20

@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