Copy value from a cell if start and end date are in a certain month
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
-
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), "")
-
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
-
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), "")
-
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.
-
Sounds perfect!
-
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
-
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))
-
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.
-
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.
-
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.
-
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.
-
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, "")
-
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?
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
