Convert time in numeric field to hh:mm:ss

[tfidler240]

In our database time is a plain numeric field in 24 time:

13001 is 1:30 am and 1 sec

111621 is 11:16am and 21 seconds

211144 is 9:11pm and 44 seconds

Is there a function or process to convert them into a time field so they can be used for calculations?

Also how do I combine it with the date to match a full date & time stamp?

My date fields are CJULIAN which I have a conversion function.

[Anonymous]

This was the best I could come up with pre morning coffee:

Time(Time#(TIMEFIELD, if(len(TIMEFIELD)=5, 'hmmss', 'hhmmss')),'hh:mm:ss') as NewTime;

The expression checks the length of your timefield and formats accordingly.

So, 5 characters means 13001, or 'hmmss'.

6 characters indicates a 'hhmmss' format.

[rwunderlich]

I've had my coffee. So how about:

=time(time#(right(0 & TIMEFIELD, 6),'hhmmss'))

-Rob

[#4]

Rob,

you never got feedback from the OP, but I had the same question and can confirm your solution works, in case you were wondering.

Thanks!