Want to get involved? We're always looking for ideas and content for Weekly Challenges.
SUBMIT YOUR IDEASolution attached
Reposting to add spoiler (to get challenge tracker to work correctly)
Went down a rabbit hole at first trying to use multi-row formulas to eliminate overlapping end and start times and coming up with the differences between combined enter and exit times. Got within one minute of the answer, but realized would take several multi row formulas to achieve the correct result. Realized the need to find unique times and came up with a similar workflow to the others.
Hi All,
Please find my Result below.
Thanks
Mat
Can finally attach files, although cannot edit my previous reply
Good practice with Multi-Row Formula Tool!
1) Parse given date fields to calculate elapsed time in room
2) Sort by Room and Enter time to get chronological entry times
3) Calculation to flag if subsequent enter time is before previous exit( flag overlap)
3) Calculation to determine of subsequent exit time extends time in room (and adds overall light time) or if exit prior to previous entry(denoted by negative number)
4) Eliminate negative time records to account for subsequent exits prior to previous.
5) Summarize