Formula Help Within Sheet
Alright, SmartSheet community. You've always been good to me with helping out. I've got a real "stumper" here.
假设我有六列在我的表(reality there's many, many more; but these are the ones relevant to my question):
- Start Date (a Date/Time column type)
- End Date (a Date/Time column type)
- Allocation % (a Text/Number column date that is in a % format, like 70%)
- February 2023 Hours Scheduled (a formula driven cell)
- March 2023 Hours Scheduled (a formula driven cell)
- April 2023 Hours Scheduled (a formula driven cell)
Now, let's assume that the Start Date is set to 02/01/2023 and the End Date is set to 04/30/2023. I need a formula in Column 4 (February 2023 Hours Scheduled) that calculates the number of working days in February (19) within the Start Date and End Date multiplied by the Allocation % cell and then multiplied by 8.
This will give me the number of hours I need to devote in February for an employee who is working 70% of their time towards this line item.
I'd need to be able to then copy/adjust this formula for the March 2023 column and then again to the April 2023 column.
Alright, please do your thing. Many thanks.
Best Answer
-
Mike TV ✭✭✭✭✭✭
Try this:
=IFERROR(NETWORKDAYS(IF([Start Date]@row < DATE(2023, 2, 1), DATE(2023, 2, 1), IF([Start Date]@row > DATE(2023, 2, 28), "", [Start Date]@row)), IF([End Date]@row < DATE(2023, 2, 1), "", IF([End Date]@row < DATE(2023, 2, 28), [End Date]@row, DATE(2023, 2, 28)))) * [Allocation %]@row * 8, "No Feb 2023 Work Days")
Answers
-
Paul Newcome ✭✭✭✭✭✭Will your start and end dates always be Feb - Mar? Are you only calculating for Feb - Mar, or does this expand to more month columns? If there are more month columns, do you need to be able to account for going into another year?
-
Nicole J ✭✭✭✭
Thanks, Paul. No, the February through April dates I gave in my example are just an example. The real life sheet will have dates spanning all months and into future years (there are columns for "February 2023, March 2023, April 2023, May 2023, through many years". So, perhaps the formula will have to designate both Month and Year. I hope this helps. Thanks for looking into this.
-
Paul Newcome ✭✭✭✭✭✭
Ok. There are a number of other posts here in the Community that have the base already built out where we spread the number of days across multiple columns. I remember helping out on a few, so we just need to find one and then tweak it. If you happen to find one of those posts before I do, feel free to drop a link here and we can work it from there.
-
Nicole J ✭✭✭✭
Hello, Paul (or anyone else),
I'm writing to follow-up on this thread. I've searched through the history of similar issues, and I found a thread from April 2022 (thread titled "Formula for Hours Scheduled By Month") and came across the below formula. When applied, it's not quite there yet (giving me error messages). I'm hoping this maybe jogs your memory and you can help modify the below formula to my issues. Thanks.
=IF(MONTH([Start Date]@row) = 3, NETWORKDAYS([Start Date]@row,IFERROR(DATE(YEAR([Start Date]@row), MONTH([Start Date]@row) + 1, 1), DATE(YEAR([Start Date]@row) + 1, 1, 1)) - 1) + " days & " + (NETWORKDAYS([Start Date]@row,IFERROR(DATE(YEAR([Start Date]@row), MONTH([Start Date]@row) + 1, 1), DATE(YEAR([Start Date]@row) + 1, 1, 1)) - 1) * [Hours per Day]@row) + " hours", IF(MONTH([End Date]@row) = 3, NETWORKDAYS(DATE(2022, 03, 01), [End Date]@row) + " days & " + (NETWORKDAYS(DATE(2022, 03, 01), [End Date]@row) * [Hours per Day]@row) + " hours", IF(AND([Start Date]@row < DATE(2022, 03, 01), [End Date]@row >IFERROR(DATE(YEAR([Start Date]@row), MONTH([Start Date]@row) + 1, 1), DATE(YEAR([Start Date]@row) + 1, 1, 1)) - 1), NETWORKDAYS(DATE(2022, 03, 01),IFERROR(DATE(YEAR([Start Date]@row), MONTH([Start Date]@row) + 1, 1), DATE(YEAR([Start Date]@row) + 1, 1, 1)) - 1) + " days & " + (NETWORKDAYS(DATE(2022, 03, 01),IFERROR(DATE(YEAR([Start Date]@row), MONTH([Start Date]@row) + 1, 1), DATE(YEAR([Start Date]@row) + 1, 1, 1)) - 1) * [Hours per Day]@row) + " hours")))
-
Mike TV ✭✭✭✭✭✭
Does this work?
=IFERROR(NETWORKDAYS(IF([Start Date]@row < DATE(2023, 2, 1), DATE(2023, 2, 1), IF([Start Date]@row > DATE(2023, 2, 28), "", [Start Date]@row)), DATE(2023, 2, 28)), "No Feb 2023 Work Days") * [Allocation %]@row * 8
-
Mike TV ✭✭✭✭✭✭
I forgot I changed something which broke the IFERROR. This is what I was going for:
=IFERROR(NETWORKDAYS(IF([Start Date]@row < DATE(2023, 2, 1), DATE(2023, 2, 1), IF([Start Date]@row > DATE(2023, 2, 28), "", [Start Date]@row)), DATE(2023, 2, 28)) * [Allocation %]@row * 8, "No Feb 2023 Work Days")
-
Nicole J ✭✭✭✭
Thanks, Mike. Right now, that formula works when placed in a row that has February dates. But when placed in a cell without February dates falling between the "Start Date" and "End Date", I'm getting the same total (where hopefully, I'd get the "No Feb 2023 Work Days" answer).
When Start Date is 01/30/2023 and End Date is 03/31/2023 and Allocation % is 70%, I'm getting the correct 112 hours for February.
But, on a different row, the Start Date is 09/26/2022 and End Date is 01/27/2023 (therefore zero February working days) and Allocation % is 70%, I'm still getting the 112 hours for February, but I think I should be getting "No Feb 2023 Work Days".
-
Mike TV ✭✭✭✭✭✭
You're welcome. It looked to work for me when I changed dates around. Now that I think of it though, I didn't add something to account for the End Date if it were say 02/23/2023 or something. That could be added in to the formula though without too much effort.
I believe this might account for that solution:
=IFERROR(NETWORKDAYS(IF([Start Date]@row < DATE(2023, 2, 1), DATE(2023, 2, 1), IF([Start Date]@row > DATE(2023, 2, 28), "", [Start Date]@row)), IF([End Date]@row < DATE(2023, 2, 28), [End Date]@row, DATE(2023, 2, 28))) * [Allocation %]@row * 8, "No Feb 2023 Work Days")
-
Nicole J ✭✭✭✭
Mike, we're super close. Thank you. I think it's not accounting for start and end dates that are outside of February 2023 (or perhaps even further in the past). For instance, I'm getting an answer of -22.4 when the start and end dates are 09/26/2022 and 01/27/2023.
-
Mike TV ✭✭✭✭✭✭
Try this:
=IFERROR(NETWORKDAYS(IF([Start Date]@row < DATE(2023, 2, 1), DATE(2023, 2, 1), IF([Start Date]@row > DATE(2023, 2, 28), "", [Start Date]@row)), IF([End Date]@row < DATE(2023, 2, 1), "", IF([End Date]@row < DATE(2023, 2, 28), [End Date]@row, DATE(2023, 2, 28)))) * [Allocation %]@row * 8, "No Feb 2023 Work Days")
-
Nicole J ✭✭✭✭
Bingo. Many, many thanks Mike.
Help Article Resources
Categories
Check out theFormula Handbook template!
Instead of applying the formula to \"Multiselect Text String\" row, did you tried with \"Multiselect Values\" row?<\/p>
=IF(HAS([Multiselect Values]@row, [Component ID]@row), \"MATCH\", \"NO MATCH\")<\/p>
Thank you,<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":109493,"type":"question","name":"I am having trouble using \"And\", \"OR\" & \"Countif(s)\" to build a formula.","excerpt":"Hello, I am attempting to come up with a sheet summary formula that counts cells if they meet at least one of 3 different statuses in the same column, AND also meet one of 5 different statuses in a separate column. So using the screenshot I've provided as an example (although it doesn't have 5 different statuses in the…","snippet":"Hello, I am attempting to come up with a sheet summary formula that counts cells if they meet at least one of 3 different statuses in the same column, AND also meet one of 5…","categoryID":322,"dateInserted":"2023-08-25T20:03:21+00:00","dateUpdated":null,"dateLastComment":"2023-08-26T00:34:49+00:00","insertUserID":165710,"insertUser":{"userID":165710,"name":"SmarsheetNewb","url":"https:\/\/community.smartsheet.com\/profile\/SmarsheetNewb","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-26T00:33:27+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":161714,"lastUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-08-27T02:16:35+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":26,"score":null,"hot":3386005690,"url":"https:\/\/community.smartsheet.com\/discussion\/109493\/i-am-having-trouble-using-and-or-countif-s-to-build-a-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109493\/i-am-having-trouble-using-and-or-countif-s-to-build-a-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":109493,"commentID":392692,"name":"Re: I am having trouble using \"And\", \"OR\" & \"Countif(s)\" to build a formula.","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392692#Comment_392692","dateInserted":"2023-08-26T00:34:49+00:00","insertUserID":161714,"insertUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-08-27T02:16:35+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-26T00:33:25+00:00","dateAnswered":"2023-08-25T20:44:12+00:00","acceptedAnswers":[{"commentID":392662,"body":"
Try this:<\/p>
=COUNTIFS([Item Number]:[Item Number], OR(@cell = \"C001\", @cell = \"COO2\", @cell = \"COO3\", @cell = \"COO4\"), [Status]:[Status], OR(@cell = \"Green\", @cell = \"Yellow\", @cell = \"Red\"))<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"formulas","name":"Formulas"}]},{"discussionID":109474,"type":"question","name":"Help with date calculation formula","excerpt":"Hello, I'm trying to find a formula that will help me calculate how long an intake took to resolve. The rows I need to be calculated are Date Reported & Resolution Date. If the resolution date is blank I want it to use the current date in the calculation to see how long this issue has gone unresolved. Any help is much…","snippet":"Hello, I'm trying to find a formula that will help me calculate how long an intake took to resolve. The rows I need to be calculated are Date Reported & Resolution Date. If the…","categoryID":322,"dateInserted":"2023-08-25T16:29:39+00:00","dateUpdated":"2023-08-25T16:29:59+00:00","dateLastComment":"2023-08-25T23:01:30+00:00","insertUserID":165688,"insertUser":{"userID":165688,"name":"Nwest","title":"Systems Analyst","url":"https:\/\/community.smartsheet.com\/profile\/Nwest","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!ukHVZ18ImX4!BcjWAe8S9SY!l7iQo_PZHOx","dateLastActive":"2023-08-25T17:22:30+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":165688,"lastUserID":8888,"lastUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-26T17:06:33+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":25,"score":null,"hot":3385987269,"url":"https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":109474,"commentID":392687,"name":"Re: Help with date calculation formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392687#Comment_392687","dateInserted":"2023-08-25T23:01:30+00:00","insertUserID":8888,"insertUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-26T17:06:33+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-25T17:04:22+00:00","dateAnswered":"2023-08-25T16:36:59+00:00","acceptedAnswers":[{"commentID":392622,"body":"
