Date Problem in VBA for Excel

[Porschemaniac]

Hi there,

OK, I'm a bit new to VBA - and new to this forum - so please be gentle!!!

Currently, I am building an Excel spreadsheet to manage training records.

At the moment, I am trying to put together a routine, which interrogates a list of 'Date of completion' dates (relating to the dates various courses were completed) and then returns a simple count of how many courses have been completed during the last 30 days.

The dates are stored in a sheet called 'Providers Mapping' and I am running the count routine from a sheet, called 'Stats', using a command button.

I have put together the following for this:

Private Sub CommandButton1_Click()
Dim Cell As Range
Dim CurDate As Date
Dim ctr As Integer
Dim ctra As Integer
Dim CellDate As Date
Dim Cellct As Integer

CurDate = Date

For Each Cell In Worksheets("Providers Mapping").Range("I3:I394")
Cellct = Cellct + 1
CellDate = ActiveCell.Value
Debug.Print "Active Cell Value " & CellDate & "Todays Date is " & CurDate
If CurDate - CellDate <= 30 Then
ctr = ctr + 1
End If
If CurDate - CellDate >= 30 Then
ctra = ctra + 1
End If
Next Cell

Worksheets("Stats").Range("B6") = "Courses Marked Complete during the last 30 days " & ctr

Worksheets("Stats").Range("B7") = "Courses Marked Complete More than 30 days ago " & ctra

Worksheets("Stats").Range("B11") = "Cells Checked " & Cellct

Worksheets("Stats").Range("B14") = "Todays Date is " & CurDate
End Sub


The problem is that the count of courses completed during the last 30 days is always 0.

As you can see, I have added a Debug.Print function and this confirms that the dates being returned from the 'Providers Mapping' sheet are all coming back as 00:00:00.

The dates in the 'Providers Mapping' sheet are all cortrectly formatted as dates, so I really am at a loss as to explain what is going on.

Any help that anyone can offer me on this will be greatly appreciated.

Thanks,

Ian.

 

[SkipVought]

Hi,

Why are you using VBA to count records? This can be done quite easily on the sheet with simple worksheet functions.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Porschemaniac]

Yes - that was my first thought.

Tried to do it with COUNTIFs, but couldn't seem to get that to work. Also wanted to assign the update action to a button - so VBA seemed like the way to go.

As I say, I'm quite new to this, so I'll be grateful for any guidance.

Cheers.

 

[SkipVought]

As you can see, I have added a Debug.Print function and this confirms that the dates being returned from the 'Providers Mapping' sheet are all coming back as 00:00:00.

Probably not REAL dates, rather TEXT.



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

In the OTHER sheet containing the "dates" select the COLUMN and change the cell format to GENERAL. (This does not change the underlying DATA, just the DISPLAY)

If your "dates" display change to NUMBERS, you have REAL DATES. Otherwise the data is just TEXT and will need to be converted. This can simply be done using Data > Text to columns

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Porschemaniac]

OK, so maybe the question is: why would it return text from cells that are formatted as dates?

 

[Porschemaniac]

Thanks - think my reply must have crossed with your - I'll give that a try.

Cheers.

 

[Porschemaniac]

OK, Gave that a try. Indeed, they did convert to numbers, so are real dates. Very confused now!!!

 

[SkipVought]

do not use ActiveCell.

rather

    For Each cell In Worksheets("Providers Mapping").Range("I3:I394")
        Cellct = Cellct + 1
        CellDate = [b]cell[/b].Value
        Debug.Print "Active Cell Value " & CellDate & "Todays Date is " & CurDate
        If CurDate - CellDate <= 30 Then
        ctr = ctr + 1
        End If
        If CurDate - CellDate >= 30 Then
        ctra = ctra + 1
        End If
    Next cell

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Porschemaniac]

Thanks Skip - worked like a charm.

Greatly appreciate your help.

Cheers,

Ian.

 

[SkipVought]

But really here's all you need
[tt]
Cnt>=30: =COUNTIF('Providers Mapping'!I3:I394,">=30")
Cnt<=30: =COUNTIF('Providers Mapping'!I3:I394,"<=30")

Cnt: =COUNT('Providers Mapping'!I3:I394)
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Porschemaniac]

Thanks for that Skip. Just out of curiosity, how does the COUNTIF do the compare to establish if the date is more that 30 days old?

The only way that I could think of doing it was to compare to today's date - and that's the part I didn't seem to be able to do with COUNTIFs.

Thanks again,

Ian.

 

[SkipVought]

duh! Sorry


[tt]
=COUNTIF('Providers Mapping'!I3:I394,">="&TODAY()+30)
=COUNTIF('Providers Mapping'!I3:I394,"<="&TODAY()+30)
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}