Hi All,
I've recently posted with regards to automatic breakdown of hours based on duration and budgeted, however, no luck. I tried re-importing the file from excel with formulas and this is what I'm getting (see pic). Could you help?
There is a formula used in the END DATE which is working fine (not sure if necessary to put this here)
=DATE(YEAR([Start Date]@row) + ROUNDDOWN((MONTH([Start Date]@row) + [Duration (months)]@row) / 12, 0) + IF(IF(MOD(MONTH([Start Date]@row) + [Duration (months)]@row, 12) = 0, 12, MOD(MONTH([Start Date]@row) + [Duration (months)]@row, 12)) = 12, -1), IF(MOD(MONTH([Start Date]@row) + [Duration (months)]@row, 12) = 0, 12, MOD(MONTH([Start Date]@row) + [Duration (months)]@row, 12)), DAY([Start Date]@row)) - 1
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.