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?

Find more posts tagged with

Accepted answers

TVang

(([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) and VALUE(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) and VALUE(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:

https://help.smartsheet.com/functions

All comments

TVang

(([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.

HoursExpression = VALUE(LEFT([Time Reported]@row,2)) - VALUE(LEFT([Time of Incident]@row,2)

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

You can find documentation on all of the Smartsheet functions at:

https://help.smartsheet.com/functions

ctsammon

Thank you very much!! This worked perfectly.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.