Creating an end time based on an amount of time and a start time.

I am trying to create a formula so that if I start something at 7:00 AM and then say it will be 30 minutes long, an end time column created at the would display 7:30 AM.

I looked through all the time formulas and could not get any to work properly. I admit that might be my lack of formula knowledge. I know already that Smartsheet has problems with time, but if anyone can help me make a formula for this I would greatly appreciate it.

Find more posts tagged with

Accepted answers

All comments

Bassam Khalil

Hi @Keenan Kibrick

Hope you are fine, i created a sample sheet for your question, and the following is the formula to calculate end time using the created time ( System column created ) and duration.

1- Hours Column =VALUE(MID(Created@row, 12, 2)) convert to column formula

2- Minutes Column =VALUE((MID(Created@row, 15, 2))) convert to column formula

3- Total Minute =IF((Duration@row + Minutes@row) > 60, (Duration@row + Minutes@row) - 60, (Duration@row + Minutes@row)) convert to column formula

4- Total Hours =IF((Duration@row + Minutes@row) > 60, ROUND((Duration@row + Minutes@row) / 60)) + VALUE(Hours@row) convert to column formula

5- Period =MID(Created@row, 18, 5) convert to column formula

6- End Time =(IF([Total Hours]@row < 10, "0" + [Total Hours]@row, [Total Hours]@row) + ":" + IF([Total Minute]@row < 10, ("0" + [Total Minute]@row), [Total Minute]@row + " " + Period@row)) convert to column formula

the following screenshot show the sample sheet

Alisia Anderson

I am doing something similar but far less complex b/c the time is a manual entry in Column A (let's say 7pm). I would like to use a formula to show 3 hours prior to the time in Column A (show 4pm in Column B). How would I do that? Would I use the same logic as above and create helper columns to extract hour, subtract by 3, and then show the final?

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.