Copy value from a cell if start and end date are in a certain month

Options

I am trying to copy the "Contract Value" number to the January, February etc cell if the start and end date are between the corresponding month.

E.g The first row shows a start date of 06/01/22 (Australian date format DD/MM/YY) and end date 31/03/22. This should copy the contract value of $417,168.62 to the January, February and March cells.


Best Answers

  • KPH
    KPH ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi @Benji241

    How about a SUMIFS formula like this:

    =SUMIFS([Contract Value]:[Contract Value], [Start Date]:[Start Date], MONTH(@cell) <= 1, [End Date]:[End Date], MONTH(@cell) >= 1)

    This sums the Contract value column of all rows where the month of the start date is equal to or before month 1 and the month of the end date is equal to or after month 1.

    You would change the 1 (in bold) to 2 for February, 3 for March, etc.

    This will result in an error if either date column is blank so you might want to wrap it in an IFERROR, like this:

    =IFERROR(SUMIFS([Contract Value]:[Contract Value], [Start Date]:[Start Date], MONTH(@cell) <= 1, [End Date]:[End Date], MONTH(@cell) >= 1), "")

  • Benji241
    Benji241 ✭✭
    Answer ✓
    Options

    Ok I think I have solved this. It was the dates that were the wrong way around. This is the formula that worked for me. (so far anyway)

    =IF(AND($[Start Date]@row <= DATE(2023, 1, 31), $[End date]@row >= DATE(2023, 1, 1)), $[Contract Value]@row, "")

Answers

  • KPH
    KPH ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi @Benji241

    How about a SUMIFS formula like this:

    =SUMIFS([Contract Value]:[Contract Value], [Start Date]:[Start Date], MONTH(@cell) <= 1, [End Date]:[End Date], MONTH(@cell) >= 1)

    This sums the Contract value column of all rows where the month of the start date is equal to or before month 1 and the month of the end date is equal to or after month 1.

    You would change the 1 (in bold) to 2 for February, 3 for March, etc.

    This will result in an error if either date column is blank so you might want to wrap it in an IFERROR, like this:

    =IFERROR(SUMIFS([Contract Value]:[Contract Value], [Start Date]:[Start Date], MONTH(@cell) <= 1, [End Date]:[End Date], MONTH(@cell) >= 1), "")

  • Benji241
    Options

    Thanks. I actually only needed the individual cells though so I edited it slightly to be =IFERROR(SUMIFS($[Contract Value]@row, $[Start Date]@row, MONTH(@cell) <= 1, $[End date]@row, MONTH(@cell) >= 1), "") and i added the $ to make the cell references absolute.


  • KPH
    KPH ✭✭✭✭✭✭
    Options

    Sounds perfect!

  • Benji241
    Benji241 ✭✭
    edited 12/13/23
    Options

    Actually, there is an issue with this. When it goes into the next year meaning that you have a project starting in say the 8th month and finishing in the 2nd month of the next year it screws up the less than and greater than calculations. Any ideas? Also i plan to continue the months onto the next year as shown below. i.e when it gets to December 2023 it will keep going to January 2024



  • KPH
    KPH ✭✭✭✭✭✭
    Options

    Rather than summing the contract value if the start and end date are between the corresponding month, you could specify a start and end date and sum the contract values for contracts that cover that period.

    Just switch out the MONTH(@cell) <= 1 criteria for the start date @cell <= DATE(2023, 1, 1) and the same for the end.

    The formula would be

    =SUMIFS([Contract Value]:[Contract Value], [Start Date]:[Start Date], @cell <= DATE(2023, 1, 1), [End Date]:[End Date], @cell >= DATE(2023, 1, 31))

  • Benji241
    Options

    This doesn't work unfortunately unless the start and end date are on the same month which is never the case.

    See attached screenshots. One has the start and end date in January and the other has the start date in January but end date in February.


  • KPH
    KPH ✭✭✭✭✭✭
    Options

    You have the < and > mixed up. The start date should be before or on Jan 1 and the end day after or on Jan 31. That will capture contracts that span the month. It will not capture contracts that start and finish within January, but as you say, that is never the case.

  • Benji241
    Options

    I have tried it both ways a thousand times. If I reverse them it does nothing regardless of what month I use for the end date.


  • Benji241
    Options

    I replied to this earlier but the post has gone missing. Switching them around does nothing no matter what month i use for the date. I tried switching the less than and greater than operators around a thousand different ways.

    Also to add to the confusion, i got it to work using :

    =IF(AND($[Start Date]@row <= DATE(2023, 1, 1), $[End date]@row >= DATE(2023, 1, 31)), $[Contract Value]@row, "")

    but this only works in January if the date is set to the 1st of January. If i change it to the 2nd of January it doesn't work.


  • Benji241
    Benji241 ✭✭
    Answer ✓
    Options

    Ok I think I have solved this. It was the dates that were the wrong way around. This is the formula that worked for me. (so far anyway)

    =IF(AND($[Start Date]@row <= DATE(2023, 1, 31), $[End date]@row >= DATE(2023, 1, 1)), $[Contract Value]@row, "")

  • KPH
    KPH ✭✭✭✭✭✭
    Options

    I've mocked up a copy of your sheet:

    The formula worked as expected. I have changed it slightly to account for the very first row which now starts and ends within January 2023. Something I didn't think was possible before. But if this is possible, then this version accounts for it.

    =SUMIFS($[Contract Value]@row, $[Start Date]@row, @cell <= DATE(2023, 1, 31), $[End date]@row, @cell >= DATE(2023, 1, 1))

    The IF AND version also works as expected:

    =IF(AND($[Start Date]@row <= DATE(2023, 1, 1), $[End date]@row >= DATE(2023, 1, 31)), $[Contract Value]@row, "")

    Can you check if there is a regional difference for the DATE function? When you type in =DATE( does smartsheet ask for year, month, day, like this?


  • Benji241
    Options

    Yes it asks for the date in US format which I am following.

    I have it working using my method so I am going to leave it for now. Thanks a lot for your help

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!