All geek questions in one place

Calculate the number of specific days in a range

How can I calculate the (fractional) account of a specific day of the week in a range? For example, if the range is Monday, June 3, 12:00 PM on Tuesday June 4, 12:00 PM, and I want to count the number of Mondays, the formula will return 0.5.

I already found the formula for an integer number of days:

=SUM(INT((WEEKDAY($B2-x)+$D2-$B2)/7))

where x is the day of the week of interest (from 1 to 7). Of course, it will need to be changed in order to return the above result. How can I do it?

+4
source share
1 answer

You can get the total number of Mondays (including start and end dates) with this version (you do not need SUM)

=INT((WEEKDAY($B2-2)+INT($D2)-INT($B2))/7)

... and then adjust to take into account the start and end days, for example

=INT((WEEKDAY($B2-2)+INT($D2)-INT($B2))/7)-IF(WEEKDAY($D2)=2,1-MOD($D2,1))-IF(WEEKDAY($B2)=2,MOD($B2,1))

I really do not recommend it (it is much more resource intensive), but this version should give you the same result

=SUMPRODUCT((TEXT(B2+(ROW(INDIRECT("1:"&ROUND((D2-B2)*1440,0)))-0.5)/1440,"ddd")="mon")+0)/1440

It checks every minute in a date range to determine if it falls on Monday (assuming you don't go out for a few seconds)

+3
source

Source: https://habr.com/ru/post/1484254/

More articles:


All Articles