Round Date/Time field to nearest 30 minutes

[Anonymous]

I have a date/time field that I need to round to the nearset 30 minute interval. For example, 6/8/18 1:33 PM would round to 6/8/18 1:30 PM and 6/8/18 1:46 PM would round to 6/8/18 2:00 PM. I'd like to do this in the query editor if possible, but would settle for DAX if needed.

[jgubler]

I find this easier:

1. split the column into a date column and a time column (or create 2 new columns)
2. Use the floor() or ceiling() function on the time to create a new column
3. combine the 2 columns together or create a new column like this NEWDATETIME = table[date] & " " & table[time]
4. change data type to be date-time

[Anonymous]

Hi!

Hi! I'd like to change data from a column that contains hour. In Excel it's pretty easy to do, but in power BI no. let's show you:

Excel:

A2 830 =rounddown(A2/100;0) after results 8

A3 1040 =rounddown(A3/100;0) after results 10

Thanks

[Interkoubess]

@Anonymous,

Please open a new subject and we will help...

Can you try MRound formula....

Ninter.

[Interkoubess]

Hi @Anonymous,

Please try this formula in Power Query where my date column is called [Datetest]

if (Time.Minute([Datetest])-30)<=10 then [Datetest]+#duration(0,0,30-Time.Minute([Datetest]),0) else if (Time.Minute([Datetest])-30)<=15 then [Datetest]+#duration(0,0,Time.Minute([Datetest])-30,0) else [Datetest]+#duration(0,0,60-Time.Minute([Datetest]),0)

Hope it helps...

Ninter

[Anonymous]

Thank you for the reply Ninter, however this always rounds down to the nearest 30 minutes. I need it to round up or down depending on how close it is to the nearest 30 minute interval.

[Interkoubess]

Hi @Anonymous,

Could you give more example with my formula and your expected results in order to correct it.

Ninter

[Anonymous]

Thanks for taking the time to help me with this. After further inspection, it's actually rounding up when i want it to round down. It's also not rounding off the seconds. Here is a table with my original value, the value from your custom column and the desired value. As you can see, some match others don't.

Original Column	Result	Desired Result
3/12/18 11:59:59 PM	3/13/18 12:00:59 AM	3/13/18/12:00:00 AM
2/7/18 7:54:00 PM	2/7/18 8:00:00 PM	2/7/18 8:00:00 PM
4/10/18 7:15:00 PM	4/10/18 7:30:00 PM	4/10/18 7:30:00 PM
12/3/17 7:00:00 PM	12/3/17 7:30:00 PM	12/3/17 7:00:00 PM
12/18/17 2:13:00 PM	12/18/17 2:30:00 PM	12/18/17 2:00:00 PM

[Interkoubess · Accepted solution]

Hi @Anonymous,

Then try this :

if Time.Minute([Original Column])/15>3 then [Original Column]+#duration(0,0,60-Time.Minute([Original Column]),-Time.Second([Original Column])) else if Time.Minute([Original Column])/15<1 then [Original Column]+#duration(0,0,-Time.Minute([Original Column]),-Time.Second([Original Column])) else [Original Column]+#duration(0,0,30-Time.Minute([Original Column]),-Time.Second([Original Column])))

Ninter

[dude15000]

Could you give an example of this with a 10 minute rounding rule?  Having trouble getting this 100% using 10 minutes.

[Interkoubess]

Hi @dude15000,

Could you please explain what you want with expected outcomes.

You can open a new subject and taggued me and I will be pleased to help...

Ninter

[Anonymous]

Works perfectly! Thanks Ninter!