Turn average working days to projected date using WORKDAY()

Henry.Busch · 02/07/19

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/formula-ed cell? Is there a workaround or has anyone seen anything similar and found a solution?

Thanks!

eric.o · 02/19/19

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

Henry.Busch · 03/07/19

Ah ha! There was a partial date happening, so I utilized ROUND() and that solved the problem. Thank you!

Turn average working days to projected date using WORKDAY()

Comments

Help Article Resources

Categories