Turn average working days to projected date using WORKDAY()
The basic ask is to calculate a projected due date based on historical data and duration that a certain task takes. So if x, y, and z parameters are checked, it will find the average amount of time (from a different sheet) that those parameters take and then calculate a suggested due date.
I want to use WORKDAY(), but am receiving an error message that reads #INVALID DATA TYPE.
So in the column titled [Average Days] I'll have the cell that calculates the average time and outputs a value of working days, let's say 20. In the cell next to it, I type =WORKDAY(TODAY(),[Column A]1) and receive an error message. But when I try to just hardcode it  =WORKDAY(TODAY(),20), I get a projected date. So there must be something wrong with referencing a calculated/formulaed cell? Is there a workaround or has anyone seen anything similar and found a solution?
Thanks!
Comments

Hello,
Happy to help! If you're receiving an #INVALID DATA TYPE with your =WORKDAY(TODAY(),[Column A]1) it's likely because the =AVG being referenced is producing a partial number for example 2.5 days. To correct the WORKDAY formula I recommending do two things.
1. Adding an IFERROR will ensure that if an error is produced, or if the reference is blank, the formula will still work as desired. It could look like this:
=IFERROR(WORKDAY(TODAY(),[Column A]1), " ")
2. You may want to ROUND the average to ensure the average always produces a whole number. For example:
=ROUND(AVG([Column14]16:[Column14]17))
Cheers,
Eric
Smartsheet Support

Ah ha! There was a partial date happening, so I utilized ROUND() and that solved the problem. Thank you!
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63.7K Get Help
 406 Global Discussions
 218 Industry Talk
 456 Announcements
 4.7K Ideas & Feature Requests
 141 Brandfolder
 136 Just for fun
 57 Community Job Board
 459 Show & Tell
 31 Member Spotlight
 1 SmartStories
 297 Events
 37 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!