I am trying to combine 2 large sets of data produced from 2 pivot tables using a vLookup so the data/graph changes as I use a slicer to filter each table. (The Data sets are not able to be combined) The trouble is in one set of data the time is formatted as HH:MM:SS AM/PM but uses only the Hour not minutes PLUS the AM or PM (which I want as I am grouping data by the hour of the day for the chart, and the other data HH:MM:SS PLUS the AM or PM but uses the exact time i.e.:

Data set 1 23:00:00 PM

Data Set 2 11:24:25 PM

For the life of me I cannot get a helper column to convert Data set 2 to extract and show 23:00:00 PM (with no minutes and seconds) so I can use a Vlookup – I am stupid and its late, any help would be greatly appreciated

Thanks

I have tried lots of different formatting and custom formatting, also the =Hour function (But for some reason this formula does not copy down the table correctly, Im not sure why)

Data set 1

Data Set 2