Automatic breakdown of hours into columns
Hi All,
I tried reimporting 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 autopopulate 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:
Apr20 >
=IF($Position1 = "SS", IF(AND(ISNUMBER($[SS Hours]1), ISNUMBER($[Start Date]1), ISNUMBER($[Duration (months)]1), [Apr20]$6 < $[End Date]1, [Apr20]$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), [Apr20]$6 < $[End Date]1, [Apr20]$6 >= $[Start Date]1), $[MO Hours]1 / $[Duration (months)]1, ""), ""))
May20>
=IF($Position1 = "SS", IF(AND(ISNUMBER($[SS Hours]1), ISNUMBER($[Start Date]1), ISNUMBER($[Duration (months)]1), [May20]$6 < $[End Date]1, [May20]$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), [May20]$6 < $[End Date]1, [May20]$6 >= $[Start Date]1), $[MO Hours]1 / $[Duration (months)]1, ""), ""))
...and so on, and so forth. Your help would be greatly appreciated.
Best Answers

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)

There was a misplaced parenthesis. Once I moved it, it is working now.
Answers


@Paul Newcome now that I retried 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.

Did you verify that the date columns are formatted as date types?

Do you mean start date  end date, or the ones where I want to see the allocated hours per month (i.e. Apr20, May20 etc)? (the start date and end date are formatted as date type, the Apr20 etc are not formatted as date types).
Ah!!! I think I know where you're coming from. However, if I format Apr20 etc as date type column, it will not show me the allocated hours, correct? Would you suggest a different solution?

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?

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 Apr20 column

Which column are you referencing in the formula for the screenshots above?

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 Apr20, it wrote DATE.

Ok. Try it again (in the [Apr20] column) but reference the [End Date] column this time.

Ok done it. Shows the same thing. Does it help us?

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")

Also try that same formula referencing the [SS Hours] column and the [MO Hours] column.

Both of the references come back as TEXT.

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)

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 the Formula Handbook template!