15,962,733 members
See more:
I have been trying to write a UDF in Excel using the Weekday() function to no avail.
I'd like to count Saturday dates of a range of cells containing various dates in no particular order.
So something like:

VB
```Function saturdayCount (InRange)
for each cell in range
if application.worksheetfunction.weekday(cell)=7 then
count = count + 1
end if
next cell
saturdayCount = count

End Function```

Is there any hope? Any suggestions for reading?
Posted
Updated 29-Mar-12 7:17am
v2

## Solution 1

Ok, I solved my problem with this code:
VB
```Function chkSat(startCell, endCell)
Dim cell As Range
Application.Volatile
For Each cell In Range(startCell, endCell)
If IsDate(cell) And Weekday(cell) = 7 Then satCount = satCount + 1
Next cell
chkSat = satCount

End Function```

Maciej Los 29-Mar-12 13:38pm
`cell` is a MS Excel built-in object. Never use variables which names are identical as built-in objects.

To count saturdays in range of dates you don't need to write custom function. You can use formula array like this: `{=SUM(IF(WEEKDAY(\$A\$1:\$A\$366,2)=6,1,0))}`. To insert it into cell, you should use combination of CTRL+SHIFT+ENTER keys.
thewebman555 29-Mar-12 20:31pm
losmac, thanks for taking the time to reply.
You are correct in admonishing me on the use of 'cell' as a variable and I have made the correction in my code.
I could not get the suggested formula array to work correctly as the 'range' of dates is not consecutive like from 1/1/2012 to 3/31/2012.
I'll try to explain further.
Each cell can contain a date. These dates represent vacation days for an employee. They may take three days here (3/1, 3/2, 3/3) and another three here (6/16, 6/17, 6/18). I needed to determine how many of those fall on a Saturday. From what I could determine, the suggested formula deals with a defined calendar range and not a range of cells that contain out of order dates.