Should have seen this coming…

FoeHunter

Board Regular
Joined
Nov 12, 2008
Messages
236
I have a macro that I use to identify who is coming due or is overdue for training
ORIGINAL
PHP:
    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-com:eek:ffice:eek:ffice" /><o:p></o:p>
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:p></o:p>
OVERDUE ONLY
PHP:
    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:p></o:p>
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:p></o:p>
-Thank you
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
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.
 
Last edited:
Upvote 0
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.
Code:
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).

Code:
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.
 
Upvote 0
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-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p></o:p>
<o:p>Thanks again for the help.</o:p>
 
Upvote 0
The first code looked like a much simpler way to evaluate the dates and I'm going to try to use it.
PHP:
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

PHP:
    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
 
Upvote 0
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
PHP:
    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
PHP:
    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
PHP:
    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
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,342
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top