# Should have seen this coming…

#### FoeHunter

##### Board Regular
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-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
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></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

### Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

#### nbrcrunch

##### Well-known Member
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:

#### Kyle82

##### New Member
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.

#### FoeHunter

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

#### FoeHunter

##### Board Regular
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

##### Board Regular
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``````

#### FoeHunter

##### Board Regular
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``````

Replies
1
Views
552
Replies
3
Views
456
Replies
3
Views
409
Replies
8
Views
304
Replies
1
Views
352

1,191,707
Messages
5,988,229
Members
440,139
Latest member
ngaicuong2017

### 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.

### Which adblocker are you using?

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

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