Time entry Smartsheet vs. Excel

Hi All,

Problem - I have a VERY simple Excel spreadsheet for collecting hours and it has been working fine, but now I want to use some functionality I know that will be easier to complete if I move the sheet to SS. I exported the sheet to SS and my very simple formula no longer works.

Goal - Have SS return the the difference (hours worked) between Start and End columns.

Details - The formula in Excel was =sum(End-Start), which I changed to =SUM([End Time]@row - [Start Time]@row). The results using the SS formula is the #Invalid Operation error.

Any thoughts? All help is greatly appreciated!

Stephanie

Find more posts tagged with

Time Calculation

Accepted answers

All comments

Spoonhead

It looks like there is an issue with the formula syntax when you moved your spreadsheet from Excel. The formula =SUM([End Time]@row - [Start Time]@row) is incorrect for calculating time differences in Smartsheet.

In Smartsheet, to calculate the difference between two times and get the result in hours, you should use the HOUR and MINUTE functions combined with some arithmetic to correctly handle the time difference. Here is the adjusted formula you can use:

=IFERROR((HOUR([End Time]@row) + MINUTE([End Time]@row) / 60) - (HOUR([Start Time]@row) + MINUTE([Start Time]@row) / 60), "WHATEVER EVER MESSAGE YOU WANT TO SEE")

This formula calculates the hour and minute parts of both the start and end times, converts the minutes to a fractional hour, and then subtracts the start time from the end time. The IFERROR function is used to handle any potential errors gracefully. You may need to adjust to accommodate your specific column headers

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.