Count Dates in a range

[Jim G]

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 G]

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 G]

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'?