Formula to calculate End Date and Time based on Start Date, Time and Task duration

Shanelle

@Paul Newcome Thank you so much!! It works! I did have the wrong column type for the completion date.

Paul Newcome

We finally got it working! That's great! Happy to help. 👍️

And don't feel bad about the column type. I do it to myself all the time. Hahaha.

Lyn Pringle

@Paul Newcome Please can you help me. :)

I have made use of the 24hr formula to calculate the number of hours worked - which works like a bomb, thank you - however not able to figure out how to create a running total.

Do you have a special formula to autosum the number of hours worked.

My sheet looks like this

Paul Newcome

@Lyn Pringle If you are using the solution above, you are going to want to reference the Duration Column. That houses the numerical values that can be used in math based calculations.

Zevy L

@Paul Newcome, can you help me with a similar question?

If I separate columns, one for date and one for time, is there a way to set a reminder let's say 24 hours after the time indicated across all columns? So for instance, I have row #1 entered as June 10, 10:00am and row #2 entered as June 20, 11:00am. Can I set a a 24 reminder so that row #1 will remind me at and row #2 will remind me on June 21, 11:00am?

Paul Newcome

@Zevy L If I remember correctly, there is actually a thread where this exact thing was discussed. Basically what you will need to do is convert the time into a number and then set up 24 automations (one for each hour) with the trigger being date based and the condition being the time.

You can use this to pull the hour number and have it converted to a 24 hour base so that you do not need to build additional criteria for AM and PM into your Automation:

=VALUE(LEFT([Time Column]@row, FIND(":", [Time Column]@row) - 1)) + IF(CONTAINS("p", [Time Column]@row), IF(VALUE(LEFT([Time Column]@row, FIND(":", [Time Column]@row) - 1)) <> 12, 12), IF(VALUE(LEFT([Time Column]@row, FIND(":", [Time Column]@row) - 1)) = 12, -12))

Zevy L

Thank you, Paul! How do I "pull the hour number and have it converted to a 24 hour base"?

Zevy L

Paul, I just tried setting it up according to you instructions. If the time is set for say 1pm, will I get the reminder at 1pm? The automation doesn't seem that way... (see screenshot attached

Capture.JPG

).

Paul Newcome

No. You would need to change the time at the bottom of the Trigger portion. You would also need to change the trigger to run off of a date field and select the column that contains the dates that the reminders would be sent.

Mindfull

https://community.smartsheet.com/discussion/comment/248173#Comment_248173

Paul, I am also new to Smartsheet. I have the same problem statement, I want to get a date and time based on the given start date time and total hours spent.I tried the formula suggested by you but I could not accomplish it.

Brenna Saunders

Hello - i am trying to create this same type of formula, but am getting some funny errors and not producing date values. Any ideas on how to resolve?

SS- date formulas.PNG

I thought it would also be helpful if i included my formulas:

DR Actual Start: Date

DR Actual Start Time: Text

Actual Duration: Text

Duration: =(VALUE(LEFT([DR Actual Start Time]2, FIND(":", [DR Actual Start Time]2) - 1)) + IF(VALUE(LEFT([DR Actual Start Time]2, FIND(":", [DR Actual Start Time]2) - 1)) <> 12, IF(CONTAINS("p", [DR Actual Start Time]2), 12), IF(CONTAINS("a", [DR Actual Start Time]2), -12)) + VALUE(MID([DR Actual Start Time]2, FIND(":", [DR Actual Start Time]2) + 1, 2)) / 60) + [Actual Duration]2

Completion Date: =[DR Actual Start Time]@row + INT(INT([Duration 1]@row) / 24)

