Creating 15 Minute intervals from Date and Time Column

[reportuser]

1. I have a column named,"STATS_DATE" which contains dates and time
2. Made a new column named, "Date" which contains only date from STATS_DATE
3. Made a new column named, "Time" which contains only time from STATS_DATE
4. Made a new column named, "Minutes" 

Minutes = DATEDIFF(DATE(1899,12,30),Table[Time],MINUTE)

5. Made a new column named, "15Min_Data"

15Min_Data = INT(DATEDIFF(DATE(1899,12,30),Table[Time],MINUTE) / 15)

How do I convert it into intervals @amitchandak ?

[AlexisOlson]

What is the end result that you want? What should the column look like?

[reportuser]

The end result could be the start of the 15 minute interval like so

12:00 AM
12:15 AM
12:30 AM

[AlexisOlson · Accepted solution]

Try formatting this as Time data type:

15Min_Data = ROUNDDOWN ( 'Table'[Time] * 24 * 60 / 15, 0 ) / ( 24 * 60 / 15 )

You can just use 96 instead of 24 * 60 / 15 since there are 96 fifteen-minute intervals in one day.

[reportuser]

How do I display the intervals?

 

[AlexisOlson]

I'd suggest trying what I posted. Those big integers you have don't look very useful to me.

[reportuser]

The column named,  "15Min_Data" uses the formula you posted above. 

[AlexisOlson]

Hmm. Then I suspect your Time column is not a time data type. If you convert that column to a decimal number, it should show values between 0 and 1 not big integers like that.

[TheoC]

@reportuser, from what I can see, @AlexisOlson has provided the right solution.  He mentioned that the values for a column that does not have the Data Type set to Time will be between 0 and 1 like that in the 15Min_Data (Non-Time Data Type) column.  Yours appear to be >1. Have you set the Time column to Data Type = Time?  If not, this might be the issue. Alternatively, you may have not copied his Calculated Column formula correctly?  Either way, hope it works out as the solution provided by @AlexisOlson is really sweet! (Cheers @AlexisOlson!!).

[reportuser]

@TheoC , the screenshot helped. @AlexisOlson  - thank you so much for providing the solution 🙂