convert seconds to HH:MM:SS

[Former Member]

Hi,

I have time specified in total seconds as number, I want to convert that into HH:MM:SS is it possible in lumira ? Using sql server as my database and sourcing the data from universe.

Thanks

[alan_han · Accepted solution]

Hello Santosh,

Can you provide an example of the number that is "specified in total seconds"? Or are you referring to Unix Time (https://en.wikipedia.org/wiki/Unix_time)?

Thanks,

Alan

[Former Member · Accepted solution]

Thanks for your reply,

I'm working with call center data, our source system captures total time spent by an agent in seconds Ex: 20166 seconds translate to 5:40:13.

I know how to do it in web intelligence and the formula goes like this

=FormatNumber(Floor(<Column_Name>/3600) ,"00") & ":" & FormatNumber(Floor(Mod(<Column_Name> ,3600)/60) ,"00") & ":" & FormatNumber(Mod(Mod(<Column_Name> ,3600) ,60) ,"00")

I need equivalent formulae in lumira.

Thanks.

[ashutosh_rastogi · Accepted solution]

You can use following formula

Lpad(ToText(Floor({Time} / 3600), 0), 2, "0") + ":" + Lpad(ToText(Floor(Mod({Time}, 3600) / 60), 0), 2, "0") + ":" + Lpad(ToText(Floor(Mod(Mod({Time}, 3600), 60)), 0), 2, "0")

Regards,

Ashutosh

[Former Member · Accepted solution]

Thank you very much, that worked great !!!!

With that I have another requirement, since we converted a number to HH:MM:SS format, which technically now becomes a string.

I want to create a rule for that column showing something red if the time is more than 04:00:00.

Is it possible to do that ??

[ashutosh_rastogi · Accepted solution]

You may try to use conditional formatting options. That's the best option today.

Ashutosh