Split Total minutes (In and out Time) in 15-minute Time Frame

Question

Hi All,

I have four columns Date, In-time, Out-time, and Total Minutes. Further, I would like to create 15-min time frame columns and divide total minutes in those 15-minute time frame columns. I'd highly appreciate if you could suggest on how to achieve that?

I have attached the excel file of the image.

Thanks.

Test.xlsx

NicoleJohnson · Accepted Answer

Fun one! This one can be done with a few key tools: Generate Rows (to find each minute between In & Out), a Formula to determine which "Time Bucket" each minute belongs to, and then Summarize tool to group by Time Buckets and count the number of minutes in each. Also included some functionality to get you the whole list of possible time buckets (used the same start and finish as your example data, but could easily be adjusted).

Hope this helps!

Cheers,

NJ

estherb47 · Accepted Answer

Hi @Shivam

Here's another approach to your challenge. I reverted the Excel file back to the original 4 columns you had mentioned (first Select tool), and then used a formula tool to generate the first time grouping. A Generate Rows tool generates the 15 minute increments into the times you need into a field called [TimeStamp] (I just based this on the times in the Excel file).

Then, a Formula tool does the heavy lifting. If the [In Time] is greater than the [TimeStamp], then there's nothing to calculate. Otherwise, if the [TimeStamp] is less than the [Out Time] which captures when the total minutes should start, calculate the difference between [TimeStamp] and [In Time] in minutes. Otherwise, subtract the difference between [TimeStamp] and [Out Date] from 15.

Zeros where the time grouping is before the In Time, calculated time difference when it's in range, and negative numbers where the time grouping is after both the In Time and Out Time.

A little more cleanup within the tool, recalculating the Minutes so that if they are greater than 15, they become 15 (the largest allowable interval), and replacing negative numbers with zero.

Next, use a DateTime tool to convert the TimeStamp (which will be used as columns in the crosstab) to text, removing the date piece.

Crosstab everything so that the intervals create the columns. And Dynamic Rename to replace the _ with :

Let me know if this helps.

Cheers!

Esther