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

Thanks!

Find more posts tagged with

Construction

Sheets

Formulas

Support

Solution Center

Comments

eric.o

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

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

Sign in to join the conversation!

Quick Links

Trending discussions

Lookup Value Issues
I am trying to populate a column from Sheet A into Sheet B and Sheet C. My Lookup column for Sheet A is formatted as such so there is a ' in front to account for ones that have a leading 0 and some do not. Each ID should be 6 digits long. I made a helper column looking at this Dealer ID column to try and get rid of that…
Separating Data into Multiple Rows
am I able to separate this into multiple rows? in a new sheet? Desired Result -
Index Distinct Support
I would like to parse out this data by creating a listing with a row for each person, for each project they appear on, for each month. I think this would only give me the groupings anyways, but this doesnt seem to even get me started - it popped up then people have disappeared.