A date column doesn't recognize a date structure even though I'm using the DATE formula
Hi all! I've been truggling with this formula since the last couple of days, and I can't seem to find the error.
I need the "Finish" column to contain the date from "Column 4". For this, I'm using a helper column ("Finish I"), that extracts the date from Column 4 and converts it to a standard format. Then, with the combination of DATE and VALUE formulas, I'm bringing the data from Finish I to "Finish" column. But it doesn't recognize it as a date so it throws the Data type error.
Any ideas on how to solve this?
Find attached the pictures of the columns and the formulas. In case the DATE formula is not clear in the picture, here it is: =DATE(VALUE(20 + LEFT([Finish I]@row, 2)), VALUE(MID([Finish I]@row, 4, 2)), VALUE(RIGHT([Finish I]@row, 2)))
Thank you :)
Answers

@Sofia Ruano It looks like it's working just fine on the rows where there are values in Column 4 and Finish I. The error shown in your screenshots  #INVALID DATA TYPE  only appears on the rows with no values. This is because the formula in the Finish column is trying to use the DATE and VALUE functions against blank spaces.
To suppress the error message, you can either:
Use IFERROR to replace the error message with a blank:
=IFERROR(DATE(VALUE(20 + LEFT([Finish I]@row, 2)), VALUE(MID([Finish I]@row, 4, 2)), VALUE(RIGHT([Finish I]@row, 2))), "")
or;
Use IF to tell the system to only run the formula if the Finish I column is not blank:
=IF([Finish I]@row <>"", DATE(VALUE(20 + LEFT([Finish I]@row, 2)), VALUE(MID([Finish I]@row, 4, 2)), VALUE(RIGHT([Finish I]@row, 2))), "")
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Hello Jeff! Thanks so much for your super fast answer!
Actually I tried the IFERROR function, but my main problem is the dates that show up in the Finish column, are not recognized as dates by Smartsheets. I have no problem with the blank columns showing an error, but I can't set a gantt view because the dates that are actually showing up are not dates for Smartsheets 😥

Is the Finish column formatted as a datetype column?
Also, you could skip the Finish I column  just use the same type of formula as you're using in Finish now:
=DATE(VALUE(LEFT([Column4]@row, 4)), VALUE(MID([Column4]@row, 6, 2)), VALUE(MID([Column4]@row, 8, 2)))
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Yes it is! I formatted like a date column, but the text you see (e.g. 3/16/23) is not recognized as a date. I just added the "IFERROR" before the formula so the error doesn't show up, but when I try to set the view to Gantt, I get this message:

@Sofia Ruano Ok, that's a different problem... We'll get to that in a second...
First, the date values in the Finish column should definitely be dates and be recognized as dates.
You can test this in a helper column: =IF(ISDATE(Finish@row), "YES", "NO")
As far as the Gantt view goes, I found this:
A sheet won't recognize date columns with a Column Formula applied as columns for a Gantt chart. If you need your dates to autofill rows, use the Cell Formula and Autofill features instead. Another option would be to set up column formulas. Then, use a report to display your Gantt view.
I haven't worked much with project sheets or Gantt view, but it seems like this may be your issue.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Thank you so much Jeff!! This was super helpful.
I tested the finish column, and the data is a date indeed. So the problem is with the gantt chart, that I can solve by using a report.
Thank you so much!
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.1K Get Help
 351 Global Discussions
 198 Industry Talk
 427 Announcements
 4.4K Ideas & Feature Requests
 133 Brandfolder
 127 Just for fun
 127 Community Job Board
 443 Show & Tell
 28 Member Spotlight
 1 SmartStories
 283 Events
 36 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!