How do you round time?

building reports webintelligence
1

I have a time object that I want to round to the nearest 15 minutes or half hour. How do I do this at the report level? RelativeDate can only seem to add or subtract and I don’t seem to be able to convert it to a number to do any math to it.

monni (BOB member since 2018-08-25)

2

If you have access to the universe, you can manipulate the time there. this example is for SQL Server:

select dateadd(minute, datediff(minute,0,GETDATE()) / 15 * 15, 0)

Substitute your time object for GETDATE().

charlie :us: (BOB member since 2002-08-20)

3

I do not have access to the universe.

monni (BOB member since 2018-08-25)

4

Solutions here and here.

Joe

joepeters :us: (BOB member since 2002-08-29)

5

I, too, am wondering how to accomplish time rounding. I found info on one of the links posted that got me close to where I need to be. I would like to round to the closest hour in military time.

I used this formula based on info I found on a prior post. Here’s my interpretation of this formula (I do better if I can actually understand the info)…

format the date object to display the hours in military time (HH) then add a colon then add minutes (mm) displayed as…this is where I get lost.

= formatdate([calltime];“HH”)+“:”+ formatnumber(15*floor(tonumber(formatdate([calltime];“mm”))/15);“00”)

I do not have the ability to edit the universe; I am an end user only so I need to rely on building formulas/variables. I guessed on what to fill in for my formula; I could kind of follow this formula, but not entirely, thus my guessing on the portion after the second “+”. It appears I need additional info to round up or down to the nearest hour (example- 16:01-29…16:00, 16:30-59…17:00).

= FormatDate([Admin Date and Time];“HH”)+“:”+ FormatNumber(60*Floor(ToNumber(FormatDate([Admin Date and Time];“mm”))/30);“00”)

my result for 7/8/2019 8:55pm= 20:60…the info is close but not quite accurate (see attached).

SAP Web Intelligence version: 4.2 Support Pack 5

Thank you in advance.

:?
universe.round.time.png

lepettit (BOB member since 2019-08-17)

6

My solution:

=ToDate((If (ToNumber(FormatDate([Admin Date and Time];"mm")) >= 30) Then Mod((  ToNumber(FormatDate([Admin Date and Time];"HH")))+1;24) Else ToNumber(FormatDate([Admin Date and Time];"HH")) )+ ":00";"HH:mm")

joepeters :us: (BOB member since 2002-08-29)