How many sales days in a month

[hillmas83]

i am looking for a way of counting how many sales days in any month, assuming the below information

[TABLE="width: 166"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Day [/TD]
[TD] Value[/TD]
[/TR]
[TR]
[TD]Sunday[/TD]
[TD="align: right"]0.125[/TD]
[/TR]
[TR]
[TD]Monday[/TD]
[TD="align: right"]1.000[/TD]
[/TR]
[TR]
[TD]Tuesday[/TD]
[TD="align: right"]1.000[/TD]
[/TR]
[TR]
[TD]Wednesday[/TD]
[TD="align: right"]1.000[/TD]
[/TR]
[TR]
[TD]Thursday[/TD]
[TD="align: right"]1.000[/TD]
[/TR]
[TR]
[TD]Friday[/TD]
[TD="align: right"]1.000[/TD]
[/TR]
[TR]
[TD]Saturday[/TD]
[TD="align: right"]0.250[/TD]
[/TR]
[TR]
[TD]Bank Holiday[/TD]
[TD="align: right"]0.250[/TD]
[/TR]
</tbody>[/TABLE]

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

 

[Joe4]

Welcome to the Board!

I would use a formula with NETWORKDAYS, which counts the number of workdays (Monday-Friday) between two dates, taking holidays into account.
So, if your have the first day of the month in cell A1, and a named range called "Holidays" which contains a listing of your holidays, then these formulas should work:
First, find the End of Month of the month in cell A1 in cell B1 with this formula:

=EOMONTH(A1,0)

Then, here is the big formula.

=[COLOR=#ff0000]NETWORKDAYS(A1,B1,Holidays)[/COLOR]+[COLOR=#0000ff]((B1-A1-NETWORKDAYS(A1,B1,Holidays)+1)*0.25)[/COLOR]-[COLOR=#008000](SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1 & ":" & B1)))=1))*0.125) [/COLOR]

Note that are three parts to my formula. The first counts the Workdays. The second counts the weekends and holidays, and multiplies by 0.25. The third finds the Sundays, and subtracts an additional 0.125 (since Sundays should be 0.125 and not 0.25).

See here if you need to learn more about NETWORKDAYS function: MS Excel: How to use the NETWORKDAYS Function (WS)

 

[hillmas83]

Perfect! - You're a star!

many thanks

 

[Joe4]

You are welcome!
Glad I was able to help.