Calculate difference between two dates and times
In the above example, I need a formula to calculate how long (in hours) it took to report an incident. So Date and Time reported - Date and Time of incident. In the above example, row 1 report time (hours) should be 23, and row 2 should be 10.
Time of Incident and Time Reported are in military time.
Any suggestions?
Best Answer
-
(([Date Reported]@row - [Date of Incident]@row)*24) + VALUE(LEFT([Time Reported]@row,2)) - VALUE(LEFT([Time of Incident]@row,2)) + (VALUE(RIGHT([Time Reported]@row,2)) - VALUE(RIGHT([Time of Incident]@row,2)))/60
Hi, @ctsammon , this is how the formula works.
Subtracting "Date of Incident" from "Date Reported" gives you the number of days elapsed. Multiplying it by "24" converts result into units of hours. The day-to-hours expression is:
DayToHours =
([Date Reported]@row - [Date of Incident]@row)*24
In the "time" columns, the first two digits are the hours, but the last two are minutes. So we need to work with them separately.
Use the LEFT() function to grab the hour digits from the text. Use the VALUE() function to get the numeric value:
VALUE(LEFT([Time of Incident]@row,2)
andVALUE(LEFT([Time Reported]@row,2))
. Subtract the "Time of Incident" hours from "Time Reported" hours. The the hours expression is:HoursExpression =
VALUE(LEFT([Time Reported]@row,2)) - VALUE(LEFT([Time of Incident]@row,2)
Next, use the RIGHT() function to grab the minute digits from the text, then use the VALUE() function to get the numeric value:
VALUE(RIGHT([Time of Incident]@row,2)
andVALUE(RIGHT([Time Reported]@row,2))
. Subtract the "Time of Incident" minutes from "Time Reported" minutes, and then divide the result by 60 to convert it to units of hours. The minutes-to-hours expression is:MinutesToHours =
(VALUE(RIGHT([Time Reported]@row,2)) - VALUE(RIGHT([Time of Incident]@row,2))/60
To find the number of hours between when the incident occurred and when it was reported...
DayToHours + HoursExpression + MinutesToHours
(([Date Reported]@row - [Date of Incident]@row)*24) + VALUE(LEFT([Time Reported]@row,2)) - VALUE(LEFT([Time of Incident]@row,2)) + (VALUE(RIGHT([Time Reported]@row,2)) - VALUE(RIGHT([Time of Incident]@row,2)))/60
You can find documentation on all of the Smartsheet functions at:
Answers
-
(([Date Reported]@row - [Date of Incident]@row)*24) + VALUE(LEFT([Time Reported]@row,2)) - VALUE(LEFT([Time of Incident]@row,2)) + (VALUE(RIGHT([Time Reported]@row,2)) - VALUE(RIGHT([Time of Incident]@row,2)))/60
Hi, @ctsammon , this is how the formula works.
Subtracting "Date of Incident" from "Date Reported" gives you the number of days elapsed. Multiplying it by "24" converts result into units of hours. The day-to-hours expression is:
DayToHours =
([Date Reported]@row - [Date of Incident]@row)*24
In the "time" columns, the first two digits are the hours, but the last two are minutes. So we need to work with them separately.
Use the LEFT() function to grab the hour digits from the text. Use the VALUE() function to get the numeric value:
VALUE(LEFT([Time of Incident]@row,2)
andVALUE(LEFT([Time Reported]@row,2))
. Subtract the "Time of Incident" hours from "Time Reported" hours. The the hours expression is:HoursExpression =
VALUE(LEFT([Time Reported]@row,2)) - VALUE(LEFT([Time of Incident]@row,2)
Next, use the RIGHT() function to grab the minute digits from the text, then use the VALUE() function to get the numeric value:
VALUE(RIGHT([Time of Incident]@row,2)
andVALUE(RIGHT([Time Reported]@row,2))
. Subtract the "Time of Incident" minutes from "Time Reported" minutes, and then divide the result by 60 to convert it to units of hours. The minutes-to-hours expression is:MinutesToHours =
(VALUE(RIGHT([Time Reported]@row,2)) - VALUE(RIGHT([Time of Incident]@row,2))/60
To find the number of hours between when the incident occurred and when it was reported...
DayToHours + HoursExpression + MinutesToHours
(([Date Reported]@row - [Date of Incident]@row)*24) + VALUE(LEFT([Time Reported]@row,2)) - VALUE(LEFT([Time of Incident]@row,2)) + (VALUE(RIGHT([Time Reported]@row,2)) - VALUE(RIGHT([Time of Incident]@row,2)))/60
You can find documentation on all of the Smartsheet functions at:
-
Thank you very much!! This worked perfectly.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!