# Automatic breakdown of hours into columns

Options
✭✭✭✭✭✭

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 = "SS", IF(AND(ISNUMBER(\$[SS Hours]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 = "SS", IF(AND(ISNUMBER(\$[SS Hours]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.

• ✭✭✭✭✭✭
Options

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)

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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?

• ✭✭✭✭✭✭
Options

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?

• ✭✭✭✭✭✭
edited 06/25/20
Options

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

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

Both of the references come back as TEXT.

• ✭✭✭✭✭✭
Options

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)

• ✭✭✭✭✭✭
edited 06/25/20
Options

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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!