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
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.