8 Replies Latest reply on Nov 30, 2018 11:24 AM by Jessica Singer

# Round time to nearest hour, split at 30 minutes

Looking for a calc that will let me round into hour bins, but with say, 4:17 rounded to 4:00, and 4: 47 rounded to 5:00.

and it seems I'd need to combine them in some way. But I don't understand date functions well enough to pull the calcs apart and use the pieces I need.

Please help!

• ###### 1. Re: Round time to nearest hour, split at 30 minutes

Can you give some examples of your input date-time along with expected results.

• ###### 2. Re: Round time to nearest hour, split at 30 minutes

[CL Time Sent] = 9:32 AM --> 10:00 AM

[CL Time Sent] = 9:05 AM --> 9:00 AM

I'm trying to create hour bins that round times with minutes <= 30 to the previous whole hour, and times with minutes >= 30 to the following full hour.

• ###### 3. Re: Round time to nearest hour, split at 30 minutes

Create these three fields:

1. Minute

DATEPART('minute',[DateTimeField ])

2. Second

DATEPART('second',[DateTimeField ])

3. Round

IF [Minute]<=30 THEN

DATEADD('second',-(([Minute]*60)+[second]),[DateTimeField ])

ELSE

DATEADD('hour',1,DATEADD('second',-(([Minute]*60)+[second]),[DateTimeField ]))

END

• ###### 4. Re: Round time to nearest hour, split at 30 minutes

This is the error the first calculation returns:

• ###### 5. Re: Round time to nearest hour, split at 30 minutes

Your time is in string format not in Datetime format. Please post this workbook. Need to check.

• ###### 6. Re: Round time to nearest hour, split at 30 minutes

Oops, wrong workbook attached. Here's the right one.

• ###### 7. Re: Round time to nearest hour, split at 30 minutes

Converted your field to DateTime and formatted the final rounded time to h:nn AMPM format.

• ###### 8. Re: Round time to nearest hour, split at 30 minutes

Doh! Rookie mistake.

Thank you so much!