Calculation of 2 types of different hours in a single row

Michaela Kamenska

Hello all,

So I've got this formula that automatically breaks down budgeted hours into individual cells based on Start and Finish Date and Total budgeted. Now I'd like to add another set to this formula that will calculate different hours and break them down too. The circled ones are already being calculated and the yellow ones I'd like to add to calculate equally into the highlighted cells. It's always going to be 3 months duration which I think may make the second formula easier - now my problem is how to join them together so I can just dragfill the whole row with the formula and it will automatically do what it needs to do, ie put Start-Up Hours to certain cells and then fill the rest with what it already being done? Please note that the total budgeted does not include the Start-Up hours. These are two separate sets of hours.

The formula I currently have is:

=IFERROR(IF(AND(VALUE(YEAR($[MDR Start]@row) + "" + IF(MONTH($[MDR Start]@row) < 10, "0") + MONTH($[MDR Start]@row)) <= [Oct-20]$1, VALUE(YEAR($Finish@row) + "" + IF(MONTH($Finish@row) < 10, "0") + MONTH($Finish@row)) >= [Oct-20]$1), $[Total Budgeted]@row / COUNTIFS($[Oct-20]$1:$[Dec-26]$1, AND(@cell >= VALUE(YEAR($[MDR Start]@row) + "" + IF(MONTH($[MDR Start]@row) < 10, "0") + MONTH($[MDR Start]@row)), @cell <= VALUE(YEAR($Finish@row) + "" + IF(MONTH($Finish@row) < 10, "0") + MONTH($Finish@row))))), "")

I also have a helper row for this formula that looks as follows:

I've tried replacing the column names in the formula with the new column names, but it is a trouble for me to somehow joining these two formulas together.

Thanks!

Toufong Vang

Michaela, that's what the formula I shared does. And it looks like there was a syntax error in my formula which you found and corrected. Below is the corrected formula.

IFERROR(
 IF(AND(([Oct-20]$1 >= YEAR($[Start-up Start Date]@row) + RIGHT("00"+MONTH($[Start-up Start Date]@row),2)),
	([Oct-20]$1 <= YEAR($[FPI/Start-Up End]@row) + RIGHT("00"+MONTH($[FPI/Start-Up End]@row),2)))
    , $[Start-Up Hours]@row/$[Start-Up Duration]@row
    , IF(AND(([Oct-20]$1 >= YEAR($[MDR Start]@row) +RIGHT("00"+MONTH($[MDR Start]@row),2)),
            ([Oct-20]$1 <= YEAR($[Finish]@row) + RIGHT("00"+MONTH($[Finish]@row),2)))
	, $[Total Budgeted]@row/$[MDR Duration (months)]@row
	, "")
    )
,"")

As you noted, however, the formula will place the hours ( total hours ÷ duration ) into all months that fall within the date ranges. The alternative is to use your method of dividing the total hours by the count of columns/cells that fall within the start and end/finish dates.

For start-up hours:

$[Start-Up Hours]@row / COUNTIFS($[Oct-20]$1:$[Dec-26]$1, AND(@cell >= VALUE(YEAR($[Start-up Start Date]@row) + RIGHT("00"+MONTH($[Start-up Start Date]@row),2)), @cell <= VALUE(YEAR($[FPI/Start-Up End]@row) + RIGHT("00"+MONTH($[FPI/Start-Up End]@row),2))))

For MDR duration hours

$[Total Budgeted]@row/COUNTIFS($[Oct-20]$1:$[Dec-26]$1, AND(@cell >= VALUE(YEAR($[MDR Start]@row) + RIGHT("00"+MONTH($[MDR Start]@row),2)), @cell <= VALUE(YEAR($[Finish]@row) + RIGHT("00"+MONTH($[Finish]@row),2))))

It's possible to get the precise number of hours for the months but that would involve calculating the number of days within the period, and then evaluating for the amount of hours to post for each month.

Toufong Vang

Hello, @Michaela Kamenska .

Give this a try.

=IFERROR( IF( AND( ([Oct-20]$1 >= YEAR($[Start-up Start Date]@row) + RIGHT("00"+MONTH($[Start-up Start Date]@row,2))),([Oct-20]$1 <= YEAR($[FPI/Start-Up End]@row) + RIGHT("00"+MONTH($[FPI/Start-Up End]@row,2))) ) , $[Start-Up Hours]@row/$[Start-Up Duration]@row, IF( AND( ([Oct-20]$1 >= YEAR($[MDR Start]@row) +RIGHT("00"+MONTH($[MDR Start]@row,2))) ,([Oct-20]$1 <= YEAR($[Finish]@row) + RIGHT("00"+MONTH($[Finish]@row,2))) ) , $[Total Budgeted]@row/$[MDR Duration (months)]@row, "")), "" ) 

Here's how I worked through it.

IF...

(1) the content at row #1 of the current column is between Start-Up Start Date and FPI/Start-Up End dates...

AND(([Oct-20]$1 >= YEAR($[Start-up Start Date]@row) + RIGHT("00"+MONTH($[Start-up Start Date]@row,2))),
    ([Oct-20]$1 <= YEAR($[FPI/Start-Up End]@row) + RIGHT("00"+MONTH($[FPI/Start-Up End]@row,2))))

(2) then insert Start-Up Hours ÷ Start-Up Duration

$[Start-Up Hours]@row/$[Start-Up Duration]@row

Else...

