How to generate number of months for a specific year from a range of dates?

[AyxLearner]

Start Date	End Date	Months in 2021
2017-12-01	2021-08-31	8
2018-11-18	2021-11-17	11
2019-01-01	2023-12-31	12

I'm trying to generate the number of months for a particular year from a given range of dates.

Ex: I want the number of months in 2021 between the "Start Date" and "End Date" for each record to be shown in the third column "Months in 2021". What are the ways I can achieve this?

[Luke_C]

Hi @AyxLearner 

Here's one approach using the generate rows tool + summarize tool. Essentially we make one row for each day in the range, figure out what year/month they fall into, and then summarize it to get the count you're looking for. 

[Emil_Kos]

Hi @AyxLearner,

What i have done is I checked if the 4 first sings are equal to or higher 2021 and if yes I showed the middle values from the end date

[Luke_C]

@Emil_Kos 

That's a great formula approach, one potential issue I see is with the last record, what if the end date was 2023-09-31? The formula would return 9, despite the range covering all 12 months of 2021. I think the logic could be tweaked slightly to check if the end date is after 2021 to fix.

[Emil_Kos]

Hi @Luke_C,

That is accurate. I made some changes to make it work as expected:

IF [End Date]>='2022-01-01' THEN 12 ELSEIF [End Date]>='2021-01-01' THEN DateTimeDiff([End Date],'2021-01-01','month')+1 ELSE 12 ENDIF

Now it should work perfectly!

[AngelosPachis]

Hi @AyxLearner ,

Here's a different expression you can use for this (all text following a // are comments and can be ignored)

IF DateTimeYear([Start Date])=2021 AND DateTimeYear([End Date])=2021
THEN DateTimeDiff([End Date],[Start Date],"months")+1

// If both your start date and end date are within 2021 then you count the months 
//between those two dates

ELSEIF DateTimeYear([Start Date])<2021 AND DateTimeYear([End Date])=2021 THEN
DateTimeDiff([End Date],"2021-01-01","months")+1

// If the start date is not within 2021 but your end date is, you want to find the 
//difference between your end date and the start of the year (2021-01-01)

ELSE 12
// Else both start date and end date will fall outside 2021, so your months count 
//will be 12
ENDIF

Hope that helps.

Regards,

Angelos