5 Replies Latest reply on Feb 16, 2017 4:11 PM by Jim Dehner

How to format Time Stamp Field to HH:MM:SS

Dustin Brickwood Feb 16, 2017 1:45 PM

Hello,

I have a field name 'Time Stamp' that consists of values ranging from 0 - 235947, I have been searching and attempting to get this field formatted to hh:mm:ss in 12-hour clock time but have had zero luck.

There is no date in this field just the time values.

Any ideas?

Thanks,

Dustin B.

1. Re: How to format Time Stamp Field to HH:MM:SS

Jim Dehner Feb 16, 2017 1:52 PM (in response to Dustin Brickwood)

right clikc the date field and follow the path to Date format

When the box opens go all the way to the bottom and select custom then the field is hh:mm:ss AMPM

Let me know how this worked
Jim
1 of 1 people found this helpful
Like Show 0 Likes(0)

Actions
2. Re: How to format Time Stamp Field to HH:MM:SS

Shinichiro Murakami Feb 16, 2017 1:57 PM (in response to Dustin Brickwood)

Hi Dustin

What time does 235947 means? is this simple ,meaning 11:59:47PM ??

If so, follow below steps.

[Time Conversion]
dateparse("hhmmss",str([Original Value]))

Thanks,
Shin
Like Show 0 Likes(0)

Actions
3. Re: How to format Time Stamp Field to HH:MM:SS

Dustin Brickwood Feb 16, 2017 2:06 PM (in response to Shinichiro Murakami)

That is correct, I believe it is 11:59:47 Shin, I tried your approach and it indicated that dateparse is an unknown function?

Jim, your technique does not work as there is no date in this field just the timestamp, when I do so it simply adds Jan 1900 and doesn't indicate the times correctly.

The end result I am trying to achieve is to have all the times in a row so that I may group them into half hour periods during the work day (7am-930pm).
Like Show 0 Likes(0)

Actions
4. Re: How to format Time Stamp Field to HH:MM:SS

Shinichiro Murakami Feb 16, 2017 2:19 PM (in response to Dustin Brickwood)

Some data source does not allow dateparse.

[Time Conversion 2]
date(#2001/1/1#)+
int(left([Original Value],2))/24+
int(mid([Original Value],3,2))/(24*60)+
int(right([Original Value],2))/(24*60*60)

*** #2001/1/1# any date works if you only need time. ***

Rest of the steps are same.

Thanks,
Shin
2 of 2 people found this helpful
Like Show 0 Likes(0)

Actions
5. Re: How to format Time Stamp Field to HH:MM:SS

Jim Dehner Feb 16, 2017 4:11 PM (in response to Jim Dehner)

Thanks
Appreciate the shout out.
Jim
Like Show 0 Likes(0)

Actions