Duration and Date closed columns auto populate when Date initiated column is filled in

vivianwu1

I have "Date Initiated", "Date Closed" and "Turnover Time" columns. The initiated and closed columns will (should) be populated when they happen, but the closed column auto populates to the same date as the date initiated column when it gets filled in.

I also had to create a "Predecessor" column which I hid because I don't need that info, but it was needed to have a formula/dependency to calculate the Turnover Time from the Initiated and Closed dates. I'm using the dependency feature instead of formula (Date Closed minus Date Initiated) because I want to count only 6 out of 7 days of the week (not counting Sundays).

How can I make it that the Date Closed and Turnover Time columns don't auto-populate?

Georgie

Hi @vivianwu1,

It sounds like you have dependencies enabled on your sheet and your “Turnover time” column is used as the Duration column, and your “Date Closed” column is used as the End date column. When dependencies are enabled, adjustments in any two of a row’s  Start Date, End Date, or Duration columns automatically calculate the third value. You can find out more about dependencies here:

• Activate dependencies and use predecessors in sheets

You could simply deactivate dependencies by unchecking the Dependencies enabled box in the project settings.

However, if you’d like to keep dependencies enabled, you could create new columns and set these as the Duration and End Date columns in the sheet’s project settings (right click any column header > Edit Project Settings) to ensure that your “Turnover Time” and “Date Closed” columns aren’t automatically populated - you could then hide those new columns if they’re not required. 

The screenshot below shows how your project settings might look currently - you can select the dropdown arrow underneath the relevant columns to select your newly created columns.

Hope that helps!

Georgie

vivianwu1

Hi Georgie,

I'm confused on this part of your response:

However, if you’d like to keep dependencies enabled, you could create new columns and set these as the Duration and End Date columns in the sheet’s project settings (right click any column header > Edit Project Settings) to ensure that your “Turnover Time” and “Date Closed” columns aren’t automatically populated - you could then hide those new columns if they’re not required. 

I do have my project settings the way you set them up in your screenshot. I don't understand how to create and hide new columns for Duration and End Date and have the dependencies on those columns. Would I need the dependency set up to the unhidden TAT/duration and End Date columns for them to have auto-calculations?

Thanks!

Georgie

Hi @vivianwu1,

Perhaps I’ve misunderstood your query. It sounds to me like you want the Turnover Time and Date Closed columns not to be auto-populated when the Date Initiated column is populated. 

I was therefore suggesting that you could create new columns (eg a Date column called “End Date” and a text/number column called “Duration”), then select these in the Project Settings as the columns used for the End Date and Duration - this will mean that those columns are auto-populated when the Date Initiated column is populated/updated when it's updated. You could then hide those columns so that you don’t see them (they will still populate/update, you just won’t see them unless you unhide them).

However, as an alternative, you can change the duration in the Turnover Time column to change the date in the Date Closed column for the same row, or you can change the date in the Date Closed column which will change the value in the Turnover Time column for the same row.

Does that make sense? Happy to help with any further questions!

Georgie

vivianwu1

Hi Georgie,

I believe I created the columns to be hidden and changed the dependencies correctly, but the end date and duration didn't auto populate like I wanted.

Would I be able to not have any dependency between the "Date Initiated" with "End Date"/"Duration"? So could "Date Initiated" be added, then whenever the end date is, the "End Date" will be populated and the "Duration" will auto-populate?

Georgie

Hi @vivianwu1,

Thanks for the clarification - I see that I wasn’t quite following what you were trying to achieve! So, to clarify, you want to be able to:

• Manually input values in the start date (Date Initiated) and end date (Date Closed) columns.
• Have the duration auto-populate in a row when both those columns are filled in.
• Set Monday through Saturday as working days.

In order for formulas to take note of your custom working days, we’ll need to keep dependencies enabled, but I’d recommend not actually using the columns that are linked with the project settings and hiding these so that you can use your own columns. In full, the steps I’d take are as follows: 

1. Set your working days in the Project Settings:
   
2. Do not use any of the columns in the Project Settings - hide all 4 of these in your sheet. In the screenshot above, you can see that I’ve added “(HIDE)” in the names of these columns and given them a different colour background so that it’s clear which ones we won’t be using and need to hide.
3. Depending on your requirements, either create an automated workflow to fill in your “Date Closed” column based on criteria, or manually fill in these dates as required.
4. In the column you’ll be using as your Duration column (Turnover Time), create a NETWORKDAYS formula and convert it to a column formula so that the column auto-populates once there’s a value in the Date Initiated and Date Closed columns for a row. The NETWORKDAYS function will use the working days you’ve set in your Project Settings. You could use this formula, for example: =IF([Date Closed]@row = “”, “”, NETWORKDAYS([Date Initiated]@row, [Date Closed]@row))
   

This solution means that you won’t be able to use the Gantt chart, however, since you won’t be using the columns that are linked to your project settings. 

Does that work for you? If not, could you include some screenshots showing your sheet’s setup so that I can be sure I understand what’s not working?

Thanks,

Georgie

vivianwu1

Hi Georgie,

I copied your instructions into my smartsheet (grey columns are the ones I hid), and the columns I used in the formula are to the left of the grey columns.

My formula is =IF([Date Incident Closed]@row = “”, “”, NETWORKDAYS([Date Incident Initiated]@row, [Date Incident Closed]@row))

However, it gives me the #UNPARSEABLE error and I'm not sure why. Here are my project settings:

Thank you, Georgie!!

Genevieve P.

Hi @vivianwu1

Just wanted to jump in here to let you know it looks like the quotes in this formula are italicized, versus straight.

This: “”

But it should be this: ""

Instead of copy/pasting, try re-typing it directly in the Smartsheet cell, this will type in the correct quotes! Or try this version instead:

=IF([Date Incident Closed]@row = "", "", NETWORKDAYS([Date Incident Initiated]@row, [Date Incident Closed]@row))

Cheers,
Genevieve

vivianwu1

Thank you Genevieve!