PDA

View Full Version : total Fridays in a range



asdzxc
10-01-2013, 06:07 PM
vba returns total Fridays in A2:A6. eg A2:A6 all are Fridays, the value should be 5.

Teeroy
10-02-2013, 12:27 AM
If you are trying to count the number of Fridays in that range use the array formula:

=SUM((WEEKDAY(A2:A6)=6)*1)

confirmed with CTRL+SHIFT+ENTER.

asdzxc
10-02-2013, 12:47 AM
If you are trying to count the number of Fridays in that range use the array formula:

=SUM((WEEKDAY(A2:A6)=6)*1)

confirmed with CTRL+SHIFT+ENTER.

The result as shown on Snap3.png is not I wanted.

Teeroy
10-02-2013, 01:56 AM
What is the evaluated result? Only the formula is shown in the image.

asdzxc
10-02-2013, 03:18 AM
I want: if SUM((WEEKDAY(A2:A6) = 5 then .........

Teeroy
10-02-2013, 03:57 AM
=IF(SUM((WEEKDAY(A2:A6)=6)*1)=5,"Yes","No") CTRL+Shift+Enter
Use the output as the test in your VBA

asdzxc
10-02-2013, 05:31 AM
=IF(SUM((WEEKDAY(A2:A6)=6)*1)=5,"Yes","No") CTRL+Shift+Enter
Use the output as the test in your VBA

run macro but there is Compile error

snb
10-02-2013, 05:45 AM
A Basic course in VBA might be beneficial.

Teeroy
10-02-2013, 07:28 AM
asdzxc, what I gave you was a worksheet formula (an array formula in fact as it required C-S-E), not VBA code. You put it in a worksheet then test the Value of the range where it is located as condition for any VBA code.

Paul_Hossler
10-02-2013, 11:33 AM
Not clear if you're looking for a worksheet formula or a VBA solution to find number of Fridays in a range

the worksheet formula must be 'Array Entered', that is instead of {Enter} you need to use Control+Shift+Enter instead

You do NOT put the { .... } in yourself, Excel will do that when you do the Control+Shift+Enter

Paul

asdzxc
10-02-2013, 06:31 PM
stock data was downloaded. my macro can convert daily data into weekly data. if it is weekly data, I want conversion not to act.

Paul_Hossler
10-03-2013, 07:44 AM
asdzxc --


Your Original request #1



total Fridays in a range
vba returns total Fridays in A2:A6. eg A2:A6 all are Fridays, the value should be 5.






Latest post #11




stock data was downloaded. my macro can convert daily data into weekly data. if it is weekly data, I want conversion not to act.



I (for one) am extremely confused.

Yout#1 refers to VBA, but the example uses worksheet functions

My #10 is a simple worksheet formula to count the number of Fridays in a range, and based on your data returns the expected answer of 5

What if anything does your #11 mean to the number of Fridays?????????????????????

Paul