Automatic breakdown of hours into columns

Paul Newcome

I just noticed that you have this twice in your formula

ISNUMBER($[Start Date]1)

Try changing it from ISNUMBER to ISDATE.

Michaela Kamenska

I just noticed that you have this twice in your formula<\/p>

ISNUMBER($[Start Date]1)<\/em><\/p>

Try changing it from ISNUMBER to ISDATE.<\/p>","bodyRaw":"[{\"insert\":\"I just noticed that you have this twice in your formula\\n\"},{\"attributes\":{\"italic\":true},\"insert\":\"ISNUMBER($[Start Date]1)\"},{\"insert\":\"\\n\\nTry changing it from ISNUMBER to ISDATE.\\n\"}]","format":"rich","dateInserted":"2020-06-25T15:56:54+00:00","insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-08-25T17:46:09+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/254814#Comment_254814","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/254814#Comment_254814

ok so I used this formula (see my previous comment, I've not noticed we changed to "ISNUMBER" test)

https:\/\/community.smartsheet.com\/discussion\/comment\/254800#Comment_254800<\/a><\/p>

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)<\/p>","bodyRaw":"[{\"insert\":\"\\n\"},{\"insert\":\"https:\\\/\\\/community.smartsheet.com\\\/discussion\\\/comment\\\/254800#Comment_254800\",\"attributes\":{\"link\":\"https:\\\/\\\/community.smartsheet.com\\\/discussion\\\/comment\\\/254800#Comment_254800\"}},{\"insert\":\"\\n\"},{\"insert\":\"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)\\n\"}]","format":"rich","dateInserted":"2020-06-25T15:52:07+00:00","insertUser":{"userID":120361,"name":"Michaela Kamenska","url":"https:\/\/community.smartsheet.com\/profile\/Michaela%20Kamenska","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!mtlVn-dB17o!ellWZ_MZE7w!nMSQy5V-vdU","dateLastActive":"2023-07-07T07:10:41+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/254812#Comment_254812","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/254812#Comment_254812

=IF($Position1 = "SS", IF(AND(ISNUMBER($[SS Hours]1), ISDATE($[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), ISDATE($[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, ""), ""))

and it still comes back as invalid operation. Should we add something else somewhere? Or am I missing properly closed condition (parentheses) somewhere?

Michaela Kamenska

@Paul Newcomeor is it the spaces before/after signs like before ", before and after /, before and after <...? I am so lost.

Paul Newcome

What exactly is in[Apr-20]$6?

我刚刚意识到行号是不同的。It looks like you are comparing it to a date, but if I am not mistaken, your [Apr-20] is a text/number type.

Michaela Kamenska

That is something I did not understand in Excel either, unfortunately.

I think I tried converting those values from $6 to "@row" but think it still didn't work. Now I tried converting all those values (including 1 and changing it to @row), but it says #UNPARSEABLE:

=IF(AND(ISNUMBER($[SS Hours]@row), ISDATE($[Start Date]@row), ISNUMBER($[Duration (months)]@row), [Apr-20]@row < $[End Date]@row, [Apr-20]@row >= $[Start Date]@row), $[SS Hours]@row / $[Duration (months)]@row, ""), IF(AND(ISNUMBER($[MO Hours]@row), ISDATE($[Start Date]@row), ISNUMBER($[Duration (months)]@row), [Apr-20]@row < $[End Date]@row, [Apr-20]@row >= $[Start Date]@row), $[MO Hours]@row / $[Duration (months)]@row, ""), ""))

I think the dollar sign means absolute value in Excel, tried deleting that too but it says #UNPARSEABLE :

=IF(AND(ISNUMBER([SS Hours]@row), ISDATE([Start Date]@row), ISNUMBER([Duration (months)]@row), [Apr-20]@row < [End Date]@row, [Apr-20]@row >= [Start Date]@row), [SS Hours]@row / [Duration (months)]@row, ""), IF(AND(ISNUMBER([MO Hours]@row), ISDATE([Start Date]@row), ISNUMBER([Duration (months)]@row), [Apr-20]@row < [End Date]@row, [Apr-20]@row >= [Start Date]@row), [MO Hours]@row / [Duration (months)]@row, ""), ""))

Michaela Kamenska

What exactly is in [Apr-20]$6<\/strong>? <\/p>

I Just now realized that row number is different. It looks like you are comparing it to a date, but if I am not mistaken, your [Apr-20] is a text\/number type.<\/p>","bodyRaw":"[{\"insert\":\"What exactly is in \"},{\"attributes\":{\"bold\":true},\"insert\":\"[Apr-20]$6\"},{\"insert\":\"? \\nI Just now realized that row number is different. It looks like you are comparing it to a date, but if I am not mistaken, your [Apr-20] is a text\\\/number type.\\n\\n\"}]","format":"rich","dateInserted":"2020-06-25T17:16:37+00:00","insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-08-25T17:46:09+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/254832#Comment_254832","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/254832#Comment_254832

Yes, [Apr-20] onwards are all text/number formatted as I need numerical value there

Paul Newcome

But it has data that represents a date? Can you provide a screenshot that includes that cell along with the formula cell?

Michaela Kamenska

@Paul NewcomeI am not sure if this helps, but I provide a sample with the formulas in excel that work. Please note lots of columns are hidden in the sheet, however, my intention is to apply the formula for the incoming projects, not the past ones.

sample.xlsx

Hopefully you'll get the idea of what I'm trying to achieve :) The data in [Apr-20] onwards should show average hours per month, my idea is to put the formula in my whole row so it autopopulates as in the excel sample I provided ( you can try to just change the hours, start date or duration and the numbers automatically change).

Paul Newcome

One of the differences (which I think is coming into play here) is that in Excel you can format individual cells whereas in Smartsheet you can only format on the column level. If you are trying to reference a date in a text/number column, then we will need to use a DATE function with MID/LEFT/RIGHT functions to get it into a formula as an actual date.

Can you show the SS in a screenshot that also includes the[Apr-20]$6cell?

Michaela Kamenska

Is this what you mean?

I have a mock sheet where I'm just entering values in SS or MO to get us sorted :)

Paul Newcome

Exactly what does the date in[Apr-20]$6look like? How is it formatted?

Michaela Kamenska

The [Apr-20] as well aas all columns onwards are formatted as number/text:

There is currently a numerical value in them inputted manually.

Paul Newcome

The bold portion in your formula below is why I am asking about that particular cell. You are trying to compare a number to a date. This is where the error is coming from.

=IF($Position1 = "SS", IF(AND(ISNUMBER($[SS Hours]1), ISDATE($[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), ISDATE($[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, ""), ""))

If you are trying to input a date into[Apr-20]$6for comparison, then we need to figure out how exactly you are entering that date so that we can piece it together in a DATE function so that it can actually be used in the formula as a date.

mm/dd/yy

mm/dd/yyyy

dd/mm/yy

dd/mm/yyyy

dd-MON-yy

etc.

Michaela Kamenska

@Paul Newcomethat's the thing, I am not trying to input a date into the [Apr-20] column or the ones afterwards. My intention is to have a numerical value there. So if I have a project that runs from 01 April 2020 to 31 March 2021 (12 months) and my budgeted hours in total for the project are 120, then I would like to have the [Apr-20] [May-20].....[March 21] to be automatically populated with number 10. So I would like the [Apr-20] etc column to have formulas there, so when I get a new project, I only enter the date, duration and budgeted hours, and the rest of the columns [Apr-20] etc will populate with a value obtained by division of budgeted hours/duration.

So I do not have to manually go into respective columns and drag fill them for the duration of the study, but that it will automatically calculate and populate, like in the sample Excel sheet I shared here:

@Paul Newcome<\/a> I am not sure if this helps, but I provide a sample with the formulas in excel that work. Please note lots of columns are hidden in the sheet, however, my intention is to apply the formula for the incoming projects, not the past ones.<\/p>

https:\/\/us.v-cdn.net\/6031209\/uploads\/VM69VLNM2PPV\/sample.xlsx<\/a><\/p>

Hopefully you'll get the idea of what I'm trying to achieve :) The data in [Apr-20] onwards should show average hours per month, my idea is to put the formula in my whole row so it autopopulates as in the excel sample I provided ( you can try to just change the hours, start date or duration and the numbers automatically change).<\/p>","bodyRaw":"[{\"insert\":{\"mention\":{\"name\":\"Paul Newcome\",\"userID\":45516}}},{\"insert\":\" I am not sure if this helps, but I provide a sample with the formulas in excel that work. Please note lots of columns are hidden in the sheet, however, my intention is to apply the formula for the incoming projects, not the past ones.\\n\"},{\"insert\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/VM69VLNM2PPV\\\/sample.xlsx\",\"attributes\":{\"link\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/VM69VLNM2PPV\\\/sample.xlsx\"}},{\"insert\":\"\\n\"},{\"insert\":\"\\nHopefully you'll get the idea of what I'm trying to achieve :) The data in [Apr-20] onwards should show average hours per month, my idea is to put the formula in my whole row so it autopopulates as in the excel sample I provided ( you can try to just change the hours, start date or duration and the numbers automatically change). \\n\"}]","format":"rich","dateInserted":"2020-06-25T18:00:19+00:00","insertUser":{"userID":120361,"name":"Michaela Kamenska","url":"https:\/\/community.smartsheet.com\/profile\/Michaela%20Kamenska","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!mtlVn-dB17o!ellWZ_MZE7w!nMSQy5V-vdU","dateLastActive":"2023-07-07T07:10:41+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/254852#Comment_254852","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/254852#Comment_254852

Is there any way this would be possible??

Michaela Kamenska

I understand now what you mean, so my question now is whether something similar would be possible at all?