Date@row not working properly

rphelps

Hello,

We had a consultant create formulas and sheets for us to combine like-information in multiple sheets. However, one formula that she set up is not grabbing the correct information. We no longer have access to this consultant. In the first picture below you'll see the formula = Date@row + "" in row 13. The same formula is in row 12. These are supposed to grab the date 5/17/2023 and 1/11/2023 from rows 13 and 12. They then populate in a different sheet (second picture) in row 18 in the blue bar. However, as you can see, the dates that actually show in the blue bar are the dates from rows 18 and 21(not pictured), not the start and end dates the formula is saying it's capturing.

When I go to change it manually in the second sheet (with the blue bar) to the 1/11/2023 start date, it shows the error in the third picture.

How can I fix this formula or is there something else I need to do to make the correct date show in the blue bar? Thank you in advance!

Paul Newcome

Is it a formula pulling it in to the sheet with the blue bar, or is it a cell link?

MichaelTCA

Hello @rphelps

Here's what I assume is happening.

First the function =Date@row+"" doesn't make sense to me. The syntax +"" is considered a string or text in Smartsheet. Try removing the +"" part of the function.

Second, the schedule looks like it starts with session 1 and ends with session 4, but there is nothing scheduled prior to or after the sessions. So the schedule won't consider it a task to be entered. Until there is a event to be scheduled, like a session, you won't see the beginning and end dates. Currently, it's used as a summary reference and not actually part of the schedule you wish to refer to, because it's not a task.

You could make a Pre-Session and Post-Session event that contains the start and end date and you should get the results you are looking for. OR use the actual beginning and end dates for session 1 and session 4.

Paul Newcome

@MichaelTCA I use the +"" method pretty frequently when I need to display a date in a text/number type column. Oftentimes it is within a Control Center build. Removing that from the screenshot above will most likely throw an "Invalid Column Value" error.

The issue is how it is being pulled into the second sheet and not necessarily the +"" formula piece.

rphelps

@Paul Newcome -The dates next to the Start and End date cells are linked in from our main intake sheet, then linked out to a holding sheet, but I'm seeing it's not linked out to the task sheet where I need it (the second picture with the blue bar). The session dates are showing as linked in from the main intake sheet, the holding sheet, and the task sheet with the blue bar. So maybe the start and end dates also need to be linked out to the task sheet also? When I click the cell in the blue bar, there is no formula or linked in or reference that I can see pop up indicating why or how that date populates there.

rphelps

@MichaelTCA The actual start and end dates are different than the session dates. The start and end are the participant access to our training course and the due date of their final assessment so I cannot use those dates. The date in the blue bar shows the date of the first training, but it needs to show the date of the access to the course. I'm also fine with just deleting those dates in the bar all together and putting the start and end dates elsewhere, but it won't even let me delete it due to the error I receive. I could make a new row in the metadata for the start date and end date in the list of sessions and have the dates show there? But again- how do I delete the info in the blue bar then?

Taking out the +"" did cause an error.

Paul Newcome

The data in the blue bar is automatically populated by the dependency settings. Try inserting a new "session" immediately under the blue bar and calling it "Participant Access" and another new session at the bottom of the list called "Final Assessment".

Then you can create cell links as appropriate to bring in the start and finish dates that you need from the source sheet which should then automatically push through into the blue bar.

If you are not really using the dependency settings for anything, you can turn them off and just create the cell links directly in the blue bar row.

rphelps

@Paul Newcome Thank you! I will try that. How do I turn off dependency settings?

Paul Newcome

Switch to Gantt view, click on the settings cog in the top right corner, then uncheck the box for dependencies.

rphelps

@Paul Newcome Thanks, but I need that apparently to keep Resource Management options.

Thank you for your all of your help! I created new rows for the dates and linked them in. I'll continue to play with it until I get that dependency date correct.

Paul Newcome

Happy to help. 👍️