Using Auto-Generated Fields in Formulas Successfully

I have a sheet which tracks all a department's projects. We are making improvements which required us to create a new version of it by copying from a previous version (save as new) which meant that the auto-generated "created" date all the sudden became the date I made the new file, rather than the date the actual project was entered. I have created a "parallel" column next to it and pasted the original data in, leaving me with two columns of dates, one of which is auto-generated.

I need a formula which creates in a new column the right date - e.g. IF there is a date in the "Actual" (e.g. pasted from the old version) column, display that date. If there isn't a date, then pull from the auto "created" date column.

I keep getting an "Invalid Column Value" error. How can I get around this and pull the "right" date for each project?

Here's what I have come to thus far...

=IF(ISBLANK([Actual Creation Date]1), Created1, [Actual Creation Date]1)

This is from the old doc new doc old doc

Thanks!

Find more posts tagged with

Formulas

Project Management

Comments

Paul Newcome

What type of column are you putting the formula in?

Hannah Johnson

The formula is going in a Text/Number column. The two columns it references are 1) text/number column, and 2) auto number/system generated column.

Paul Newcome

My Smartsheet is down at the moment, so I am unable to do some testing. Try changing the target column to a date type if your's is up. Otherwise I will do some testing once mine comes back up.

Hannah Johnson

Thanks, Paul. I think it's a Smartsheet block on using an auto-generated date field in a formula. I can't even do a basic =Column Name formula - if i simply wanted to duplicate the contents of that cell. I can do that with autogenerated email address columns... but not date.

Paul Newcome

If all you need is the date, you can switch it to a date type column and use a DATEONLY function.

If you need the timestamp as well, you can "trick it" by using a LEFT function and the a LEN function in a text/number column.

=LEFT(Created@row, LEN(Created@row)

Will essentially duplicate the date and time stamp as a text string which means it won't throw the error (tested as true).

The only catch is that a DATEONLY function or any other date type functions will not work on the date portion of this because it is being stored as text instead of date/time.

Sign in to join the conversation!

Quick Links

Trending discussions

Is there a way to create a 3-digit padding for numbers
Hi all, and thanks in advance! I wanted to know if there was any way to create "padding" in Smartsheet. Basically I have account numbers, and a handful are still 2-digit, so they appear as TDL-17, TDL-67, etc. Most of the accounts are 3-digit numbers like TDL-180. I want something that will recognize whether it is 2 or 3…
Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?