I am having some difficulty finding the optimal solution to a problem I am having with Smartsheet. Right now, I have a large sheet which has time on one axis (column A is every date between 1/1/2018 to 1/1/2019).
For example, for rows 1/1/2018 - 1/3/18, there is a starting time on 1/1/2018 (example, 8:00:00), and then an ending time on 1/3/18 (example, 16:00:00). The time between those two dates and two times on those dates is what I am looking to calculate.
This is on a sheet with 2000+ rows, and each interval is different (some may be three days, some may be the same day). The difference between all of these is another column with location. This looks like:
- Row 1) Date, Location [X], Arrival Time (8:00:00), Departure Time (blank)
- Row 2) Date (the next day), Location [X] (the same as above), Arrival time (blank), Departure Time (16:00:00)
I need to calculate the time spend in Location [X] between Arrival time on one date to the Departure time on the next date.
Please let me know what you think the optimal solution is to this problem, I am open to anything!
