Recreating the formula imported from excel

Michaela Kamenska

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.

Genevieve P.

Hi Michaela,

I answered you o the other post but didn't quite understand the full question; this definitely helps. I have an idea for how to do this, but it would mean adding in a DATE function to a formula with a different date for each column (since they each represent different months).

What about something that says, if the Month in the Start date is in the past, or equal to, this month, AND the date in the End Date is in the future or equal to this month, then return: Hours in this row divided by Duration in this row.

This would be for April:

=IF(AND(MONTH([Start Date]@row) <= MONTH(DATE(2020, 4, 1)), MONTH([End Date]@row) >= MONTH(DATE(2020, 4, 1))), ([MO Hours]@row / [Duration (Months)]@row), "")

To change it for May's column, you'd just need to adjust the 4 in the DATE function to be 5:

=IF(AND(MONTH([Start Date]@row) <= MONTH(DATE(2020, 5, 1)), MONTH([End Date]@row) >= MONTH(DATE(2020, 5, 1))), ([MO Hours]@row / [Duration (Months)]@row), "")

And so on, for the other columns. Now, this is only looking at the Months... would you cross over into 2021? Also, will your Start and End dates always be 1 and 31 (or 30)?

Michaela Kamenska

Hi Genevieve,

I will see how that works, but I prefer something more generic, not specific. Yes, some projects last for even 5--7years so I would definitely cross over to another year for 98% of the projects.

To answer your second question, no, they do not always start 1st and end last day of the month. That is just a made-up date so I can better see if it populates correctly.