Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

DATETIME_DIFF between start and end date in months for different years

Topic Labels: Dates & Timezones Formulas
241 2
cancel
Showing results for 
Search instead for 
Did you mean: 
jasiri
4 - Data Explorer
4 - Data Explorer

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!

2 Replies 2

Does this look right?

Screenshot 2025-01-31 at 11.08.07 AM.png

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!

jasiri
4 - Data Explorer
4 - Data Explorer

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.