Group hours

[Anonymous]

Hi All,

I have a "Date" field in the format  eg: "3/24/2017  1:05:00 AM". I used Time([Date], 'h tt') to get 1 AM, 2 AM , 3 AM etc.

My problem is I get the data in the below format

I would like to group these to show only

01:00 AM

02:00 AM

03:00 AM

Need help in achieving this   

[hectormunoz]

use hour(field) to extract the numeric value. Time() function just formats the data, but still has the original value.

rgds

[Digvijay_Singh]

Like this may be -

[hectormunoz]

As the timestamp fields are numbers, you can use floor(field) to retrieve only the day value, and time(frac(field)) to extract the time part. The format will depend on the second parameter.

date(floor(field)) will set to 00:00:00 the time part

time(frac(Date),'hh:mm') will keep the second precision, but will not show in the field

[vishsaggi]

May be this?

= Time(Date(yourdatefield, 'MM/DD/YYYY hh:mm:ss TTT'), 'hh:mm TTT')

[Anonymous]

Put this in your script.

=MakeTime(hour(Time(frac(Date))),0,0) as GroupedHour

[Anonymous]

Hi,

I think I will elaborate my question with a actual example. I have the below data in my date field

When I use the hour function on the above. I get the data as

What I would like to see is just one time 09:00 AM. Basically for the Range -> 09:00 - 09:59 show it as 09:00

Hope you understood my question.

[Anonymous]

Thank you Wallo. This is what I am looking for. How do you remove the minutes and the seconds  and show

09 AM

10 AM

12 AM etc.....

[hectormunoz]

time(MakeTime(hour(Time(frac(Date))),0,0) , 'HH TT')

[Anonymous · Accepted solution]

You could either just go into the Number format for the field in the UI and select Timestamd and type

h[.fff] TT as the Format Pattern.

Or you could do it in the script like this:

time(MakeTime(hour(Time(frac(Date))),0,0),'h TT') as GroupedHour