Formula to assist in creation of 24/7 profile of visitor on site duration

I have been asked to do some analysis of visitors to specific sites and how long they spend at each site.

I have a list which contains the site they visited, the date and time of arrival as well as departure.

I would like to build a profile that shows the weekday and hour that they arrive and then populates each hour of the day that the individual remained on site.

The list looks like the below:
Site Arrival hr Departure hr
1 Smith St 21/07/2017 10:29 21/07/2017 12:41 5 10 5 12
2 George St 31/07/2017 10:16 31/07/2017 15:55 1 10 5 12
3 Smith St 12/02/2017 23:00 13/02/2017 07:45 7 23 6 07
4 Green Road 14/02/2017 15:57 14/02/2017 23:45 2 15 2 23
5 Green Road 07/03/2017 10:28 07/03/2017 14:48 2 10 2 14

The four final columns I have created identify the weekday and hour in which the person arrives and departs

To put it simply, if they arrived at Monday at 00:23 and left at 04:00 I would like a formula that will then populate the 5 hour time slots that person was on site with a 1

Monday
00:00 01:00 02:00 03:00 04:00
1 1 1 1 1

Can anybody help please? I can get the formula to populate the day and hour of arrival, however my issue arises with populating the hours after, especially when the duration of the stay on site goes over in to the next day...

Any assistance very, very gratefully received

its not too difficult, but problems can arise depending how you have typed the values

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Hope this works!