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.

 

Reply


Terms & Conditions