Using Date functions in Excel UDF

Question

0.00/5 (No votes)

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 28-Mar-12 17:18pm

thewebman555

Updated 29-Mar-12 7:17am

Maciej Los

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

thewebman555 · Accepted Answer · 2012-03-28T22:38:00

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

Posted 28-Mar-12 22:38pm

thewebman555

Comments

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.