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!
Comments
-
What type of column are you putting the formula in?
-
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.
-
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.
-
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.
-
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!