Should have seen this coming…

[FoeHunter]

I have a macro that I use to identify who is coming due or is overdue for training
ORIGINAL

    For itm = 2 To 275
        Range("I" & itm).Value = _
            Evaluate("=IF(AND(TODAY()+30>=DATE(YEAR('Certificate Overview'!G" & itm & "),MONTH('Certificate Overview'!G" & itm & ")+0,DAY('Certificate Overview'!G" & itm & "))),CELL(""contents"",'Certificate Overview'!A" & itm & "),"""")")
    Next itm

<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Supervision liked the idea, but wanted to be able to differentiate between those who are overdue and those who are coming due. The over due part was simple.
<o></o>
OVERDUE ONLY

    For itm = 2 To 275
        Range("I" & itm).Value = _
            Evaluate("=IF(AND(TODAY()+0>=DATE(YEAR('Certificate Overview'!G" & itm & "),MONTH('Certificate Overview'!G" & itm & ")+0,DAY('Certificate Overview'!G" & itm & "))),CELL(""contents"",'Certificate Overview'!A" & itm & "),"""")")
    Next itm

<o></o>
My problem is I can’t think of a way to only identify those who’s training will expire within the next 30 days. Any help would be appreciated.
<o></o>
-Thank you

 

[nbrcrunch]

Dang! I'm not even going to try to decipher that line of code, even though it IS only a formula (in essence).

However, to get you on the right track...

If column A carried dates and I were putting mere formulas in column B to show who was coming up in 30 days, then...

=IF((A1 > = (TODAY()-30))*(A1 < TODAY()), "Within 30","")

And then copy that down for all the records.

 

[Kyle82]

This is the code I used for a similar purpose... took a slightly different approach to you, but achieved the same results.

1st one is for overdure, 2nd one is expiring in next 30 days. I think the code ur looking for is:

I had to put a 2nd IF statement in because I have a table with employee's licence's and some of them arent trained, so there are a lot of blank cells, these blank cells will pop-up with msg boxes unless that IF statement is in there.

Now it will only perform the IF check if the cells contain dates.

If IsDate(cell.Value) Then
If cell.Value >= Int(Now()) And cell.Value <= Int(Now()) + 30 Then 'put your msg box stuff here.

That gives you dates expiring after today or "Int(Now())", and before today +30(days).

For Each cell In Range("C9:P26")
If IsDate(cell.Value) Then
If cell.Value <= Int(Now()) Then MsgBox Cells(cell.Row, 1).Value & Cells(cell.Row, 2).Value & vbCrLf & Cells(8, cell.Column).Value, vbCritical + vbOKOnly, "Licence has Expired"
End If
Next

For Each cell In Range("C9:P26")
If IsDate(cell.Value) Then
If cell.Value >= Int(Now()) And cell.Value <= Int(Now()) + 30 Then MsgBox Cells(cell.Row, 1).Value & Cells(cell.Row, 2).Value & vbCrLf & Cells(8, cell.Column).Value, vbExclamation + vbOKOnly, "Licence nearing expiry Date"
On Error Resume Next
End If
Next

Hope this helps.

 

[FoeHunter]

Thank you for the advice, I'll see if I can't get them to work.

 

[FoeHunter]

It's been a long day and my brain is fried, I printed out some notes and will see if relaxing with a cold one this evening will facilitate a "EUREKA!" moment.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
<o>Thanks again for the help.</o>

 

[FoeHunter]

The first code looked like a much simpler way to evaluate the dates and I'm going to try to use it.

If IsDate(cell.Value) Then
If cell.Value >= Int(Now()) And cell.Value <= Int(Now()) + 30 Then 'put your msg box stuff here.

Right now I'm getting a #value error, but that's fine because I haven't figured the cell references for the date comparison yet

    For itm = 2 To 275
        Range("I" & itm).Value = _
            Evaluate("=IF cell.value>=int(now()) and cell.value <=(now())+30 then CELL(""contents"",'Certificate Overview'!A" & itm & "),"""")")
    Next itm

 

[FoeHunter]

Someone pointed out a MUCH simpler method for doing this and I wanted to share in case it would help someone else.
-First create a seperate tab to calculate all of the due dates then use the following scripts to filter the results

-Coming Due

    Dim DueDate As Variant
    Dim Persname As Variant
     For itm = 2 To 275
      Persname = Sheets("Due Dates-Formula").Range("A" & itm).Value
      DueDate = Sheets("Due Dates-Formula").Range("E" & itm)
        Range("I" & itm).Value = IIf(DueDate >= Now() And DueDate < Now() + 30, Persname, "")
     Next itm

-Due

    Dim DueDate As Variant
    Dim Persname As Variant
     For itm = 2 To 275
      Persname = Sheets("Due Dates-Formula").Range("A" & itm).Value
      DueDate = Sheets("Due Dates-Formula").Range("E" & itm)
        Range("I" & itm).Value = IIf(DueDate <= Now() And DueDate < Now() + 30, Persname, "")
     Next itm

-Both coming due and overdue

    Dim DueDate As Variant
    Dim Persname As Variant
     For itm = 2 To 275
      Persname = Sheets("Due Dates-Formula").Range("A" & itm).Value
      DueDate = Sheets("Due Dates-Formula").Range("E" & itm)
        Range("I" & itm).Value = IIf(DueDate <= Now() Or DueDate < Now() + 30, Persname, "")
     Next itm