(3) IF the content at row #1 of the current column is between the MDR Start and Finish dates... 

AND(([Oct-20]$1 >= YEAR($[MDR Start]@row) + RIGHT("00"+MONTH($[MDR Start]@row,2))),
    ([Oct-20]$1 <= YEAR($[Finish]@row) + RIGHT("00"+MONTH($[Finish]@row,2))))

(4) then insert Total Budgeted ÷ MDR Duration (months)

$[Total Budgeted]@row/$[MDR Duration (months)]@row

Else...

(5) insert "".

Enclose the statement within IFERROR( ).

Here's the formula formatted for easier reading.

IFERROR(
  IF(AND(([Oct-20]$1 >= YEAR($[Start-up Start Date]@row) + RIGHT("00"+MONTH($[Start-up Start Date]@row,2))),
	 ([Oct-20]$1 <= YEAR($[FPI/Start-Up End]@row) + RIGHT("00"+MONTH($[FPI/Start-Up End]@row,2))))
    , $[Start-Up Hours]@row/$[Start-Up Duration]@row
    , IF(AND(([Oct-20]$1 >= YEAR($[MDR Start]@row) +RIGHT("00"+MONTH($[MDR Start]@row,2))),
             ([Oct-20]$1 <= YEAR($[Finish]@row) + RIGHT("00"+MONTH($[Finish]@row,2))))
	, $[Total Budgeted]@row/$[MDR Duration (months)]@row
	, ""
	)
   )
,"")

Cheers!

Michaela Kamenska

Hi @Toufong Vang ,

I'm afraid that the formula is not working properly. What it does is spreading the Start-Up hours equally across the cells that are already populated. what I'm trying to get is something like this (for illustrative purposes only):

Toufong Vang

Looking specifically at the second row in the image below, is this what you are trying to get the combined formula to do?

Michaela Kamenska

Yes, that's the idea. I am aware that the dates are not always exact, eg. 10-sep-2021 to 10dec-2021 runs through 4 different months (sept, oct, nov, dec), but if I could just get that start-up hours split across start-up start date and FPI/Start-Up End Date and then the rest as you indicated, that's what I'm looking for. Do you think it's possible?

Toufong Vang

Michaela, that's what the formula I shared does. And it looks like there was a syntax error in my formula which you found and corrected. Below is the corrected formula.

IFERROR(
 IF(AND(([Oct-20]$1 >= YEAR($[Start-up Start Date]@row) + RIGHT("00"+MONTH($[Start-up Start Date]@row),2)),
	([Oct-20]$1 <= YEAR($[FPI/Start-Up End]@row) + RIGHT("00"+MONTH($[FPI/Start-Up End]@row),2)))
    , $[Start-Up Hours]@row/$[Start-Up Duration]@row
    , IF(AND(([Oct-20]$1 >= YEAR($[MDR Start]@row) +RIGHT("00"+MONTH($[MDR Start]@row),2)),
            ([Oct-20]$1 <= YEAR($[Finish]@row) + RIGHT("00"+MONTH($[Finish]@row),2)))
	, $[Total Budgeted]@row/$[MDR Duration (months)]@row
	, "")
    )
,"")

As you noted, however, the formula will place the hours ( total hours ÷ duration ) into all months that fall within the date ranges. The alternative is to use your method of dividing the total hours by the count of columns/cells that fall within the start and end/finish dates.

For start-up hours:

$[Start-Up Hours]@row / COUNTIFS($[Oct-20]$1:$[Dec-26]$1, AND(@cell >= VALUE(YEAR($[Start-up Start Date]@row) + RIGHT("00"+MONTH($[Start-up Start Date]@row),2)), @cell <= VALUE(YEAR($[FPI/Start-Up End]@row) + RIGHT("00"+MONTH($[FPI/Start-Up End]@row),2))))

For MDR duration hours

$[Total Budgeted]@row/COUNTIFS($[Oct-20]$1:$[Dec-26]$1, AND(@cell >= VALUE(YEAR($[MDR Start]@row) + RIGHT("00"+MONTH($[MDR Start]@row),2)), @cell <= VALUE(YEAR($[Finish]@row) + RIGHT("00"+MONTH($[Finish]@row),2))))

It's possible to get the precise number of hours for the months but that would involve calculating the number of days within the period, and then evaluating for the amount of hours to post for each month.

Michaela Kamenska

@Toufong Vang thank you. I'm not sure what I did wrong the first time, but it did not populate those 3 cells with different hours - what it did for me was that whatever there was originally e.g 10hours for all the cells, it just upped it by 1hr extra, flatlining everything to 11hours instead. I tried repasting the new formula and it works great, thank you!

Michaela Kamenska

Hi @Toufong Vang ,

I was wondering if you can help:

I've taken your original formula in this post and adjusted it for the needs of another sheet where I only have 1 set of hours to be split amongst the cells and the adjusted formula now reads:

=IFERROR(IF(AND(([Jan-21]$1 >= YEAR($[Start-Up Date]@row) + RIGHT("00" + MONTH($[Start-Up Date]@row), 2)), ([Jan-21]$1 <= YEAR($[End Date (CSR)]@row) + RIGHT("00" + MONTH($[End Date (CSR)]@row), 2))), $[Total Budgeted incl MDR Oversight]@row / $[Duration (months)]@row), "")

As you can see below, some cells, however, do not want to populate and if I remove it "If error" to find what error it is, it says #INVALID OPERATION. Would you be able to help with why this might be?

Thank you.