Hello everyone,
I’m trying to build a monthly revenue recognition board for support and maintenance invoice reminders, that takes the overall deal value for the contract and splits it into equal monthly values for each month that is activate between the start and end dates (see screenshot):
For the purposes of this question I have removed the value calculation which is a simple {Deal Value}/{Support Months} and replaced it with a “True” & “False” values.
In the screenshot I’d expect “Item 1” to have “True” against Jan23, Feb23 and Mar23, but as you can see only Feb is set to “True”, and similar can be said for the other items.
My formulas in this version of the board are:
Support Months = ROUND(ROUND(DAYS({End Date}, {Start Date}),0)/30.4167, 0)
Jan23 = IF(AND(AND({Start Date}>"2023-01-01",{Start Date}<"2023-01-31"),AND({End Date}>"2023-01-1",{End Date}>"2023-04-31")), "True", "False")
The other months are the same as Jan23 except the hardcoded dates reflect the month in question.
In a previous version of the board I tried this formula for the month column:
IF(AND(DATE(2023,1,1)>=DATE(YEAR({Start Date}), MONTH({Start Date}), DAY({Start Date})),DATE(2023,1,1)<=DATE(YEAR({End Date}), MONTH({End Date}), DAY({End Date}))),"True","False")
This formula worked better for the end date month, but the start date months still showed as false.
I also tried converting this excel based method (worksheet function - How to split a total contract value into equal monthly values, starting from the contract start date, in Excel? - Super User) into Monday which uses the start date and duration in months, but that also gave me similar results.
I’ve also tried wrapping the date parts of the formulas in FORMAT_DATE() functions after reading a lot of posts explaining how Monday does date formulas but nothing has worked so far. So if anyone can point me in the right direction on how to get this working, that would be awesome.
Many thanks,
Alex