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

Question

Start DateEnd DateMonths in 20212017-12-01

2021-08-3182018-11-182021-11-17112019-01-012023-12-3112

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?

AngelosPachis · Answer

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

example.yxmd

Emil_Kos · Answer

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!

number of months v2.yxmd

Luke_C · Answer

@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.