Assistance with a calculation that compares the current date to month/year

Cheryl Moon

I am calculating labor costs based on project duration. I need to know the labor dollars only for the months greater than the current month (for example, if this month is July/2020, then only calculate August/2020 through February/2021. The following formula sums hours x rate based on value in month columns 1 - xx. What I have below works for the month of July/2020, but it is producing a negative number because I am subtracting the value of a calculated column [Total Labor Forecast]25 at the end. I have tried to move [Total Labor Forecast]25 immediately following SUM, but it doesn't work. I'm sure this is a syntax issue, but I have not been successful in resolving it. Any assistance or guidance is greatly appreciated! Thank you.

=IF(AND(MONTH(TODAY()) = 7, YEAR(TODAY()) = 2020), SUM([Month 1]25 * [Month 1]2) * Rate25) - [Total Labor Forecast]25

Genevieve P.

Hi Cheryl,

I believe you're running into an issue because in your SUM function you are using a * symbol which will multiply the cells in row 25 and in row 2 together.

Are you wanting to add together the values from the range of rows 2 down to 25, instead? If so, you'll want to put colon between the two cells to indicate that it's a range, like this -

SUM([Month 1]2:[Month 1]25)

Our Help Center has more information on referencing columns (see here).

Try this as your full formula, instead:

=IF(AND(MONTH(TODAY()) = 7, YEAR(TODAY()) = 2020), SUM([Month 1]2:[Month 1]25) * Rate25) - [Total Labor Forecast]25

Let me know if this works for you!

Cheers,

Genevieve

Genevieve P.

Hi Cheryl,

Thank you for clarifying! The SUM function will add the cells together instead of multiply, so in this instance you can use parenthesis to indicate what should be done... try this:

=IF(AND(MONTH(TODAY()) = 7, YEAR(TODAY()) = 2020), (([Month 1]2 * [Month 1]25) * Rate25) - [Total Labor Forecast]25

Genevieve P.

Hi Cheryl,

I believe you're running into an issue because in your SUM function you are using a * symbol which will multiply the cells in row 25 and in row 2 together.

Are you wanting to add together the values from the range of rows 2 down to 25, instead? If so, you'll want to put colon between the two cells to indicate that it's a range, like this -

SUM([Month 1]2:[Month 1]25)

Our Help Center has more information on referencing columns (see here).

Try this as your full formula, instead:

=IF(AND(MONTH(TODAY()) = 7, YEAR(TODAY()) = 2020), SUM([Month 1]2:[Month 1]25) * Rate25) - [Total Labor Forecast]25

Let me know if this works for you!

Cheers,

Genevieve

Cheryl Moon

Thank you, Genevieve, for looking at this for me. I am intending to multiply the cells in rows 25 and 2 together in this instance. I think I am close to resolving my problem by taking another direction, but appreciate your response to me.

Genevieve P.

Hi Cheryl,

Thank you for clarifying! The SUM function will add the cells together instead of multiply, so in this instance you can use parenthesis to indicate what should be done... try this:

=IF(AND(MONTH(TODAY()) = 7, YEAR(TODAY()) = 2020), (([Month 1]2 * [Month 1]25) * Rate25) - [Total Labor Forecast]25

Cheryl Moon

Thank you for your assistance, Genevieve. Much appreciated.

Cheryl Moon

Hi Genevieve,

If you don't mind providing some additional direction, I have another question. I have a "Total Labor Forecast" column and a "Remaining Forecast" column. I have the following formula in the "Remaining Forecast" column, which is date driven based on TODAY()). Where the month/year is 07/20 and "Month 1" is "07/20", SUM [Total Labor Forecast], if TODAY is 08/20 and "Month 1" is 07/20, SUM [Total Labor Forecast] - [M1]. What I am trying to do is to reduce the amount in the "Remaining Forecast" calculation by the amount in the "M" columns for each month that is less than TODAY()). I need to perform this calculation for 42 months x 42 dates. As you can see, the "Month" columns are text and do not represent actual dates.

Is there a better way to perform this calculation?

=IF(AND(MONTH(TODAY()) = 7, YEAR(TODAY()) = 2020, [Month 1]1 = "07/20"), SUM(([Total Labor Forecast]6), IF(AND(MONTH(TODAY()) = 8, YEAR(TODAY()) = 2020, [Month 1]1 = "07/20"), SUM(([Total Labor Forecast]6 - [M1]6), IF(AND(MONTH(TODAY()) = 9, YEAR(TODAY()) = 2020, [Month 1]1 = "07/20"), SUM(([Total Labor Forecast]6 - [M1]6) - [M2]6), IF(AND(MONTH(TODAY()) = 10, YEAR(TODAY()) = 2020, [Month 1]1 = "07/20"), SUM(([Total Labor Forecast]6 - [M1]6 - [M2]6 - [M3]6), IF(AND(MONTH(TODAY()) = 11, YEAR(TODAY()) = 2020, [Month 1]1 = "07/20"), SUM(([Total Labor Forecast]6 - [M1]6 - [M2]6 - [M3]6) - [M4]6)

Genevieve P.

Hi Cheryl,

No problem at all, I'm happy to help!

Based on this, I'm assuming you want this formula in each row, in the "Remaining Forecast" column, with the grey cell at the top being a total of what's below. Now, in your formula you only state one number to SUM, the [Total Labor Forecast] in row 6... but there's nothing to SUM it with. SUM indicates there are values to add together.

Therefore, you can use the SUM function to add together the cells that need to be subtracted off of the Total, but you don't need it to reference that first cell.

Here's an example of what the first IF statement would look like if you pasted this into row 6, the yellow highlighted cell:

=IF(AND(MONTH(TODAY()) = 7, YEAR(TODAY()) = 2020, [Month 1]1 = "07/20"), [Total Labor Forecast]@row

You'll notice I didn't need the SUM function at all, since I'm just returning the one value from that row. So then lets look at the second statement:

IF(AND(MONTH(TODAY()) = 8, YEAR(TODAY()) = 2020, [Month 1]1 = "07/20"), [Total Labor Forecast]@row - [M1]@row,

Once again, there is no SUM function needed, as you are simply subtracting one value from another. Then we get into more than one cell needing to be subtracted, and here is where we can use SUM:

IF(AND(MONTH(TODAY()) = 9, YEAR(TODAY()) = 2020, [Month 1]1 = "07/20"), [Total Labor Forecast]@row - SUM([M1]@row, [M2]@row),

This will SUM together, or ADD together the values in M1 & M2 in that row, to minus off the total.

Here's what the full formula would be:

=IF(AND(MONTH(TODAY()) = 7, YEAR(TODAY()) = 2020, [Month 1]1 = "07/20"), [Total Labor Forecast]@row, IF(AND(MONTH(TODAY()) = 8, YEAR(TODAY()) = 2020, [Month 1]1 = "07/20"), [Total Labor Forecast]@row - [M1]@row, IF(AND(MONTH(TODAY()) = 9, YEAR(TODAY()) = 2020, [Month 1]1 = "07/20"), [Total Labor Forecast]@row - SUM([M1]@row, [M2]@row), IF(AND(MONTH(TODAY()) = 10, YEAR(TODAY()) = 2020, [Month 1]1 = "07/20"), [Total Labor Forecast]@row - SUM([M1]@row, [M2]@row, [M3]@row), IF(AND(MONTH(TODAY()) = 11, YEAR(TODAY()) = 2020, [Month 1]1 = "07/20"), [Total Labor Forecast]@row - SUM([M1]@row, [M2]@row, [M3]@row, [M4]@row))))))

Finally, you'll see I used @row instead of saying row 6. This will help your formula know it only needs to look to the values in that specific row. You can then drag-fill this down that entire "Remaining Forecast" column and it will update for each individual row!

Let me know if this works for you or if you have any more questions about nested IFs or the SUM function.

Cheers,

Genevieve

Cheryl Moon

Hi Genevieve,

Thank you for the explanation above, and the approach to helping me understand the @row function. In my case, I need to extend the formula for 42 months, then the formula starts over with TODAY = 8/20 in M1, 9/20 in M1, 10/20 in M1, etc., and I think that I am hitting a character cell limit. Is there a way to build some sort of table on another sheet that the formula can reference? I am going to attempt to do that, just not certain what that will look like. I will give it a go and let you know what I come up with.

Thank you,

Cheryl