Counting the number of days in specific months from date range. Google sheets-CodePudding

I am trying to make a google sheet where it will automatically calculate how many days a device was in repair per month.

I need it to calculate the dates in columns and to post the results in a different collumn rows since there will be a list which will constantly have new entries

Ex: Device| start date | end date.

Laptop| 8/29/2021 | 9/10/2021. 2 days in August, 10 days in September

The desired result would it be in 2 columns 1 for month 1, column 2 for month 2

I managed to find this formula: =ArrayFormula(query({EOMONTH(row(indirect("A"&A2):indirect("A"&B2)),0),row(indirect("A"&A2):indirect("A"&B2))},"Select Col1,Count(Col2) group by Col1 label Col1 'Month', count(Col2)'Days in Month' format Col1 'MMMM-YYYY' "))

but i cannot use it in a list sheet. Any ideas would be appreciated

CodePudding user response：

Perhaps this is easiest to do by using sequence() to enumerate the dates, format them as month names only with text(), and aggregate the data with query(), together with some string manipulation to split the data into columns, like this:

=arrayformula( 
  split( 
    join( 
      "", 
      flatten( 
        query( 
          " days in " & text(sequence(C2 - B2   1, 1, B2), "mmmm→"), 
          "select count(Col1), Col1 group by Col1 label count(Col1) '' ", 
          0 
        ) 
      ) 
    ), 
    "→" 
  ) 
)

CodePudding user response：

If you can have one formula for each month, then this will work.

FIRST OUTPUT COLUMN

In your example, column B would be the start date and column D would be the output:

=ARRAYFORMULA(
  EOMONTH(B2:B, 0)-B2:B &" days in " & Text(B2:B, "mmmm")
)

Explanation - EOMONTH() Gives you the last date of the selected month. So this works by getting the last day of the month, subtracting the start date giving you the number of days for that month.

To get the text word value of a month, you use the TEXT() function to convert the data into a month. 'mmmm' gives August, 'mmm' gives you Aug, and 'mm' returns the number of the month.

Then you concatenate those together with the 'days in' text, and wrap it in a ARRAYFORMULA().

SECOND OUTPUT COLUMN

Similar to the first, but the math is a bit different.

=Arrayformula(
  Text(C2:C, "dd") & " days in " & Text(C2:C, "mmmm")
)

Explanation - Here you get the number day of the month. Since the number of days in September is equal to the date. So, you get the number of the month using TEXT() and 'dd', and concatenate it with your text and the month's text value.