Car study that goes before and after midnight

j1mbo

New Member
Joined
Apr 7, 2021
Messages
1
Office Version
  1. 2016
Platform
  1. Windows
Hi All,

I'm running a study where I measure car speeds passing a speed detection device. The device records speeds and spits out one excel spreadsheet per date. I've put together all the spreadsheets using PowerQuery in Excel which has worked nicely.

The problems start to arise with the fact that the shift goes from 7pm - 4am, i.e. past midnight and into a new date. So if I'm analysing data on say the 16/09/20, it's really analysing the shift from the previous night (15/09/20) from midnight to 4am, and then the data actually on the 16/09/20 from 7pm to midnight.

Here's what I want to achieve:

1. Create a column such as "date shift started" which separates shifts by the date they started and only groups data from 7pm-4am. So for example if the shift starts on 15/09/20, I want the data associated with that shift to be 15/09/20: 7:00pm - 11:59pm and 16/09: 00:00am. - 04:00am.

2. Order the hourly data from 7pm - 4am. Currently excel orders it from 12am - 11pm and I don't know how to customise the order. Refer attached picture.

3. The raw data has the time stamps in the correct "Time" format. However when I import to data model in PowerPivot, it converts them to a DateTime format of "30/12/1899 7:25:00PM". How can I separate this into a time only format?

Any help would be greatly appreciated,

Cheers

Jimbo


`
 

Attachments

  • data table from query.PNG
    data table from query.PNG
    81.3 KB · Views: 4
  • hourly data in wrong order.PNG
    hourly data in wrong order.PNG
    33.8 KB · Views: 3
  • time changed to datetime in powerpivot.PNG
    time changed to datetime in powerpivot.PNG
    238.5 KB · Views: 3

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Alex Blakenburg

Active Member
Joined
Feb 23, 2021
Messages
372
Office Version
  1. 365
Platform
  1. Windows
If you have a "Changed Type" step, try deleting that step and then manually click on symbol in the top left corner of the column heading for the time and select Time from the drop down.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,063
Messages
5,622,470
Members
415,897
Latest member
Barry18180

We've detected that you are using an adblocker.

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.
Go back
Top