10 Replies Latest reply: Apr 14, 2016 5:04 AM by Sunny Talwar

# Round up time to the start of hour

How to round up time to the start of hour? Say,

Time               Return

03:05 AM        03:00

07:45 PM        19:00

I want to know whether it's attainable by Set Analysis or by Data Load. Thanks!

• ###### Re: Round up time to the start of hour

=time(hour(time#(TimeField,'hh:mm TT')),'hh:mm') as TIME

• ###### Re: Round up time to the start of hour

or

hour(time#(TimeField,'hh:mm TT'))&':'&00 as TIME

• ###### Re: Round up time to the start of hour

May be this:

Time(Round(Time, 1/24), 'hh:mm') as Time

or

Time(Floor(Time, 1/24), 'hh:mm') as Time

Althouth I am not sure how you converted 7:45 PM to 17:00 -> did you mean 20:00 or 19:00 intead? First expression will get you 20:00 and second will get you 19:00

• ###### Re: Round up time to the start of hour

Sorry, should be 19:00 instead. Thanks!

• ###### Re: Round up time to the start of hour

Try this:

Time(Floor(Time, 1/24), 'hh:mm') as Time

• ###### Re: Round up time to the start of hour

Thanks, Sunny! You are right that it returns the rounded up time value but why are there duplicates?

I am intending this to use as a dimension, I expect that the measures will be aggregated according to the unique time value.

Any idea why it behaved like that?

• ###### Re: Round up time to the start of hour

I there date information attached to it for some reason?

May be give this a shot:

Time(Floor(Frac(Time), 1/24), 'hh:mm') as Time

• ###### Re: Round up time to the start of hour

Try

Set TimeFormat = 'hh:mm TT';

Time as TimeOriginal,

Time(Floor(Time, 1/24), 'hh:mm') as Time, // adapt the time format as needed, if you want to show AM/PM

...

FROM ...;

edit: this will of course perform a rounding to the lower hour value 07:45 --> 07:00

• ###### Re: Round up time to the start of hour

7:00 or 19:00 Stefan?

• ###### Re: Round up time to the start of hour

Sunny T &lt;span class=&quot;icon-status-icon icon-mvp&quot; title=&quot;Mvp&quot;&gt;&lt;/span&gt; wrote:

7:00 or 19:00 Stefan?

19:00. Missed the PM on the second line

07:45 PM --> 19:00

May, if the above doesn't work, please check that your input records are correctly read in as time, having a numeric representation, e.g. by using Time#() interpretation function or setting the default format.