I'm, trying to count dates in a column to determine the number of dates after
the current date in order to present the user with a messge to check for
overdue jobs and the number of jobs due today, when the sheet is opened or
activated. I testing the following code:

Sub CountDates()

Dim Dt As Date

Dt = Date ' todays date

With Worksheets("Jobs")
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row 'last row of job data
End With

Set StartCell = Range("Q4") ' starting range
Set Endcell = Range("Q" & LastRow) 'ending range
oDue = Application.WorksheetFunction.CountIf(Range(StartCell, Endcell), "<"
& Dt) 'counts the number of dates earlier than today

MsgBox oDue & " Jobs are overdue"

End Sub

I have ensured that the dates are entered in each cell as a date dd/mm/yyy.
The result is always 0 using "<" or ">". If text is entered in the range (a
heading) it returns a positive count with ">" but not "<". Strangley
Application.WorksheetFunction.CountIf(Range(StartCell, Endcell), Dt) counts
the correct number of dates = to Dt.

The result I'm looking for is a message "There are N jobs due today and N
jobs are overdue".

I'm using XL2007

Any ideas or other solutions
--
Jim

Thanks rylo,

I don't know how you knew that but it works.

Is there a place I should have been able to find this knowledge intuitively?

MS XL drives me nuts, but I can't live without it!!

--
Jim


"rylo" wrote:

>
> Jim
>
> In your test code try using
>
>
> Code:
> --------------------
> oDue = Application.WorksheetFunction.CountIf(Range(StartCell, Endcell), "<" & Format(Dt, "0")) 'counts the number of dates earlier than today
> --------------------
>
>
> If that works, then expand for your full requirement.
>
> HTH
>
> rylo
>
>
> --
> rylo
> ------------------------------------------------------------------------
> rylo's Profile: http://www.thecodecage.com/forumz/member.php?userid=28
> View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=29459
>
>

I seems your reply on 'thecodecage.com' has an extra line not in this forum.

The but I was refering to was knowing how to use '& Format(Dt, "0")) ' to
get the to get the code to work. I couldn't find this anywhere in Excel.

I was aware of the active sheet issue, in this case "Jobs" is always the
active sheet. However, I get point and have had a similar painful experience
and should change it.

BTW: how does this thread end up in 'thecodecage.com'?


--
Jim


"rylo" wrote:

>
> Hi
>
> Not sure which but you were referring to, but if it is making sure you
> are on the right sheet, then it was just painful experience!!!!
>
>
> rylo
>
>
> --
> rylo
> ------------------------------------------------------------------------
> rylo's Profile: http://www.thecodecage.com/forumz/member.php?userid=28
> View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=29459
>
>