Rounding Time Up

[acetrevor]

Hi there,

I am having a problem rounding my time field to the nearest hour.

I have two time fields that I am grouping by day first, then by each hour. I am asking for the "last date in the period" to be printed which results in time values of 0:59, 01:59, 02:59 etc.

I would like to know how to round those values up to the nearest hour so that I would get 01:00, 02:00, 03:00 etc.

I am trying to average data between 12AM - 1 AM and have the value printed with a time stamp of 1 AM instead of 12 AM.

I tried to group by each hour using a datetime formula field with this formula:
dateadd("n",+1,{DateTimeField}) with no success.

Any help would be appreciated, thanks in advance.

Trevor

 

[lbass]

What happens to datetimes that are right on the hour? What would you expect to see for:

12:00AM
12:05AM
12:30AM
12:45AM
12:59AM

-LB

 

[acetrevor]

I'm not sure I understand your question.

When I'm grouping in CR, I group by each day then by each hour. The data that is being averaged is between each hour (ex. there are many points between 12 AM - 1 AM). I am then telling CR to print the last date in the period which turns out to be 12:59, 1:59 ... 23:59.

What I would like to do is to round each of those times up to the nearest hour to get 1:00, 2:00 ... 0:00 (I think this would be a formatting issue, but I'm not sure).

Thanks for your response.

Trevor

 

[lbass]

I shouldn't have asked about all the times, but the issue is this. It is showing 11:59pm, etc., because it is considering 11:00 part of the range, e.g., 11:00 to 11:59pm. If you round up the 11:59 to 12:00, it implies that your real range is 11:01pm to 12:00am--and then you get into the issue of the dates being incorrect, since the day begins with 12:00am. This also means that datetimes of 11:30pm would be grouped with in the 12:00am group of the next day.

You would have to insert your group by date on the following formula (on change of day):

if datetime(date({@datetime}),time(hour({@datetime}),0,0)) = {@datetime} then
{@datetime} else
dateadd("h",1,datetime(date({@datetime}),time(hour({@datetime}),0,0)))

Then insert a second group on this formula on change of hour.

-LB

 

[acetrevor]

Thank you very much LB, that's exactly what I needed. Your help is very much appreciated!