Automatic breakdown of hours into columns

Michaela Kamenska

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.

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.

Paul Newcome

Do you have any error(s) other than #BLOCKED?

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

It lets us know that the two date columns are NOT the problem. Haha. Lets see...<\/p>

Lets adjust the formula a little bit (you can leave it where it is for now)...<\/p>

=IF(ISNUMBER([Duration (months)]@row), "NUMBER", "TEXT")<\/p>","bodyRaw":"[{\"insert\":\"It lets us know that the two date columns are NOT the problem. Haha. Lets see...\\nLets adjust the formula a little bit (you can leave it where it is for now)...\\n=IF(ISNUMBER([Duration (months)]@row), \\\"NUMBER\\\", \\\"TEXT\\\")\\n\"}]","format":"rich","dateInserted":"2020-06-25T15:13:30+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\/254800#Comment_254800","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/254800#Comment_254800

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)