Networkdays Error - Dates used in cross sheet references
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?
Best Answer
-
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.
Answers
-
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.
-
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!
-
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 - 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!
-
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!