Using Auto-Generated Fields in Formulas Successfully

Hannah Johnson

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! 

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.