Networkdays Error - Dates used in cross sheet references

tariq.shaikh@signspecialists.com

I get an #INVALID DATA TYPE error for this formula: =NETWORKDAYS([Baseline Finish]@row, [Planned Finish]@row).

The Baseline Finish and Planned Finish date columns pull these dates from a different sheet using cross-sheet links.

Any suggestions?

Paul Newcome

Try clicking on the button to remove all formatting and double check conditional formatting rules to make sure it is not left aligned due to that.

You can also insert a temporary flag column and use a column formula such as

=IF(NOT(ISDATE([Date Column]@row)), 1)

This will flag any row that does not contain a date value. If you have a lot of rows in your sheet, you can create a temporary filter to show only those rows that are flagged.

Paul Newcome

Double check that all columns including those in the other sheet are in fact set as date type column. If they are, double check that the data is actually being stared as a date value as opposed to a text value that just looks like a date.

tariq.shaikh@signspecialists.com

Paul Newcome - Thanks. I have checked the cells in all sheets and they are set as Dates. I also validated this by changing the date Format for the cells from the standard "mm/dd/yyyy" to "mmm dd, yyyy" and the dates pick up the new format. I did notice that even though these are set up as date columns, but the dates are left-aligned in the cell, not right!

Paul Newcome

Try clicking on the button to remove all formatting and double check conditional formatting rules to make sure it is not left aligned due to that.

You can also insert a temporary flag column and use a column formula such as

=IF(NOT(ISDATE([Date Column]@row)), 1)

This will flag any row that does not contain a date value. If you have a lot of rows in your sheet, you can create a temporary filter to show only those rows that are flagged.

tariq.shaikh@signspecialists.com

Paul Newcome - Thanks Paul. It appears to have been some sort of a glitch in Smartsheet. I was trying to figure out the issue and added a blank column between the 2 date columns and the error messages went away! Deleted the blank column and the errors didn't re-appear.... So I guess it was a freak phenomenon😲

Thanks for the flag formula!

Paul Newcome

Happy to help. 👍️

I am glad the glitch worked itself out as sometimes those can be a real pain to figure out let alone fix.