Automatic breakdown of hours into columns

Paul Newcome

Can you provide a screenshot (or multiple screenshots if the sheet is too wide) that shows manually entered data in the date columns, duration column, and the desired output in the [Month-20] columns?

Michaela Kamenska

Something like this?

Paul Newcome

Exactly like that. Seeing the "big picture" really helps. I need to do a little testing to work out the details, but I do have some ideas.

Additionally...

What if the [Start Date] is in the middle or near the end of the month? Would you want to "prorate" the hours worked there in the first applicable [Month-yy] column?

What if the [End Date] isn't at the end of the month but in the middle or near the beginning? Would you want to "prorate" the hours in the last applicable [Month-yy] column?

Or are you fine with just having the hours spread out evenly?

Michaela Kamenska

The start date will 98% of times be on the first of month and end date on the last of the month, so I am fine with just having the hours spread out evenly, thank you! :)

Paul Newcome

Ok. And (hopefully) final question... Are you open to adding two rows that will not be used for the displayed data but to house "helper data" to allow for more efficient formula replication across your columns?

Michaela Kamenska

Absolutely. Anything as long as it works the desired way!

Paul Newcome

Great! Let me throw something together in between calls, and I will get back to you.

Michaela Kamenska

No problem at all, take your time!

Paul Newcome

Ok. So the below is assuming that your month columns go from [Apr-20] on the left through [Dec-22] on the right. If that is not accurate, you will need to update the below accordingly.

I also did not differentiate between SS and MO hours. I only used one Hours column to put this together, so if you need help tweaking this to get it into your nested IF statements feel free to let me know.

I also found that it was actually easier to use only one "helper row" instead of two.

In my formulas I used row 1 as my "helper row". If you wanted to use something different and need help adjusting the formulas for that, also feel free to let me know.

For the helper row I simply entered the year and month into each column as "yyyymm" It is important to use the 2 digit month ("01" for January and not "1") so that all entries are 6 digits.

Then you would use the below in the [Apr-20]2 cell as your ouput in your nested IF and you will be able to dragfill across and down for the rest of the rows/columns. The bold portion is where you will insert the "SS" vs "MO" hours.

=IF(AND(VALUE(YEAR($[Start Date]@row) + "" + IF(MONTH($[Start Date]@row) < 10, "0") + MONTH($[Start Date]@row)) <= [Apr-20]$1, VALUE(YEAR($[End Date]@row) + "" + IF(MONTH($[End Date]@row) < 10, "0") + MONTH($[End Date]@row)) >= [Apr-20]$1), $Hours@row / COUNTIFS($[Apr-20]$1:$[Dec-22]$1, AND(@cell >= VALUE(YEAR($[Start Date]@row) + "" + IF(MONTH($[Start Date]@row) < 10, "0") + MONTH($[Start Date]@row)), @cell <= VALUE(YEAR($[End Date]@row) + "" + IF(MONTH($[End Date]@row) < 10, "0") + MONTH($[End Date]@row)))))

Michaela Kamenska

Hi Paul, this is what I am getting:

The red coloured fields is where I applied the formula, it comes back blank (except the Apr-20 in the second row where it says #UNPARSEABLE). I adjusted the SS and MO hours to only 1 column (I checked if it is actually feasable to do that with the person primarily using the tracker [I am secondary] and they said no), but it doesn't seem to work. Where am I making a mistake?

For big picture to see that I am essentially using Row 1 as Helper:

HOWEVER, as soon as I drag down the formula to row #4, the previous row (#3) populates:

Could this be the reason for 2 "helper rows"? because Row 2 doesn't populate at all.

Also if the formula could be tweaked to adjust for MO and SS hours and "position" column, that would be awesome!

Paul Newcome

I can definitely help with the tweaks for "SS" and "MO" hours, but lets get this working first as this is the "core" of the formula so to speak.

I am not sure why you are getting the #UNPARSEABLE error or why it isn't populating that first data row (row 2). It populated fine in my sheet as show in the screenshot.

It looks like you have typed everything as it should be with commas and parenthesis and whatnot all in place...

Can you save a copy of the sheet, publish it as editable, then share the published link here so I can take a look? Of course remove any sensitive/confidential data or replace it as needed with "dummy data".

Michaela Kamenska

I don't know what I did, but it eventually worked on the back-up test sheet and also freshest copy of the most up to date sheet! Also where it initially said unparseable, I re-copy-pasted the formula and dragged and it worked. Do you think you could try tweak the formula on the SS and MO hours so I can test it in that environment and possibly get back to you if anything?

Paul Newcome

Ok. So here we have our output:

=IF(AND(VALUE(YEAR($[Start Date]@row) + "" + IF(MONTH($[Start Date]@row) < 10, "0") + MONTH($[Start Date]@row)) <= [Apr-20]$1, VALUE(YEAR($[End Date]@row) + "" + IF(MONTH($[End Date]@row) < 10, "0") + MONTH($[End Date]@row)) >= [Apr-20]$1), $Hours@row / COUNTIFS($[Apr-20]$1:$[Dec-22]$1, AND(@cell >= VALUE(YEAR($[Start Date]@row) + "" + IF(MONTH($[Start Date]@row) < 10, "0") + MONTH($[Start Date]@row)), @cell <= VALUE(YEAR($[End Date]@row) + "" + IF(MONTH($[End Date]@row) < 10, "0") + MONTH($[End Date]@row)))))

We want to replace $Hours@row with either [SS Hours]@row or [MO Hours]@row depending on Position@row, and we actually have a few options on how to do this.

I am going to assume that if Position@row = "MO" then [SS Hours]@row is going to be blank and the other way around. If Position@row = "SS" then [MO Hours]@row is going to be blank. You also have those two columns right next to each other, and since any number is larger than a blank, we can just use

MAX($[SS Hours]@row:$[MO Hours]@row)

You want the formula to operate exactly the same regardless of SS vs MO, so we can just use...

=IF(AND(VALUE(YEAR($[Start Date]@row) + "" + IF(MONTH($[Start Date]@row) < 10, "0") + MONTH($[Start Date]@row)) <= [Apr-20]$1, VALUE(YEAR($[End Date]@row) + "" + IF(MONTH($[End Date]@row) < 10, "0") + MONTH($[End Date]@row)) >= [Apr-20]$1), MAX($[SS Hours]@row:$[MO Hours]@row) / COUNTIFS($[Apr-20]$1:$[Dec-22]$1, AND(@cell >= VALUE(YEAR($[Start Date]@row) + "" + IF(MONTH($[Start Date]@row) < 10, "0") + MONTH($[Start Date]@row)), @cell <= VALUE(YEAR($[End Date]@row) + "" + IF(MONTH($[End Date]@row) < 10, "0") + MONTH($[End Date]@row)))))

This will keep you from having to repeat it in an IF statement since it is already a bit of a messy formula.

EDIT: Missed a parenthesis.

Michaela Kamenska

Paul can you advise if this is number/order of columns dependent? because it seems that if I delete certain columns, it works, but otherwise it just shows empty space where a number should be.

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)