Calculating Time Between Two Different Dates
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!
Comments
-
I don't think smartsheet supports calculations on time in general .... would you be able to do these calculations in excel instead?
-
Could you please show us a screenshot of your data ? I don't understand what your sheet looks like...
As for hours, Smartsheet can't make calculation indeed. But with some text formula we should be able to get the hour number then the minute number and to do the calculation on that.
-
What I have figured out is for the 24 hour clock as you are using, but without the : and without the seconds. Some additional work would be needed to include both of those, but it is possible. Just let me know. I figure this will at least get you started...
Here's what I have thus far... You are going to need 6 helper columns plus your 5 original columns bringing the total to 11. It seems excessive, but it helps spread things out a little bit to help keep from breaking things. The helper columns can be hidden to keep your sheet clean. Here there are in order from left to right:
Start Date, End Date, Start Time, Finish Time, Total, Date, Date Help, Hour, Hour Help, Minute, Minute Help
The first 4 in bold do not require formulas. They are the data from which the rest of the formulas automate things. Here are your formulas (change the row number from "1" to "@row" to make things easier in the long run):
Total: =SUBSTITUTE(JOIN(Date1:[Minute Help]1, " "), "-", "")
Date: =IF(IF(VALUE([Finish Time]1) < VALUE([Start Time]1), ([End Date]1 - [Start Date]1) - 1, IF(VALUE([Finish Time]1) >= VALUE([Start Time]1), [End Date]1 - [Start Date]1)) = 0, "-", IF(VALUE([Finish Time]1) < VALUE([Start Time]1), ([End Date]1 - [Start Date]1) - 1, IF(VALUE([Finish Time]1) >= VALUE([Start Time]1), [End Date]1 - [Start Date]1)))
Date Help: =IF(Date1 = "-", "-", IF(Date1 = 1, "Day", IF(Date1 > 1, "Days")))
Hour: =IF(IF(AND(VALUE(RIGHT([Finish Time]1, 2)) < VALUE(RIGHT([Start Time]1, 2)), VALUE(LEFT([Finish Time]1, 2)) < VALUE(LEFT([Start Time]1, 2))), 23 - (VALUE(LEFT([Start Time]1, 2)) - VALUE(LEFT([Finish Time]1, 2))), IF(VALUE(LEFT([Finish Time]1, 2)) < VALUE(LEFT([Start Time]1, 2)), 24 - (VALUE(LEFT([Start Time]1, 2)) - VALUE(LEFT([Finish Time]1, 2))), IF(AND(VALUE(LEFT([Finish Time]1, 2)) = VALUE(LEFT([Start Time]1, 2)), VALUE(RIGHT([Finish Time]1, 2)) < VALUE(RIGHT([Start Time]1, 2))), 23, IF(VALUE([Finish Time]1) >= VALUE([Start Time]1), VALUE(LEFT([Finish Time]1, 2)) - VALUE(LEFT([Start Time]1, 2)))))) = 0, "-", IF(AND(VALUE(RIGHT([Finish Time]1, 2)) < VALUE(RIGHT([Start Time]1, 2)), VALUE(LEFT([Finish Time]1, 2)) < VALUE(LEFT([Start Time]1, 2))), 23 - (VALUE(LEFT([Start Time]1, 2)) - VALUE(LEFT([Finish Time]1, 2))), IF(VALUE(LEFT([Finish Time]1, 2)) < VALUE(LEFT([Start Time]1, 2)), 24 - (VALUE(LEFT([Start Time]1, 2)) - VALUE(LEFT([Finish Time]1, 2))), IF(AND(VALUE(LEFT([Finish Time]1, 2)) = VALUE(LEFT([Start Time]1, 2)), VALUE(RIGHT([Finish Time]1, 2)) < VALUE(RIGHT([Start Time]1, 2))), 23, IF(VALUE([Finish Time]1) >= VALUE([Start Time]1), VALUE(LEFT([Finish Time]1, 2)) - VALUE(LEFT([Start Time]1, 2)))))))
Hour Help: =IF(Hour1 = "-", "-", IF(Hour1 = 1, "Hour", IF(Hour1 > 1, "Hours")))
Minute: =IF(ABS(VALUE(RIGHT([Finish Time]1, 2)) - VALUE(RIGHT([Start Time]1, 2))) = 0, "-", ABS(VALUE(RIGHT([Finish Time]1, 2)) - VALUE(RIGHT([Start Time]1, 2))))
Minute Help: =IF(Minute1 = "-", "-", IF(Minute1 = 1, "Minute", IF(Minute1 > 1, "Minutes")))
-
^ The above works exceptionally well, only downside is that you can't sum that Total column.
-
True, but you can sum the helper columns that provide the numeric values for the Total column.
-
@ Paul Newcome
I'm trying to do the same calculation, but using the Smartsheet auto generated columns of "Created" and Modified" which contain date and time in one field. I've tried to modify what you provided here, but haven't been able to get it work. Any help you can offer would be much appreciated.
Help Article Resources
Categories
Check out the Formula Handbook template!
