Automatic breakdown of hours into columns
Hi All,
I tried re-importing the file from excel with formulas and this is what I'm getting (see pic). The idea is that I will apply the formula to all the columns and when I enter the start date and duration (which calculates end date), it will automatically auto-populate all applicable columns without me having to do it manually. Could you help?
Original EXCEL FORMULA:
=IF($R8="SS",IF(AND(ISNUMBER($M8),ISNUMBER($O8),ISNUMBER($P8),FE$7<$Q8,FE$7>=$O8),$M8/$P8,""),IF($R8="MO",IF(AND(ISNUMBER($N8),ISNUMBER($O8),ISNUMBER($P8),FE$7<$Q8,FE$7>=$O8),$N8/$P8,""),""))
The imported formulas in the month columns are as follows:
Apr-20 --->
= IF ($ Position1 =“党卫军”,如果(和(ISNUMBER ($ [SS小时]1), ISNUMBER($[Start Date]1), ISNUMBER($[Duration (months)]1), [Apr-20]$6 < $[End Date]1, [Apr-20]$6 >= $[Start Date]1), $[SS Hours]1 / $[Duration (months)]1, ""), IF($Position1 = "MO", IF(AND(ISNUMBER($[MO Hours]1), ISNUMBER($[Start Date]1), ISNUMBER($[Duration (months)]1), [Apr-20]$6 < $[End Date]1, [Apr-20]$6 >= $[Start Date]1), $[MO Hours]1 / $[Duration (months)]1, ""), ""))
May-20--->
= IF ($ Position1 =“党卫军”,如果(和(ISNUMBER ($ [SS小时]1), ISNUMBER($[Start Date]1), ISNUMBER($[Duration (months)]1), [May-20]$6 < $[End Date]1, [May-20]$6 >= $[Start Date]1), $[SS Hours]1 / $[Duration (months)]1, ""), IF($Position1 = "MO", IF(AND(ISNUMBER($[MO Hours]1), ISNUMBER($[Start Date]1), ISNUMBER($[Duration (months)]1), [May-20]$6 < $[End Date]1, [May-20]$6 >= $[Start Date]1), $[MO Hours]1 / $[Duration (months)]1, ""), ""))
...and so on, and so forth. Your help would be greatly appreciated.
Best Answers
-
Michaela Kamenska ✭✭✭✭✭✭
Please see the links attached of the published sheet to play about with:
https://app.smartsheet.com/b/publish?EQBCT=6b500b9463d7490fa5f2d9c18f502d1b
when it doesn't put empty values, it gives me #INVALID OPERATION as seen in the sheet (that's the only row where I applied the formula)
-
Paul Newcome ✭✭✭✭✭✭There was a misplaced parenthesis. Once I moved it, it is working now.
Answers
-
Paul Newcome ✭✭✭✭✭✭
-
Michaela Kamenska ✭✭✭✭✭✭
@Paul Newcomenow that I re-tried it in one of my sheets, it is coming up as #INVALID OPERATION. When imported straight from Excel, it showed #BLOCKED. I can't figure out what is not right with that equation, that's why I tried posting here.
-
Paul Newcome ✭✭✭✭✭✭
Did you verify that the date columns are formatted as date types?
-
Michaela Kamenska ✭✭✭✭✭✭
Do you mean start date - end date, or the ones where I want to see the allocated hours per month (i.e. Apr-20, May-20 etc)? (the start date and end date are formatted as date type, the Apr-20 etc are not formatted as date types).
Ah!!! I think I know where you're coming from. However, if I format Apr-20 etc as date type column, it will not show me the allocated hours, correct? Would you suggest a different solution?
-
Paul Newcome ✭✭✭✭✭✭
No. I meant the Start and End Date columns.
Lets do a quick test...
Insert a text/number type column and enter this formula into a row that contains a date...
=IF(ISDATE([Start Date]@row), "DATE", "TEXT")
What does that output?
-
Michaela Kamenska ✭✭✭✭✭✭
The start and end date are selected as date type columns. this is what I got:
The end date is automatically calculated based on the entered duration.
and this is what I get when I put it into the Apr-20 column
-
Paul Newcome ✭✭✭✭✭✭
Which column are you referencing in the formula for the screenshots above?
-
Michaela Kamenska ✭✭✭✭✭✭
When I entered the formula you provided (=IF(ISDATE([Start Date]@row), "DATE", "TEXT")) earlier for the test in the "start date" column, I got #CIRCULAR REFERENCE. When I entered the formula you provided earlier in the Apr-20, it wrote DATE.
-
Paul Newcome ✭✭✭✭✭✭
Ok. Try it again (in the [Apr-20] column) but reference the [End Date] column this time.
-
Michaela Kamenska ✭✭✭✭✭✭
Ok done it. Shows the same thing. Does it help us?
-
Paul Newcome ✭✭✭✭✭✭
It lets us know that the two date columns are NOT the problem. Haha. Lets see...
Lets adjust the formula a little bit (you can leave it where it is for now)...
=IF(ISNUMBER([Duration (months)]@row), "NUMBER", "TEXT")
-
Paul Newcome ✭✭✭✭✭✭
Also try that same formula referencing the [SS Hours] column and the [MO Hours] column.
-
Michaela Kamenska ✭✭✭✭✭✭
Both of the references come back as TEXT.
-
Paul Newcome ✭✭✭✭✭✭
There's the problem. Your numbers are coming over as text. We need to convert those to numerical values using the VALUE function.
Wherever you reference those cells in your formulas, wrap them like so...
VALUE([SS Hours]@row)
VALUE([MO Hours]@row)
VALUE([Duration (months)]@row)
-
Michaela Kamenska ✭✭✭✭✭✭
Oh Sorry Paul, I've overlooked this part. I just went with DATE and TEXT formula. When I enter this and thre is a number in the SS Hours and MO hours column, it comes back as NUMBER (same NUMBER comes when referencing duration)
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":"
