# 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 = "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.

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

• @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.

• 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. 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?

• 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 Apr-20 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 Apr-20, it wrote DATE.

• Ok. Try it again (in the [Apr-20] 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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!