- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Jan 30, 2025 02:15 PM
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!
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Jan 30, 2025 07:10 PM
Does this look right?
DATETIME_DIFF(
DATEADD(
DATETIME_PARSE(
'0101' & DATEADD(Start, 1, 'years'),
'DDMMYYYY'
),
-1,
'days'
),
{Start},
"days"
) / 30
IF(
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!
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Jan 31, 2025 12:29 PM - edited ‎Jan 31, 2025 12:38 PM
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.
