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

 

[rylo]

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 toda
-------------------


If that works, then expand for your full requirement.

HTH

ryl

 

[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!!

 

[rylo]

Hi

Not sure which but you were referring to, but if it is making sure yo
are on the right sheet, then it was just painful experience!!!!


ryl

 

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

 

[rylo]

Jim

1) That was just playing and, again, experience. Dates really are only
formatted numbers, so all I did was take the string and "force" it be in
the raw date number format, so it would action the correct things. Took
me a while to work it out (I don't say I'm the first, and I've probably
seen it somewhere else, but don't specifically remember!!!).

2) I don't know. I'll see if I can find an answer somewhere.


rylo

 

[Simon Lloyd]

Gents, the posts on the newsgroups are imported to The Code Cage so folk
can see all the posts in one place with an easy to follow/read
interface. Posts to the newsgroups come from a variety of other forums
too, when a post is made in a participating forum it is also posted to
the newsgroup, likewise if a newsgroup post is answered in the forum it
too is posted to the newsgroup which is why it appeared that Rylo
replied from The Code Cage a forum dedicated to help with Microsoft
Office applications.


--
Simon Lloyd

Regards,
Simon Lloyd
'www.thecodecage.com' (http://www.thecodecage.com)