Automatic Finish date and Finish time columns for a 3D Printer Sheet

Angelos S.

Hello everyone,

I am creating a sheet for my 3D Printer and I need some help as I am new in Smartsheet and in using functions.

I want to make the "Finish Date" and "Finish Time" automatic, meaning once I input the Start date, start time and duration, the Finish date and finish time will automatically show when my 3d print will be done.

1) As seen below, I want to put a print on 22 MAR 2022, start at 13:27 and its duration will be 1 day 05 hrs and 23 minutes.

I know that the result should be: Finish Date - 23/03/22 and Finish Time - 18:50.

Is there a function to do that? Start Date and Finish Date are Date columns, and the rest are just Text/Number columns.

It is easier for me to have the times in a "XX:YY" format which is tricky as I can't make the sheet understand what (13:27) means and that if you add 5 hrs and 23 minutes it should change to 18:27 (forgetting the extra day added for this example)

Example if I use "Finish Time = [Start Time]@row + [Duration]@row" function, which obviously does not work as I want:

2) In another attempt of this sheet, I managed to make the start date and finish date work automatically by using an "IF" function that would change the date, if the finish time was more than a day.

• To Explain the sheet, the user has to:
• Set the date in the "Date" column
• Input the "Start time" in hours, meaning 10am is 10, 4 pm is 16, 1:30 pm is 13.5 etc, and
• Input the "Duration" in hours
• Notes:
  • The "Finish Date" is equal to the "Finish date Calc" column which is explained below.
  • The "Finish date Calc" would be hidden so the calculation and formulas would not confuse the user.

The sheet calculates the Finish time by adding the Start time and Duration:

Finish time Calc = [Start time (in hours)]@row + [Duration (in hours)]@row

Then the Finish date is calculated in the "Finish date Calc", by using the formula below:

=IF([Finish time Calc]@row > 24, IF([Finish time Calc]@row > 48, IF([Finish time Calc]@row > 72, IF([Finish time Calc]@row > 96, IF([Finish time Calc]@row > 120, IF([Finish time Calc]@row > 144, IF([Finish time Calc]@row > 168, IF([Finish time Calc]@row > 192, IF([Finish time Calc]@row > 216, IF([Finish time Calc]@row > 240, IF([Finish time Calc]@row > 264, Date@row + 11, Date@row + 10), Date@row + 9), Date@row + 8), Date@row + 7), Date@row + 6), Date@row + 5), Date@row + 4), Date@row + 3), Date@row + 2), Date@row + 1), Date@row)

So, if the finish time is more than 24, so 24 hours, the finish date would be 1 day later (from 01Mar to 02Mar). If the finish time is more than 72, that is translated to 3 days, so 3 days later ( 1Mar to 04Mar), and so on up to 11 days later, so if the finish time would be more than 264 hours long. If it would take longer, then you would not get a correct day output but I doubt that in my situation more than 11 days would be needed. It is a sheet for a 3D Printer after all and this time period is TOOO much.

To sum up, I want a smartsheet that automatically calculates the "Finish date" and "Finish time" for me, once I input the Date, Start time and Duration of a 3D Print. The examples I provided are to help you understand my thought process and how I think it would work, although I came to a hard stop due to lack of experience and knowledge.

I hope what I am asking for makes sense, if not, please let me know and I will try to explain. Automation using a workflow is also welcome if functions are not useful. Any support and suggestion is welcome, including criticism :).

I have more things to ask for this sheet, but I will do once we can solve this issues first.

Thank you in advance for your help!

Kind regards,

Angelos S.

Paul Newcome

Yes. There are a number of other solutions in the comments as well.

Paul Newcome

Take a dig through this thread. We are up to 10 pages worth of comments now, but there should be something in here you can use:

https://community.smartsheet.com/discussion/68947/formulas-for-calculating-time

Angelos S.

@Paul Newcome Thank you for sending me the link. I found this thread yesterday and read it through before making my post but I did not find something that solves my question in the 7 links you provide. I will have another look, maybe I missed something. I did not check the comments but I will.

Hopefully I will find something useful.

Thank you again,

Angelos

Paul Newcome

Yes. There are a number of other solutions in the comments as well.