Calculation of 2 types of different hours in a single row
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 StartUp 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 StartUp 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)) <= [Oct20]$1, VALUE(YEAR($Finish@row) + "" + IF(MONTH($Finish@row) < 10, "0") + MONTH($Finish@row)) >= [Oct20]$1), $[Total Budgeted]@row / COUNTIFS($[Oct20]$1:$[Dec26]$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!
Best Answer

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(([Oct20]$1 >= YEAR($[Startup Start Date]@row) + RIGHT("00"+MONTH($[Startup Start Date]@row),2)), ([Oct20]$1 <= YEAR($[FPI/StartUp End]@row) + RIGHT("00"+MONTH($[FPI/StartUp End]@row),2))) , $[StartUp Hours]@row/$[StartUp Duration]@row , IF(AND(([Oct20]$1 >= YEAR($[MDR Start]@row) +RIGHT("00"+MONTH($[MDR Start]@row),2)), ([Oct20]$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 startup hours:
$[StartUp Hours]@row / COUNTIFS($[Oct20]$1:$[Dec26]$1, AND(@cell >= VALUE(YEAR($[Startup Start Date]@row) + RIGHT("00"+MONTH($[Startup Start Date]@row),2)), @cell <= VALUE(YEAR($[FPI/StartUp End]@row) + RIGHT("00"+MONTH($[FPI/StartUp End]@row),2))))
For MDR duration hours
$[Total Budgeted]@row/COUNTIFS($[Oct20]$1:$[Dec26]$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.
Answers

Hello, @Michaela Kamenska .
Give this a try.
=IFERROR( IF( AND( ([Oct20]$1 >= YEAR($[Startup Start Date]@row) + RIGHT("00"+MONTH($[Startup Start Date]@row,2))),([Oct20]$1 <= YEAR($[FPI/StartUp End]@row) + RIGHT("00"+MONTH($[FPI/StartUp End]@row,2))) ) , $[StartUp Hours]@row/$[StartUp Duration]@row, IF( AND( ([Oct20]$1 >= YEAR($[MDR Start]@row) +RIGHT("00"+MONTH($[MDR Start]@row,2))) ,([Oct20]$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 StartUp Start Date and FPI/StartUp End dates...
AND(([Oct20]$1 >= YEAR($[Startup Start Date]@row) + RIGHT("00"+MONTH($[Startup Start Date]@row,2))), ([Oct20]$1 <= YEAR($[FPI/StartUp End]@row) + RIGHT("00"+MONTH($[FPI/StartUp End]@row,2))))
(2) then insert StartUp Hours ÷ StartUp Duration
$[StartUp Hours]@row/$[StartUp Duration]@row
Else...
(3) IF the content at row #1 of the current column is between the MDR Start and Finish dates...
AND(([Oct20]$1 >= YEAR($[MDR Start]@row) + RIGHT("00"+MONTH($[MDR Start]@row,2))), ([Oct20]$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(([Oct20]$1 >= YEAR($[Startup Start Date]@row) + RIGHT("00"+MONTH($[Startup Start Date]@row,2))), ([Oct20]$1 <= YEAR($[FPI/StartUp End]@row) + RIGHT("00"+MONTH($[FPI/StartUp End]@row,2)))) , $[StartUp Hours]@row/$[StartUp Duration]@row , IF(AND(([Oct20]$1 >= YEAR($[MDR Start]@row) +RIGHT("00"+MONTH($[MDR Start]@row,2))), ([Oct20]$1 <= YEAR($[Finish]@row) + RIGHT("00"+MONTH($[Finish]@row,2)))) , $[Total Budgeted]@row/$[MDR Duration (months)]@row , "" ) ) ,"")
Cheers!

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

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

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

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(([Oct20]$1 >= YEAR($[Startup Start Date]@row) + RIGHT("00"+MONTH($[Startup Start Date]@row),2)), ([Oct20]$1 <= YEAR($[FPI/StartUp End]@row) + RIGHT("00"+MONTH($[FPI/StartUp End]@row),2))) , $[StartUp Hours]@row/$[StartUp Duration]@row , IF(AND(([Oct20]$1 >= YEAR($[MDR Start]@row) +RIGHT("00"+MONTH($[MDR Start]@row),2)), ([Oct20]$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 startup hours:
$[StartUp Hours]@row / COUNTIFS($[Oct20]$1:$[Dec26]$1, AND(@cell >= VALUE(YEAR($[Startup Start Date]@row) + RIGHT("00"+MONTH($[Startup Start Date]@row),2)), @cell <= VALUE(YEAR($[FPI/StartUp End]@row) + RIGHT("00"+MONTH($[FPI/StartUp End]@row),2))))
For MDR duration hours
$[Total Budgeted]@row/COUNTIFS($[Oct20]$1:$[Dec26]$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 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!

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(([Jan21]$1 >= YEAR($[StartUp Date]@row) + RIGHT("00" + MONTH($[StartUp Date]@row), 2)), ([Jan21]$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.
Help Article Resources
Categories
Check out the Formula Handbook template!