#INVALID DATA TYPE error on NETDAYS function

madisonjade94

I am trying to find the number of days between two dates - a due date, and a paid date. I used the formula =NETDAYS([Due Date]@row, [Cleared Date]@row). This worked for a good chunk of the cells, but not others. The remaining cells are returning the #INVALID DATA TYPE error.

I've already ensured both the Due Date row and the Cleared Date row are set under Column Properties as Date.

I have also tried doing the Cleared Date minus the Due Date and receive an error on the same cells - #INVALID OPERATION.

Also, I have already checked that the Cleared Date is AFTER the Due Date, so that shouldn't be the issue. I couldn't find any answers searching the community. Any help on this would be greatly appreciated.

Jeff Reisman

@madisonjade94

How are your Due Date and Cleared Date values populated? Manually through data entry, via a form, or by looking up values from some other sheet?

The #INVALID DATA TYPE is almost always associated with trying to perform a function on a data type that's not compatible with it. In your case, this means that on the rows with errors, one of the date values is actually in there as text. That would also account for the #INVALID OPERATION from trying to subtract a text value from a date value or vice versa.

The reason I ask about how the date values are populated is that if there are different ways the data is getting input, this could cause a value to end up as text. In the properties for your date columns, make sure the "Restrict to dates only" box is checked. When you check that box and hit OK, Smartsheet will tell you how many of your cells in that column contain values that are not dates.

More info on error messages in Smartsheet:

Formula Error Messages | Smartsheet Learning Center

madisonjade94

Hi Jeff,

Thank you for your help. I do have a follow-up question.

I am entering these dates manually, so they are not being extracted from any other sheets or formulas. I went back and did check that box that says "Restrict to Dates Only," and Smartsheet did alert me that there are rows not considered dates and that it would only apply to future items. It does not give me the option to convert to date for the items currently not set to dates. Also, when I hover over the date columns that are associated with the formula yet returning the errors, it does show the calendar icon indicating that it is set to date. What else could I be doing wrong and how can I convert to date when Smartsheet is alerting me they aren't all set that way?

Regards,

Madison

kirstie858

@Jeff Reisman Hi Jeff, I'm also getting the #INVALID DATA TYPE issue. I have two datetime columns that are being populated via DataShuttle (source data: Jira export, csv format).

Created and Resolved Datetime are Date fields. I tried the following formula in TAT1, and get an error:

TAT1 =NETWORKDAYS([Created Datetime]@row, [Resolved Datetime]@row)

I restricted these two columns to Dates, and I don't get a validation error. However, if I put in a column, =IF(ISDATE([Created Datetime]@row), "is date", "isn't date"), "isn't date" is returned for all records.

So then I created two formula columns to grab just the date. Column properties = Date. Again, I get an error in the TAT2 calculation.

Created Date =LEFT([Created Datetime]@row, FIND(" ", [Created Datetime]@row, 1) - 1)

Resolved Date =IFERROR(LEFT([Resolved Datetime]@row, FIND(" ", [Resolved Datetime]@row, 1) - 1), "")

Although these columns are restricted to dates, I don't get any validation errors. Unlike the datetimes, here I can even change the formatting of the dates! Seems like they're real, right? But an =IF(ISDATE()) column tells me they're not.

TAT2 =NETWORKDAYS([Created Date]@row, [Resolved Date]@row)

Lastly, I try copy and pasting the values from Created Date and Resolved Date into Created Date (value) and Resolved Date (value), and finally, TAT3 works. =IFERROR(NETWORKDAYS([Created Date (value)]@row, [Resolved Date (value)]@row), "")

Is there no way to calculate TAT, without having to manipulate the incoming CSV file? This is a major bummer!

Jeff Reisman

@kirstie858 Looks pretty complicated. Date functions are very serious about that date format!

While getting you close, your formulas for creating the date values in Created Date and Resolved Date aren't actually creating date values, but... we can use the DATE function to make them into real date values.

=DATE(VALUE("20" + RIGHT([Created Date]@row, 2)), VALUE(LEFT([Created Date]@row, 2)), VALUE(MID([Created Date]@row, 4, 2)))

In English: Make me a date value where the year is the numeric value of the result of joining "20" with the last two characters in that date-stored-as-text, the month is the numeric value of the two leftmost characters, and the day is the numeric value of the middle two characters.

kirstie858

@Jeff Reisman Thanks Jeff! Your response helped me find the ultimate solution. To complicate matters, when I opened my sheet, the formula I used yesterday was giving a different result! I was actually surprised my =LEFT([Created Datetime]@row, FIND(" ", [Created Datetime]@row, 1) - 1) formula yielded something that looked like a date yesterday (10/21/20) and today it looked like this (21/Oct/20, which makes more sense, but is farther from what I ultimately need). I'm sure a contributing factor was me changing the column properties (data type) of the loaded columns from text to date back to text, and reconfiguring datashuttle as a potential solution (it wasn't!).

After reading your response this morning, the VALUE() clause helped me come up with the ultimate formula. I needed to use a simple external mapping table to convert the MMM value to an integer.

Here's my final conversion formula for Created Date: =DATE(VALUE(20 + MID([Created Datetime]@row, 8, 2)), VALUE(INDEX({map_monthnum}, MATCH(MID([Created Datetime]@row, 4, 3), {map_abbrvmonth}, 0))), VALUE(LEFT([Created Datetime]@row, 2)))

I used a similar formula for Resolved Date, and finally my NETDAYS() formula worked.

Thanks again!!

Jeff Reisman

@kirstie858 Glad you got it to work!

I was wondering how your LEFT formula got you that result, but figured it may have been something about importing from Data Shuttle that changed the format (I don't have Data Shuttle.) Using a remote sheet to look up the month name and convert to month number was going to be my approach as well.