Formatting work schedules in an easy-to-process, and easy to enter way?

Question

I'm trying to format work schedules in google spreadsheets in a way that makes them easy to process. I have a sheet that will tell you who is supposed to be here on what day, who was gone, and interval counts. The biggest hangup is the requirement that historical schedules need to be maintained and accessible through the other sheet that displays the intervals for that day.

A single person's schedule needs to be able to be changed at any time, or any number of times, and all of the previous schedules for that individual need to be maintained.

I've puzzled through a variety of formats and it ends up either being extremely hard to add data to (adding/editing a schedule), or extremely difficult to pull data from (to count intervals and see who is scheduled on what day).

The current format:

I do, with a little work, pull schedule data from this and put it on display. However, I cannot maintain any historical schedules with this format.

The new format I tried:

This is MUCH easier to pull data from, and I could have new schedules added that would replace old ones while maintaining historical schedules. However, this would be a nightmare for data entry, and would not be acceptable.

Suggestions?

Answer 1

I suggest the following entry system: each time someone's schedule is changed, a new row is added at the bottom of "Entry" sheet, specifying the name, the date from which the schedule is valid, and start/end times for every day.

+---+--------------+------------+----------+---------+---------+---------+
|   |      A       |     B      |    C     |    D    |    E    |    F    |
+---+--------------+------------+----------+---------+---------+---------+
| 1 |              |            |        Monday      |      Tuesday      |
| 2 | Name         | Valid from | Start    | End     | Start   | End     |
| 3 | John Smith   | 9/1/2015   | 8:00 AM  | 5:00 PM | 8:30 AM | 4:00 PM |
| 4 | Someone Else | 9/1/2015   | 9:00 AM  | 4:30 PM | 7:45 AM | 5:00 PM |
| 5 | John Smith   | 9/15/2015  | 11:00 AM | 6:00 PM | 8:00 AM | 3:00 PM |
+---+--------------+------------+----------+---------+---------+---------+

You want to pull out the data from the above to make something like this in another sheet:

+---+--------------+----------+---------+---------+---------+
|   |      A       |    B     |    C    |    D    |    E    |
+---+--------------+----------+---------+---------+---------+
| 1 |              |        Monday      |      Tuesday      |
| 2 | Name         | Start    | End     | Start   | End     |
| 3 | John Smith   | 11:00 AM | 6:00 PM | 8:00 AM | 3:00 PM |
| 4 | Someone Else | 9:00 AM  | 4:30 PM | 7:45 AM | 5:00 PM |
+---+--------------+----------+---------+---------+---------+

Command for cell A3 of second sheet:

=sort(unique(Entry!A3:A))

which returns sorted list of people, eliminating repetition.

For cell B3 of the second sheet:

= vlookup(today(), filter(Entry!$B$3:$F, Entry!$A$3:$A = $A3), column(B3)-column($A3)+1, true)

This will correctly propagate to the rest of the table. The logic is as follows:

• filter returns only the rows with the given name
• vlookup matches the latest "valid from" date that is <= today.
• the appropriate column from that row is returned.

To look up the schedule as it was at another day, replace today with a reference to a cell containing the desired date.