Good evening
I would need to modify a formula I use that counts on Saturdays even on holidays.
In the example attached to B1 I choose the month
I should count the max between columns E and F that contain at least one number 1
on Saturdays + holidays.
The result with just the Saturdays in cell E33 (4) I found I need the one in F33 including
Saturdays (4) + 2 Holidays Day 1 and Day 6 as from Holiday List in Column J.
Thank you
Hi Berna11,
not sure if I fully understand what you need, but I think these formulas should help.
Formula for E33 (counts the number of saturdays in the selected month)
Formula:
{=SUM(--(IFERROR(WEEKDAY(B4:B30,2),0)=6))}
Formula for F33 (counts the number of saturdays and holidays in the selected month)
Formula:{=SUM(--(MONTH(J4:J15)=MONTH(B1)))}
Notice that the formulas are enclosed in brackets ({}). This means you need to close the formula by pressing [ctrl]+[shift]+[enter] simultaneously.
Cheers!
Tsjallie
--------
If your problem is solved, pls mark the thread SOLVED (see Thread Tools in the menu above). Thank you!
If you think design is an expensive waste of time, try doing without ...
i wasn't 100% sure about your requirement. but, is this formula correct??
=NETWORKDAYS.INTL(B1,EOMONTH(B1,0),"1111101")+COUNTIFS($J$4:$J$15,">="&$B$1,$J$4:$J$15,"<="&(EOMONTH($B$1,0)))
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
thank you Tsjallie, Glenn K
for your answer but, you both have neglected a particular one.
I try to explain better I do not just want to count how many Saturdays and holidays
there are in the attached sheet but, I have to count the sabbaths and holidays of the
sheet that have in column E-F a n.1
In the example if we remove the n.1 in E28 in F28 and then empty the result F33
From 6 passages to 5.
I hope it is clearer.
Thanks again
I attach you other files to clarify better
The account to be made is:
Total = 4
E4 Holidays E9 o F9 Holidays E10 Saturday F28 Saturday
Also my formula in E33 is wrong from 1 instead of 2 Saturday.
Last edited by Berna11; 04-26-2017 at 02:35 AM.
Hope I get it now
Formula for cell F40:
Formula:
{=SUM(SUMPRODUCT(--(--(E4:E30=1)+--(F4:F30=1)>0),--(COUNTIF(J4:J15,B4:B30))),
SUMPRODUCT(--(--(E4:E30=1)+--(F4:F30=1)>0),--(IFERROR(WEEKDAY(B4:B30,2),0)=6)))}
First part calculates the dates which have 1 in column E or F and are a holiday.
Second part calculates the dates which have 1 in column E or F and are a saturday.
The results of both are then added together.
Close the formula with [ctrl]+[shift]+[enter]
Great, good,
That's what I was looking for
Thanks endless
A greeting to the next
You're welcome. Thx for the rep
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks