VBA Date

[JLandrum]

Im new to VBA and been trying to find dates that are greater than a specified date. Tom was not hire until 5/1/2015, he will not be qualified until 3 months after hire date. The not qualified cell would then msg NH for new hire. After the 3 months the msg would go away. I have a spreadsheet that im trying to do this to, any help would be greatly appreciated.



6/1/2015


EMPLOYEE NAME
DIV.
D.O.H.
Not Qualified


tom

5/1/2015

[mperrah]

something like this

Sub probation90()
Dim x, lr As Long

lr = Cells(Rows.Count, 3).End(xlUp).Row
For x = 2 To lr
If DateAdd("d", 90, Cells(x, 3).Value) > Now() Then
MsgBox ("Row " & x & " is NH")
End If
Next x
End Sub

[Paul_Hossler]

A worksheet formula



=IF(NOW()<=DATE(YEAR(A1),MONTH(A1)+3,DAY(A1)), "NH","OK")

[JLandrum]

I tried with no luck, what am I doing wrong. please help


EMPLOYEE NAME
DIV.
D.O.H.
Not Qualified


BENNY
BKS
7/3/1998



BRET
BKS
12/8/2009



CHARLES
BKS
4/18/2007



CHESLEY
BKS
3/25/2005



CHRIS
BKS
11/25/2008

[JLandrum]

I tried with no luck, what am I doing wrong. please help



EMPLOYEE NAME
DIV.
D.O.H.
Not Qualified


BENNY
BKS
7/3/1998



BRET
BKS
12/8/2009



CHARLES
BKS
4/18/2007



CHESLEY
BKS
3/25/2005



CHRIS
BKS
11/25/2008

[mperrah]

paste this in cell D3
if A1 has todays date
A2 is a header row
and A3 is where the data begins.
then copy down D3 to end of data, it will show "OK" if after 90 days from Today's date
or "NH" if less then 90 days from today.


=IF(NOW()<=DATE(YEAR(C3),MONTH(C3)+3,DAY(C3)), "NH","OK")
credit to paul

[mperrah]

my code is a macro you run to test the values in Column C against todays date
it will pop up a message box as you requested
and state the line number of the employee that is less then 90 days from today.


Sub probation90()
Dim x, lr As Long

lr = Cells(Rows.Count, 3).End(xlUp).Row
For x = 2 To lr ' start at row 2 if you have a header - or 3 if 2 rows before data
If DateAdd("d", 90, Cells(x, 3).Value) > Now() Then
MsgBox (cells(x, 1).Value & " is NH") ' this shows name of NH employee
End If
Next x
End Sub


to use this code:
type Alt + F11 to open vb editor
click insert in menu and choose module.
then paste my code in the right side.
type F5 to run from vb
or from excel, type Alt+F8 and click my macro "probation90"
then choose run.

[JLandrum]

Thank you

[mperrah]

Just re-read your original post.
maybe this is what you are looking for (same instructions as above)

Sub probation90()
Dim x, lr As Long

lr = Cells(Rows.Count, 3).End(xlUp).Row
For x = 2 To lr
If DateAdd("d", 90, Cells(x, 3).Value) > Now() Then
Cells(x, 4).Value = "NH"
End If
Next x
End Sub

this puts NH in column D for NH employees

-mark

[mperrah]

This might be helpful,
I added a sum count of NH employees


Sub probation90()
Dim x, lr, c As Long

lr = Cells(Rows.Count, 3).End(xlUp).Row
c = 0
For x = 2 To lr
If DateAdd("d", 90, Cells(x, 3).Value) > Now() Then
Cells(x, 4).Value = "NH"
c = c + 1
End If
Next x
MsgBox ("There are " & c & " NH employees as of " & Now())
End Sub

[mperrah]

JLandrum (http://www.vbaexpress.com/forum/member.php?56663-JLandrum), be sure to mark post as solved if we have answered you issue.

click on thread tools and choose mark as solved :)