Determining a Weekday Start Day by Referencing a Calculated Field

m_dorsey

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?

Paul Newcome

So you hard key a numerical value in the LoE column.

The [Effort (# Work Days)] column contains

=LoE@row / 7

THis gives you an error:

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

But this does not:

=WORKDAY(Stop@row, 3)

Try this...

=WORKDAY(Stop@row, ROUND([Effort (#Work Days)]@row))

Genevieve P.

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))

m_dorsey

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!

Genevieve P.

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(Stop@row, [Effort (#Work Days)]@row)

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

Paul 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?

m_dorsey

@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 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(LoE@row / 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).

m_dorsey

I'm all for trying to troubleshoot quickly! Sadly, the =VALUE(LoE@row / 7) formula change didn't result in anything different.

Paul Newcome

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

m_dorsey

It's just a hard keyed numeric value.

Paul Newcome

So you hard key a numerical value in the LoE column.

The [Effort (# Work Days)] column contains

=LoE@row / 7

THis gives you an error:

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

But this does not:

=WORKDAY(Stop@row, 3)

Try this...

=WORKDAY(Stop@row, ROUND([Effort (#Work Days)]@row))

m_dorsey

That worked!!! Thank you so so much!

Paul 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.

Genevieve P.

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

Paul Newcome

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