How do I calculate End Date and End Time based on Start Date, Start Time, and duration in hours?

TRB6965

Would like to use the new TIME function, if possible. Working hours per day would be 24. Working days are all 7 days of the week. A task could span multiple dates, for example: Start Date = 6/26/2025, Start Time = 10:30pm, Duration (manual entry) = 3.75 hours. Formula(s) should result in End Date = 6/27/2025, and End Time = 2:15am. I would prefer to have Start Date and Start Time in the same column, and same for End Date and End Time, but it would be OK of they need to be in separate columns. Searching Community always takes me to Paul N.'s time formulas threads, which are great, but those don't use the new TIME function. Thank you.

TRB6965

@Paul Newcome, I just tested your IFERROR advice and it works great! Thank you so much for your help with all of this (and your patience). This was a great experience for me, since I am new to Smartsheet formulas.

I also discovered that, if I don't convert the [End Date] and [End Time] columns to column formula, I can use these formulas in some sections of the sheet but not in others. That is a great fit for my project plans.

Thanks again!!! Now I want to learn more!

Paul Newcome

@TRB6965 The new TIME function doesn't accommodate going into a different date. It will work if you are keeping it in the same date, but the amount of additional logic / formula building you would have to use to move into another date could increase quite a bit depending on your needs.

Do you need to cross multiple dates, or would it always be less than a 24 hour period?

TRB6965

@Paul Newcome, Thanks for jumping in here. The task list spans multiple dates (i.e. - Thurs thru Sun), and it is possible that an individual task could start late on one day and finish early the next day. If that creates so much complexity that it will prevent me from meeting our objective, I MIGHT be able to break the logic down into multiple pieces, like a set of Thursday tasks, followed by a set of Friday tasks, etc. Then I could apply the logic needed to several sets of tasks, but with each set being separate. My preference would be to have one set of tasks spanning Thurs thru Sun, but I can break it up if necessary. What are your thoughts on that? Do you think the new TIME function has a role in this?

Paul Newcome

Is a single task going to cross multiple dates, or would a single task never be more than 24 hours?

TRB6965

Hi @Paul Newcome, a single task could go across two dates, but never more than two. In the example in my original post I described a task that starts at 10:30pm on 6/26/2025 with a duration of 3.75 hours. That would result in an End Date of 6/27/2025 and End Time of 2:15am. I might be able to make this work with a limitation that a single task would never be more than 24 hours, if that restriction is necessary to get this to work. Thanks again!

TRB6965

@Paul Newcome, I just learned that this is extremely easy to do in Excel. A quick online search and I had it functioning in 10 minutes. Please see attached. There must be a way to do this in Smartsheet, but my very limited Smartsheet experience is preventing me from coming up with the correct formula(s). Also, I should have mentioned earlier that I am OK with using hidden helper columns if we can get that to work. I really would like to stay with Smartsheet as my tool for project management, and not revert back to using Excel! Thanks again!

Test Project Task List with Durations.xlsx

Darren Mullen

@TRB6965 As @Paul Newcome alluded to, it can be done. But, there are considerations on the logic to implement to account for all conditions. Just takes time to develop the formulas for your specific use case.

As I like to say… anything is possible with time and money. Just depends on if it's worth the investment or not.

TRB6965

@Darren Mullen, thanks for chiming in here. This got way more complicated than I expected. For now I will have to go back to calculating/inputting the End Date and End Time manually. Perhaps Smartsheet will be able to do these date/time calculations someday. Thanks again to both you and @Paul Newcome.

Paul Newcome

@TRB6965 If you are not opposed to helper columns, there should be a solution in the large time thread that will work for you even though it doesn't use the new function.

TRB6965

Hi @Paul Newcome, I am open to using helper columns. Part of my problem is that I am new to Smartsheet formulas, so as I go through your large time thread I can't determine whether a given formula is relevant and applicable to my objective. Can you point me to a particular formula (or formulas) in your large time thread that would be a good starting point for me? Any guidance is appreciated!

