A date column doesn't recognize a date structure even though I'm using the DATE formula

Sofia Ruano

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 :)

Jeff Reisman

@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))), "")

Sofia Ruano

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 😥

Jeff Reisman

https://community.smartsheet.com/discussion/comment/367108#Comment_367108

Is the Finish column formatted as a date-type 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)))

Sofia Ruano

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:

Jeff Reisman

@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.

Sofia Ruano

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!