[FONT="]Hi there,[/FONT] [FONT="] [/FONT] [FONT="]I have a dataset that has a Car ID (Unit ID), the time (and date) the driver logged in and the time (and date) the driver logged out. I am trying to calculate the number to unique units logged on during each 15 minute block of time each day and display it in a table with the date down the left side and the 15 minute block across the top. I keep running into memory errors because the data set is huge (millions of rows). The other issue I have is that I can't figure out how to show which date the 15 minute block occured in if the unit was logged in over night (i.e. logged on at 1900hrs on January 1st and logged out at 0700hrs on January 2nd). [/FONT] [FONT="] [/FONT] [FONT="]I have a sample data set I've dumped into an Excel spreadsheet:[/FONT] [FONT="] [/FONT] [FONT="]https://www.dropbox.com/s/ovt0mtcgov66lk3/Sample Data.xlsx?dl=0[/FONT] [FONT="] [/FONT] [FONT="]Any help would be greatly appreciated. I tried this solution but this didn't seem to work:[/FONT] [FONT="] [/FONT] [FONT="]https://community.powerbi.com/t5/Desktop/15-Minute-Increments-between-2-Timestamps/m-p/330330[/FONT] [FONT="] [/FONT] [FONT="]Thanks,[/FONT]
Thanks for this. I need to take it one step further though and also count the unit as "Logged In" if it logged in during a previous time block and is still logged in. So for example, if unit 101 logged in at 01/01/2017 at 06:00 and logged out at 01/02/2017 at 04:30 it needs to be included in the count of the log in for the following records. How can I do this? Right now the solution only counts it as logged in at the time that it logs in at. So for example if unit 101 logged in at 01/01/2017 at 06:00 that is the only time block it is counted as logged on.
How many rows of data does your original table have ? I am assuming you are doing it via power bi, are you using the same query I was using ? Yes the data could be previously filtered if you have unwanted records
We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel
Which adblocker are you using?
Disable AdBlock
Follow these easy steps to disable AdBlock
1)Click on the icon in the browser’s toolbar. 2)Click on the icon in the browser’s toolbar. 2)Click on the "Pause on this site" option.
Go back
Disable AdBlock Plus
Follow these easy steps to disable AdBlock Plus
1)Click on the icon in the browser’s toolbar. 2)Click on the toggle to disable it for "mrexcel.com".
Go back
Disable uBlock Origin
Follow these easy steps to disable uBlock Origin
1)Click on the icon in the browser’s toolbar. 2)Click on the "Power" button. 3)Click on the "Refresh" button.
Go back
Disable uBlock
Follow these easy steps to disable uBlock
1)Click on the icon in the browser’s toolbar. 2)Click on the "Power" button. 3)Click on the "Refresh" button.
