How to add column that shows start/finish time in minutes?

Andrée Starå

@Paul Newcome

https://media.giphy.com/media/1117SnVlkwL5FS/giphy.gif

Patrick D

I figured out a method to do this although it's pretty cumbersome:

1) I created the following columns in my Cutover Tasks sheet: Start Time (Date column), Finish Time (Date column), UKID (auto-number column), and a RowID column (text column).

2) In the first row of the RowID column I inserted the cell formula =COUNTIFS(UKID$1:UKID@row, OR(@cell = "", @cell <> "")) . Copy this all the way down your plan as a cell formula. It won't work as a column formula.

2) I then created a copy of the Cutover Tasks sheet and named it Cutover Tasks2, and deleted all the cell/row content out except the UKID and RowID values.

3) Then in the Cutover Tasks2 sheet, only for the columns I needed the data (Task Name, Start Time & Finish Time), I inserted a JOIN COLLECT column formula for the Task Name, Start Time and Finish Time columns.. =JOIN(COLLECT({Cutover Tasks | Task Name}, {Cutover Tasks | RowID}, =RowID@row)) and =JOIN(COLLECT({Cutover Tasks | Start Date}, {Cutover Tasks | RowID}, =RowID@row)) and =JOIN(COLLECT(Cutover Tasks | {Finish Date}, {Cutover Tasks | RowID}, =RowID@row)) . You will see that this populates not just the date but the time also.

4) In the Cutover Tasks sheet I then insert JOIN COLLECT formulas in the Start Time and Finish Time columns bringing them over from the Cutover Tasks2 sheet. =JOIN(COLLECT({Cutover Tasks2 | Start Time}, {Cutover Tasks2 | RowID}, =RowID@row)) and =JOIN(COLLECT({Cutover Tasks2 | Finish Time}, {Cutover Tasks2 | RowID}, =RowID@row)) . This will populate the date & time in these columns.

5) As long as all of your tasks have a predecessor and a duration, Smartsheet will calculate the times correctly, down to the minute. The first task in the plan will have the Start Time of 8am so you may need to add a dummy task as the first task if you don't want your first real task to start at 8am.

6) You will also want to check your Length of Day in Edit Project Settings because Smartsheet takes this into account when scheduling Successor tasks.

SJ Sellers

You can make your RowID column a Column Formula thusly:

=MATCH(UKID@row, UKID:UKID,0)

You can also show the time right on the original sheet - no need to create a second sheet.

I create 4 columns: Start Date, Start Time, End Date, End Time (but the Start Time and End Time can easily be modified to show both the date and time)

I use the Start and End Date columns as normal, and in the Start and End Time columns, I use this formula:

=RIGHT([Start Date]@row + " ", LEN([Start Date]@row + " ") - 9)

It looks like this:

The RowID is not needed here, but I included it in case you need it for other purposes. I intentionally reordered some of the rows by dragging them to illustrate how the formula works.

The hard part about this approach is we have a number of tasks that should not begin at 8AM, and so the lack of ability to actually specify the start time without creating many, many "Dummy" tasks becomes problematic. Also explaining to collaborators (who are generally more casual users of Smartsheet) how to create a dummy task is challenging.

barryrowland

Any solution for this 2+ years later? I want to create training events through the course of a day and show them on an Hour level Gantt chart and Daily calendar view. Eg Training 1 start 12/12 0800 to end 0930 and Training 2 12/12 1000 to end 1215.

celtics345

Chiming in another year later... still waiting on Smartsheet to figure out the seemingly-impossible task of hourly entry and Gantt view.

Gopi Kartheesan

I used the below formula from the previous chat and able to see the time based on the Start and End Date. Created 2 new columns for Start and End time and copied the below formula for the respective Start and End Time column

Start Time : =RIGHT([Start Date]@row + " ", LEN([Start Date]@row + " ") - 0)

End Time : =RIGHT([End Date]@row + " ", LEN([End Date]@row + " ") - 0)

See screenshot below

Smartsheet_Start_End_Time.png