Weekly Challenges
Solve the challenge, share your solution and summit the ranks of our Community!Also available in | Français | Português | Español | 日本語
IDEAS WANTED
Want to get involved? We're always looking for ideas and content for Weekly Challenges.
SUBMIT YOUR IDEA
I'm surprised that you can't use some DateTime functions on 'Time' only data, but it does make good sense and practice to always have a date associated with your data and then change your type at the end (:
I learned so much about date-time functions and the generate rows tool while doing this challenge. Definitely an interesting problem to solve!
Definitely made this one harder than it needed to be ([Insert Michael Scott comment here]). Thought the general solution was wrong for quite a while, but, surprise, found out the error was mine.
Need to study the other solutions as I can see they are much simpler (though not keen on the brute force Generate Rows idea)
- Convert times to useable format
- Sort by room, then by entry, then by exit
- Calculate time in room (without considering duplicates)
- Calculate overlap time in room in one direction (i.e. 2nd entry before first exit)
- Calculate overlap time in room in other direction (i.e. 2nd exit before 1st exit) - this is the step I overlooked initially
- Grouped each figure by room and added the three times (in the three steps above)
- Joined data
- Subtracted difference in overlap times from total time (i.e. Total - (Overlap 1 - Overlap 2))
- Cleaned up data
