Car study that goes before and after midnight

[j1mbo]

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


`

 

[Alex Blakenburg]

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.