Date range issues

[lanhao]

Hi again,

I was wondering, how would I put into VB a method of having only certain rows counted if they are within the past year (from the present date listed in the system).

Basically i need it to just look for the info that is within the past year (it's for an attendance tracker basically)

Any help/suggestions would be greatly appreciated

[mdmackillop]

Can you post a sample layout of your data?

[lucas]

This might get you started:
[VBA]
Option Explicit
Sub GetData()
Dim i As Long
Dim cPast As Long
'change the 1 to Cells to any number to start on a different row
For i = 1 To Cells(Rows.Count, "A").End(xlUp).Row
If Cells(i, "A").Value < Date Then
cPast = cPast + 1
End If
Next i
Range("B3").Value = "Past dates = " & cPast
End Sub
[/VBA]

[lanhao]

ok, the way the layout is in this sheet as a temp, it has to count the amount of hours spent within the past year, anything past it won't count towards the number in question.

[lucas]

certain rows counted if they are within the past year (from the present date listed in the system).

I don't understand your layout.....its based on hours missed?? What are you trying to accomplish?

Take a look at autofilter......

[mdmackillop]

I don't understand your layout.....its based on hours missed?? What are you trying to accomplish?

Agreed.

[lanhao]

the first parto f this is done and simple, it will automatically pull up the information in the database of the information for the repname in question. What i need it to do is pull up all that information, but only COUNT the hours missed within the past year - since that is what determines how they are with the attendance policy

[lanhao]

Sorry about that - been really hecitc at work today. What i need to have done, is have it check the dates of information that are pulled up from the database. From there, it adds the total number of hours missed by an employee within the past year. (This is a tracking method used by some of my bosses to see who is over a certain number of hours). Having it have all the dates missed by the rep is fine, but only the last year counts for certain things that the higher ups deal with.

[lucas]

Like this?

[mdmackillop]

Hi Steve,
To limit the data to the past year, you'll need to include the early date as well
[VBA]
If Cells(i, "B").Value < Date And Cells(i, "B").Value >= Date - 365 Then

[/VBA]

[lucas]

Thanks Malcolm, that works great......I didn't see any dates before a year on lanaho's template and I'm still not positive I understand what he's trying to do...

[lanhao]

Actually it's a little different than that - what it needs to do is add the total number of hours missed for the days within the range of dates listed, not counted (Though the counting thing you put together solved an entirely different issue for me on a different thing I was going to work on).

So if there were three days within the past year a person was not here, and it was 8 hrs each day - it would come back with the result of 24 hrs.

Thanks veyr much for the help , it definitely is giving me an idea of where to go with this.