Determining a Weekday Start Day by Referencing a Calculated Field
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
-
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))
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))
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
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(Stop@row, [Effort (#Work Days)]@row)
If this doesn't work, you may want to contact our Support Team.
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
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.
-
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).
-
I'm all for trying to troubleshoot quickly! Sadly, the =VALUE(LoE@row / 7) formula change didn't result in anything different.
-
Grr. Ok. So let's keep working backwards... What's in the LoE column?
-
It's just a hard keyed numeric value.
-
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))
-
That worked!!! Thank you so so much!
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Thanks, @Genevieve P! I just wish I had that "ah-ha" moment sooner and saved us some frustration.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.7K Get Help
- 405 Global Discussions
- 215 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!