Determining a Weekday Start Day by Referencing a Calculated Field

04/15/20
Accepted

Hello,

I'm trying to determine when to start a project based on the due date and the effort. The effort exists in hours and I've converted that into days based on a 7 hour day (padding days to allow for admin/other work in a day). This is my WORKDAY formula:

=WORKDAY(Stop109, [Effort (#Work Days)]109)

However, when I try to reference the "# Work Days" calculation in my WORKDAY formula, I receive the "INVALID DATA TYPE" error. If I hard key that value in the formula, the formula works. I have over 300 project line items that have varying efforts, so I need my formula to work with a reference to effort.

Any ideas/workarounds/etc?

Best Answer

Answers

  • Hi @m_dorsey

    How are the [Effort (#Work Days)] column values being returned? Is it possible that they are somehow input as text (left-aligned in the cell) instead of numerical (which would appear as right-aligned)?

    To test this, you could wrap the column in a VALUE function to make sure the number is read as a number. Try this:

    =WORKDAY(Stop109, VALUE([Effort (#Work Days)]109))

  • Hi @Genevieve P - My [Effort (#Work Days)] column values are being returned as values (right aligned), but I did go ahead and try your formula. Unfortunately, I still receive the same error. It's so confusing bc if I hard key the result of that calculation, it works. That's just not realistic for constantly moving values based on date changes across hundreds of line items. I appreciate you trying to help and open to trying other things!

  • Hmm that is strange... I'm not able to duplicate this.

    It shouldn't matter, but I wonder if the formula is getting confused with the parenthesis in your column name, since parenthesis are used for different actions in formulas. Could you adjust the column name to be something else, perhaps "Effort - Number of Work Days"?

    Could you also replace the row references (in this case 109) with @row instead? The sheet will be able to calculate a bit faster without needing to search through the row numbers:

    =WORKDAY([email protected], [Effort (#Work Days)]@row)

    If this doesn't work, you may want to contact our Support Team.

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    How are the values in [Effort (#Work Days)] being populated? Is it by manual entry or a formula? If it is a formula, can you copy/paste the exact formula from the sheet to here?

  • @Genevieve P - thanks for the recs re: @row and removing the parentheses in the column name. I gave them a whirl but it didn't solve the issue.

    @Paul Newcome - [Effort (# Work Days)] is calculating the number of days based on an estimate of hours (LoE). So that field's value is "=LoE109 / 7". I also tried changing that formula to @row but it didn't solve the issue.

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    That's odd. Your formula should be returning a numerical value, but it is as if the WORKDAY function is registering it as text as Genevieve had previously mentioned. What happens if you adjust that formula like so...

    =VALUE([email protected] / 7)


    I am also going to suggest reaching out to Support, but maybe we can get it figured out with some further troubleshooting a little more quickly (maybe).

  • I'm all for trying to troubleshoot quickly! Sadly, the =VALUE([email protected] / 7) formula change didn't result in anything different.

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Grr. Ok. So let's keep working backwards... What's in the LoE column?

  • It's just a hard keyed numeric value.

  • That worked!!! Thank you so so much!

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Excellent!!


    When you are dividing by 7 you are most likely creating some decimals. Using a ROUND function removes those decimals and provides a whole number for the WORKDAY function. The INT function would have worked the same way.


    I'm sorry I didn't think of that sooner after staring at the "divide by seven" bit for so long.

  • Just came back to this thread - so happy to see that you were able to resolve it! Great tip, @Paul Newcome

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Thanks, @Genevieve P! I just wish I had that "ah-ha" moment sooner and saved us some frustration.

Sign In or Register to comment.