# Convert to 30 minutes Interval

on
Hello EE,

Can someone please help me with how to convert date and times to 30 minutes interval in Microsoft Power BI.

Thanks
Comment
Watch Question

Do more with

EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
It could be:

``````=TIME(HOUR(NOW()),INT(MINUTE(NOW())*2)/2,0)
``````
Top Expert 2014

Commented:
I don't know how you would convert dates into 30 minute intervals.  However, there are 2880 such intervals in each day.  So, that conversion might be:
``````=(Now()-Date())\2880
``````
Mechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
aikimark,
You may want to edit your Comment.

With 24 hours in a day, there are only 48 of those 30 minute long intervals. But if you meant to calculate the number of 30 second intervals, there are 2880 of those.

Freelance programmer / Consultant

Commented:
I could argue there are probably an infinite number of thirty minute intervals in a day.
One starts now, the next start 0.00000000001 seconds later.....

You need to specify rather more information as to just exactly what you require and how a thirty minute interval is started.
Top Expert 2014

Commented:
too late now. :-(

I started writing the formula and had 30 second intervals in mind.

Commented:
@byundt and aikimark

That is exactly what I wanted.  Like in this example
``````0:00:00
0:30:00
1:00:00
1:30:00
2:00:00
2:30:00
3:00:00
3:30:00
4:00:00
4:30:00
5:00:00
5:30:00
6:00:00
6:30:00
7:00:00
7:30:00
8:00:00
8:30:00
9:00:00
9:30:00
10:00:00
10:30:00
11:00:00
11:30:00
12:00:00
12:30:00
13:00:00
13:30:00
14:00:00
14:30:00
15:00:00
15:30:00
16:00:00
16:30:00
17:00:00
17:30:00
18:00:00
18:30:00
19:00:00
19:30:00
20:00:00
20:30:00
21:00:00
21:30:00
22:00:00
22:30:00
23:00:00
23:30:00
``````
Mechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
If you multiply the time value by 48, the integer part of the result is a bracket number from 0 to 47. I would add this as a new column in your data rather than a PowerBI measure.
=INT(48*(DateAndTime - INT(DateAndTime)))

Or if you want the interval to appear as a time, then just divide the previous formula by 48:
=INT(48*(DateAndTime - INT(DateAndTime)))/48
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
That will return a number.

See my original post. Of course, NOW() can be replaced with any date/time (cell) value.

Commented:
I will try Gustav's original post in the morning.

@byundtMy data has several million row rows in .csv that I am importing to Power BI
Mechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
The formula I posted works in PowerBI Desktop, where I applied it to the data after importing it. Many of the functions have the same names in Power BI's M and DAX languages as they do in Excel.

Commented:
This formula in excel worked as I expected

TEXT(FLOOR(DATEANDTIME,TIME(0,30,0)),"HH:MM")