Create a pivot table with text data and format

Hi all,

I am trying to create a pivot table that will be showing the utilization on an equipment that I am renting out.

Ideally, the pivot table will have the hours of operations 3AM to 11PM by hour, (21 lines) going down on the row axis and the dates on the column axis. Within the cells, I would like to have either a "YES" or "NO" depending on the report that I get.

See attached for sample report.example-rent.xlsx

Right now, I get a report that tells me the time in and time out. I did an "IF" function where depending on the Time In and Time Out, each cell will either spit out "Y" or "N".

I am having trouble figuring out how to pivot this report to get the hours of operation on the "row label" and how to get it to report Y or N within the values.

Any idea how I can do this?

Thanks

So, I have been able to get the pivot table "row label" to output the hours of operations (3AM - 11PM) by creating a separate column rounding each time in to the nearest hour. In addition, I created 21 line items within the report with each hour listed from 3AM - 11PM so no matter what the report has, the row label will always show those every hour.

My problem now is figuring out how to output the values for each date to output a "Yes" or "No". I know I can convert the "Y" and "N" to a number but the problem is how do consolidate instances when this equipment was used more than once in a day.

For example, If someone used this equipment from 11AM - 1PM and another person used it from 3PM - 6PM. This would be two separate lines on the report I get and I would like this pivot to show "N" for each hour from 3AM-11AM, 1PM - 3PM, 6PM - 11PM and "Y" from 11AM - 1PM and 3PM - 6PM.

Thanks