i am looking for a way of counting how many sales days in any month, assuming the below information
<colgroup><col><col></colgroup><tbody>
</tbody>
so, using the above August 2017 would equal 23.75 (with the UK bank holiday on 28/08).
I need to work out the sales days in each month of each year ongoing.
I was thinking of using some sort of formula such as -
SUMPRODUCT(N(TEXT(ROW(INDIRECT(A1&":"&EOMONTH(A1,0))),"ddd")="Sun"))*0.125
(where A1 refers to the first day in the given month) to count the sundays +saturdays etc but i cant figure out how to account for the bank holidays.
thanks
M
Day | Value |
Sunday | 0.125 |
Monday | 1.000 |
Tuesday | 1.000 |
Wednesday | 1.000 |
Thursday | 1.000 |
Friday | 1.000 |
Saturday | 0.250 |
Bank Holiday | 0.250 |
<colgroup><col><col></colgroup><tbody>
</tbody>
so, using the above August 2017 would equal 23.75 (with the UK bank holiday on 28/08).
I need to work out the sales days in each month of each year ongoing.
I was thinking of using some sort of formula such as -
SUMPRODUCT(N(TEXT(ROW(INDIRECT(A1&":"&EOMONTH(A1,0))),"ddd")="Sun"))*0.125
(where A1 refers to the first day in the given month) to count the sundays +saturdays etc but i cant figure out how to account for the bank holidays.
thanks
M