Paul Newcome

@TRB6965 I didn't see anything right off in my notes anywhere, so I went ahead and built out a solution (with some new tricks I've discovered along the way).

[End Date]

=[Start Date]@row + (FLOOR(VALUE(LEFT([Start Time]@row , FIND(":", [Start Time]@row ) - 1)) + Duration@row , 24) / 24)

[End Time]

=TIME([Start Time]@row , 0, 2) + (Duration@row * 60)

TRB6965

Hi @Paul Newcome, sorry for the delayed response. For some reason I'm not getting email notifications alerting me to Smartsheet Community updates. Thank you for accepting this challenge! I will test out your two new formulas ASAP and let you know what happens. I'm curious about the FLOOR function, and also whether I can do the time in 12-hour format.

Paul Newcome

@TRB6965 No worries.

The only adjustment for a 12hr based solution would be to the [End Date] formula:

=[Start Date]@row + (FLOOR(VALUE(LEFT(TIME([Start Time]@row, 1), FIND(":", TIME([Start Time]@row, 1)) - 1)) + Duration@row, 24) / 24)

TRB6965

Hi @Paul Newcome. This is great! I think we're really close! When I try to use the first date formula you gave me, it calculates the End Time correctly, but there are issues with the End Date calculation:

If Start Date = 07/01/25, Start Time = 10:00 PM, and Duration in Hours = 3, the result for End Date = 07/01/25 and End Time = 1:00 AM. The End Date should be 07/02/25.

If Start Date = 07/01/25, Start Time = 10:00 PM, and Duration in Hours = 13, the result for End Date = 07/01/25 and End Time = 11:00 AM. The End Date should be 07/02/25.

If Start Date = 07/01/25, Start Time = 10:00 PM, and Duration in Hours = 14, the result for End Date = 07/02/25 and End Time = 12:00 PM. This is correct.

If Start Date = 07/01/25, Start Time = 10:00 PM, and Duration in Hours = 15, the result for End Date = 07/02/25 and End Time = 1:00 PM. This is correct.

So the only time the End Date is correct is when the duration is long enough to get you to noon or beyond the next day. I'd like to be able to use a duration (in hours) that traverses 2 dates, if possible.

When I try to use the second date formula you provided (today) I get #UNPARSEABLE in my End Date column. I don't know what's going on there.

One other thought on this… When using the first date formula you provided, and converting to column formula, if I don't have anything in the Start Date or Start Time fields, I get #INVALID VALUE and #INVALID DATA TYPE, respectively. For my projects, sometimes we will have tasks listed, but no Start Date or Start Time know yet. In that scenario, is there a way to just make the End Date and End Time fields blank?

Thanks again for all the great help!!

Paul Newcome

The second [End Date] formula is the one you want for 12 hour format. It is working in my sheet though, so are you able to provide a screenshot of the formula open in your sheet as if you are about to edit it?

The final step is to add an IFERROR to both formulas, but I only ever add that after I have made sure the formulas are working as intended. Then you would wrap them in an IFERROR like so:

=IFERROR(original_formula, "")

TRB6965

Hello again @Paul Newcome. We can forget about the first [End Date] formula you sent on 6/27/2025. As you said, we want to us the second [End Date] formula you sent on 7/1/2025. Although I should note that the [End Time] formula I'm using is the one you sent on 6/27/2025. That seems to be working.

Here are the results using the second [End Date] formula:

Here is the [End Date] formula:

Here is the [End Date] formula, bigger:

Here is the [End Time] formula:

Here is the [End Time] formula, bigger:

AS you suggested, I'll wait to add the IFERROR until after we know the formulas are working as desired. Using the [End Time] formula as an example, is this how I would wrap the formula in an IFERROR?

IFERROR(=TIME([Start Time]@row , 0, 2) + (Duration@row * 60), "")

note I have a space before the "" not sure if that's right

Thanks!