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

Options

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


Best Answers

Answers

  • Cheryl Moon
    Options

    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.

  • Cheryl Moon
    Options

    Thank you for your assistance, Genevieve. Much appreciated.

  • Cheryl Moon
    Options

    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.
    Genevieve P. Employee Admin
    Options

    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
    Options

    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!