CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formulaarray-formulas
user15212303
user15212303

Reputation:

How to run arrayformula to find overlapping days in a month given the start and end dates?

I am trying to get the number of days in each month based on the start and end date. However, my arrayformula is not working which I suspect it to be because of indirect. Would you be kind to enlighten me on how I can do so?

view screenshot

Descriptions for code:

column Q --> start date

column R --> end date

cell U1--> value contains Mar ( in this case, I am trying to get the values for the month of March)

ARRAYFORMULA(SUMPRODUCT(--(TEXT(ROW(INDIRECT($Q$2:$Q & ":" & IF($R$2:$R="","",$R$2:$R))),"mmm")=$U$1)))

Upvotes: 0

Views: 91

Answers (1)

doubleunary
doubleunary

Reputation: 18784

Try this in cell U2:

=max( 0, min($R2, eomonth(1 & U$1, 0)) - max($Q2, datevalue(1 & U$1)) + 1 )

Format the result cell as Format > Number > Number and copy it further as required.

I think this can be written as an array formula using dmax() and dmin() but it would probably be a bit complex, and thus left as an exercise for the reader.

See this answer for an explanation of how date and time values work in spreadsheets.

Upvotes: 1

Related Questions