How can I calculate the number of lost weather days in Excel?-CodePudding

I have done some programming in the past, so I would be able to figure this problem out in a programming language. But, I do not know how to proceed with accomplishing my task in an Excel spreadsheet. Any kind of guidance would be helpful because I am not familiar with anything but a basic use of MS Excel, so I don't even know which topics to search for to get some guidance on solving this problem.

Here is the breakdown for my problem:

I work for a construction company and I am keeping count of the number of days which need to be extended to a schedule deadline. All "lost weather days" are in a column (assume they are in chronological order and there are not bad dates like 2/32/22).

Contractually, we eat the first 5 lost weather days for any given month. But, if there are more than 5 lost weather days in any given month, then I need to add those days to a count by which the schedule deadline can be extended. So, any time a month has more than 5 lost weather days, I need to start counting.

For example:

DATE
1/1/23
1/2/23
1/13/23
1/14/23
1/25/23
1/26/23
1/27/23
2/1/23
2/12/23

Here, 1/6/23 and 1/7/23 need to be counted while all of the other dates are ignored because 1/1/22, 1/2/22, 1/3/22, 1/4/22, and 1/5/22 are the first five days in January 2023. So, I should have a cell with a value of 2.

Any help or guidance would be appreciated. Thank you.

I haven't tried anything specific because I am unfamiliar with excel at the moment.

CodePudding user response：

With Office 365:

=LET(rng,A2:A10,uq,UNIQUE(DATE(YEAR(rng),MONTH(rng),1)),SUM(BYROW(uq,LAMBDA(a,MAX(0,COUNTIFS(A2:A10,">="&a,A2:A10,"<"&EOMONTH(a,0) 1)-5)))))

CodePudding user response：

=LET(m,EOMONTH(TRANSPOSE(A2:A10),0),
     f,FREQUENCY(m,m),
SUM(IF(f>5,f-5)))

This first converts the dates in the same month(/year) to the same date being the end of that month. Then FREQUENCY counts the number of days being the same per month. Then, if the frequency is higher than 5, 5 is substracted, else ignored.

For older Excel versions:

=SUM( 
     IF(
        FREQUENCY(               
                  EOMONTH(TRANSPOSE(A2:A10),0),                
                  EOMONTH(TRANSPOSE(A2:A10),0)
                 )>5,
        FREQUENCY(               
                  EOMONTH(TRANSPOSE(A2:A10),0),                
                  EOMONTH(TRANSPOSE(A2:A10),0)
        )-5)
    )