How to detect whether a month is present within two dates in excel-CodePudding

I am trying to figure out how to find all active consultants within a certain month (in this case june) I have the start and end date for each consultant. I want to write a formula that will return 1 or 0 in the IsInJune column depending on whether june is present within the start and end date range. Also if this is not possible in excel, could PowerBI/powerquery/DAX be used for this?

I could not think of any formulae to use for this. I am expecting to return 1 or 0 based on if june is present within that range.

CodePudding user response：

In powerquery, this would see if any period between start and end (of any year) is in June

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"start date", type date}, {"end date", type date}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "IsInJune?", each List.Contains(List.Transform({Number.From([start date])..Number.From([end date])}, each Date.Month(Date.From(_))),6))
in  #"Added Custom"

In powerquery, this would see if any period between start and lesser of (end if year, end date) is in June

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"start date", type date}, {"end date", type date}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "IsInJune?", each List.Contains(List.Transform({Number.From([start date])..List.Min({Number.From(#date(Date.Year([start date]),12,31)),Number.From([end date])})}, each Date.Month(Date.From(_))),6))
in  #"Added Custom"

CodePudding user response：

This should do the trick, assuming start date is in column A starting A2:

=IF(MONTH(A2)<=6,IF(B2-DATE(YEAR(A2),6,30)>-30,1,0),0)

Paste this in C2 and fill down as required