How to make a Team Schedule tracker

[parthsahay]

To explain my problem, let me give you all a basic structure on how I receive the data. 

Please note: I really don't want a complete solution but a direction I can go. All of this is too much of logic which I'm unable to think so asking for some genuine help.

Statement:

In my organization, daily morning we receive a class schedule sheet containing the tutor email id, Class timings, class link, channel ids etc. each tutor gets minimum one and maximum 5 sessions daily as per availability and cohort.  Now there are many different teams of tutors under different managers. What I want is to fetch the data from the central sheet, sort my team out and see when the rest of my team members are available or do not have session while remaining members are available. I have used lookup and count fxn to fetch the unique IDs and number of session each tutor received but how to present the class timings and time of availability, or to say in easier words,  when according to the schedule, these tutors are available so that in case or replacement or reassignment of class they can be reached as per tracker and we do not have to call 100s of tutor one by one just to ask them their availability. This tracker should also show if there is no tutor name fetched in the central sheet then that tutor is either unavailable or on leave. or how to create a list of present tutor on that day would be a much better option. All of this is too much of logic which I'm unable to think so asking for some genuine help. A sample sheet is attached in the link. 

Link to the sample sheet: https://docs.google.com/spreadsheets/d/18W7ou_FBQ8wHTXyiCQjaBkWE6WJkamsQxQ... 

[#2]

@parthsahay Can't access the sheet. Since you have Office 365, please try sharing via OneDrive.

[#3]

Please check now

[#4]

@parthsahay Thanks, that worked. But now I don't understand what you want. Based on the data in the file, how would you want to summarize it if you had to do it manually? I.e. without formulas.

[#5]

Alright. If I had to do manually I'll take each session details and create a timeline for each tutor. In each timeline I have to put the class timings and free periods separately. For example If I'm having sessions at 3 4 and 6 pm respectively, I'm free at 5, 7, 8 and 9 pm and in case of emergency I'm available for the reassignment or as a backup. What if we create a sheet or table (Pivot table if it is possible) for the tutors in a list of my team from the list of tutors of the whole Organization to track and reassign the session as per requirement. Hope this helps!!

[#6]

@parthsahay Still not clear in my mind what you want, but perhaps the attached file helps. If not, can you create a "timeline" for a few tutors manually?

Note, I inserted a column for the Session Start Time.

[#7]

Hey, I saw a pivot table you made with respect to the timings. It was very close to what I want. All I want to change is to add the whole timeline from the start of the day to the EOD in terms of Yes/No or 0/1 as in available or not available respectively.

Link is given below:- https://docs.google.com/spreadsheets/d/18W7ou_FBQ8wHTXyiCQjaBkWE6WJkamsQxQlIFrRz3WE/edit#gid=4645101...

[#8]

@parthsahay I'm rather pragmatic and don't want to overcomplicate matters. Since the pivot table option seems to work just fine, I added a few dummy rows to the bottom of the table with tutor " " who has classes every hour from 10AM to 10PM without students. That gives you the time span you want in the pivot table. Would that work for you?

[#9]

In a few place it seems tutor is having 2 session at some places. Might be because of multiple entries on the same timeline. Rather than that everything is how I wanted. I'll just put conditional formatting to put Yes or No in the same place if required. Thanks a lot @Riny_van_Eekelen