Skip to main content

Hi all,

I have some entries in my data table for multi-year contracts. I have one field that shows the number of months the contracts span (using the formula (DATETIME_DIFF({End}, {Start}, "days"))/30). I used days instead of months because if a contract ran from 1/1/24 to 12/31/25, it would count that as 11 months.

I also need to know the total of months in every year of the contract (e.g. 12 months in 2024, 12 months in 2025, 6 months in 2026). Can anyone help?

Thank you!

Does this look right?

DATETIME_DIFF( DATEADD( DATETIME_PARSE( '0101' & DATEADD(Start, 1, 'years'), 'DDMMYYYY' ), -1, 'days' ), {Start}, "days" ) / 30IF( YEAR(Start)!= YEAR(End), DATETIME_DIFF( {End}, DATETIME_PARSE( '0101' & DATETIME_FORMAT(End, 'YYYY'), 'DDMMYYYY' ), "days" ) / 30 )

And I've created it for you here!

Thank you - that worked very well, but there was one issue:

Not all of the contracts start in the same year and I actually need the columns to be "months in 2024", "months in 2025", "months in 2026" (rather than first year, second year, etc.) 

As of right now, I have some date ranges in there that are, for instance, 7/1/2023-6/30/2024, so I need the columns to say 6 months in 2024 and 0 or NaN in 2025. With the current formula, it says 6 months in 2024 and 6 months in 2025.

 

Terms & ConditionsAccessibility statement