Completion Time: =MOD(INT((VALUE(LEFT([DR Actual Start Time]2, FIND(":", [DR Actual Start Time]2) - 1)) + IF(VALUE(LEFT([DR Actual Start Time]2, FIND(":", [DR Actual Start Time]2) - 1)) <> 12, IF(CONTAINS("p", [DR Actual Start Time]2), 12), IF(CONTAINS("a", [DR Actual Start Time]2), -12)) + VALUE(MID([DR Actual Start Time]2, FIND(":", [DR Actual Start Time]2) + 1, 2)) / 60) + [Actual Duration]2), 24) + IF(MOD(INT((VALUE(LEFT([DR Actual Start Time]2, FIND(":", [DR Actual Start Time]2) - 1)) + IF(VALUE(LEFT([DR Actual Start Time]2, FIND(":", [DR Actual Start Time]2) - 1)) <> 12, IF(CONTAINS("p", [DR Actual Start Time]2), 12), IF(CONTAINS("a", [DR Actual Start Time]2), -12)) + VALUE(MID([DR Actual Start Time]2, FIND(":", [DR Actual Start Time]2) + 1, 2)) / 60) + [Actual Duration]2), 24) <> 12, IF(CONTAINS("p", IF(MOD(INT([Duration 1]2), 24) >= 12, "pm", "am")), -12), IF(CONTAINS("a", IF(MOD(INT([Duration 1]2), 24) >= 12, "pm", "am")), 12)) + ":" + IF(((VALUE(LEFT([DR Actual Start Time]2, FIND(":", [DR Actual Start Time]2) - 1)) + IF(VALUE(LEFT([DR Actual Start Time]2, FIND(":", [DR Actual Start Time]2) - 1)) <> 12, IF(CONTAINS("p", [DR Actual Start Time]2), 12), IF(CONTAINS("a", [DR Actual Start Time]2), -12)) + VALUE(MID([DR Actual Start Time]2, FIND(":", [DR Actual Start Time]2) + 1, 2)) / 60) + [Actual Duration]2 - INT((VALUE(LEFT([DR Actual Start Time]2, FIND(":", [DR Actual Start Time]2) - 1)) + IF(VALUE(LEFT([DR Actual Start Time]2, FIND(":", [DR Actual Start Time]2) - 1)) <> 12, IF(CONTAINS("p", [DR Actual Start Time]2), 12), IF(CONTAINS("a", [DR Actual Start Time]2), -12)) + VALUE(MID([DR Actual Start Time]2, FIND(":", [DR Actual Start Time]2) + 1, 2)) / 60) + [Actual Duration]2)) * 60 < 10, "0") + ((VALUE(LEFT([DR Actual Start Time]2, FIND(":", [DR Actual Start Time]2) - 1)) + IF(VALUE(LEFT([DR Actual Start Time]2, FIND(":", [DR Actual Start Time]2) - 1)) <> 12, IF(CONTAINS("p", [Duration 1]4), 12), IF(CONTAINS("a", [DR Actual Start Time]2), -12)) + VALUE(MID([DR Actual Start Time]2, FIND(":", [DR Actual Start Time]2) + 1, 2)) / 60) + [Actual Duration]2 - INT((VALUE(LEFT([DR Actual Start Time]2, FIND(":", [DR Actual Start Time]2) - 1)) + IF(VALUE(LEFT([DR Actual Start Time]2, FIND(":", [DR Actual Start Time]2) - 1)) <> 12, IF(CONTAINS("p", [DR Actual Start Time]2), 12), IF(CONTAINS("a", [DR Actual Start Time]2), -12)) + VALUE(MID([DR Actual Start Time]2, FIND(":", [DR Actual Start Time]2) + 1, 2)) / 60) + [Actual Duration]2)) * 60 + IF(MOD(INT([Duration 1]2), 24) >= 12, "pm", "am")

Expected Completion Time: =[Completion Date]@row + " " + [Completion Time]@row

Ben Goldblatt

Hi @Brenna Saunders,

It sounds like you're looking to be able to enter a start date, time, and duration to then have the actual end date and time calculated automatically. You may be able to leverage the Dependency functionality for the date automation, but you would need to use formulas for the time calculations. @Paul Newcome may be the best resource for this in looking through this thread (and feel free to chime in here Paul if you have a moment), but I highly recommend Submitting an Enhancement Request to see about having built-in "time tracking" features implemented in a future release.

You may also want to look into our Pro Desk services for coaching on these types of formulas. If you're not familiar with this, Pro Desk delivers practical one-on-one coaching sessions with a Smartsheet expert (30 minutes per session, 10 sessions per year) to accelerate your setup time and help you optimize your projects, programs and processes. A Pro Desk expert will provide coaching, guidance, best practices, and resources for further development.

I hope this helps!

Thanks,

Ben

Paul Newcome

@Ben G Thanks for the tag!

@Brenna Saunders Take a look through this thread HERE. There may be something you can use. I do remember helping someone set something very similar up at some point, but I can't remember if it is in the time thread or not.

If not... Feel free to let me know. I am crazy busy with work at the moment, so it may take a little while to put something together, but it can be done.

If I understand correctly... You basically want to manually enter your start date, start time, and duration and it will automatically calculate your end date and end time?

mukul

Hi @Paul Newcome and Experts,

I am new to the smart sheet and the community, so I need assistance calculating the End time based on the following conditions: Start time + Duration, as shown on this sheet, Duration can be expressed in days, hours or minutes (e.g. 1 day, 1 hour or .1 hour, or 60 minutes), factor 8 working days.

I tried using the logic / formula from this published sheet, giving me the error shown above when adding duration as 1 hour or .1h, or 60 minutes. In addition, it didn't convert to AM or PM based on the duration(perhaps I'm doing some wrong in the formula- not an expert)

https://app.smartsheet.com/b/publish?EQBCT=d861f6ccf0e048b98906495ed74671fc&_ga=2.24685626.1661211734.1652079367-686098908.1647358440

Please paste your formula or publish

I really appreciate your quick response and advice, which will help me out

Regards

Paul Newcome

@mukul You would need to convert your duration into a standard measurement whether that be days, hours, or minutes.

Hours:

=IF(LEFT(Duration@row) = "d", VALUE(SUBSTITUTE(Duration@row, "d", "")) * 24, IF(LEFT(Duration@row) = "m", VALUE(SUBSTITUTE(Duration@row, "m", "")) / 60, VALUE(SUBSTITUTE(Duration@row, "h", ""))))

From there you can use the formulas that will add a number of hours to a time.