VBA coding to calculate bonus based off dates

[kolbm18]

Still learning VBA but trying to apply it in order to save future time.

I'm setting up a program for employees to determine if they are due for a bonus at the end of the month. Our company pays a $100 bonus every month for every employee who has been working with us for over six months.

So far I have created a pop up message box to ask for the current date because I think that would be the first step.

My table looks like this...

Employee Hire Date
E1 1-Aug-2015
E2 14-Sep-2014
E3 5-Jan-2015
E4 12-Nov-2015
And so on...

I would appreciate any help! Please let me know if I can help clarify anything.

 

[akmatz]

Assuming first Employee is in Column A cell A2, Hire Date is in Column B cell B2; enter this formula in Cell C2 on the same row 2:

=IF(DATEDIF(B2,TODAY(),"M")>6,100*DATEDIF(B2,TODAY(),"M"),0)

 

[kolbm18]

Assuming first Employee is in Column A cell A2, Hire Date is in Column B cell B2; enter this formula in Cell C2 on the same row 2:

=IF(DATEDIF(B2,TODAY(),"M")>6,100*DATEDIF(B2,TODAY(),"M"),0)

Thank you for the help! However, I would like to create code to run through vba instead of inputting a function into the spreadsheet. Any help there?

 

[Biz]

Try code below.
Used akmatz formula in VBA

Sub Test()Call SpeedOn
Range("C2:C" & Cells(Rows.Count, "A").End(xlUp).Row).Formula = "=IF(DATEDIF(B2,TODAY(),""M"")>6,100*DATEDIF(B2,TODAY(),""M""),0)"
Call SpeedOff
End Sub
Private Sub SpeedOn()
    'Speeding Up VBA Code
    With Application
        .ScreenUpdating = False 'Prevent screen flickering
        .Calculation = xlCalculationManual 'Preventing calculation
        .DisplayAlerts = False 'Turn OFF alerts
        .EnableEvents = False 'Prevent All Events
    End With
End Sub
Private Sub SpeedOff()
    'Speeding Up VBA Code
    With Application
        .ScreenUpdating = True 'Prevent screen flickering
        .Calculation = xlAutomatic 'Preventing calculation
        .DisplayAlerts = True 'Turn OFF alerts
        .EnableEvents = True 'Prevent All Events
    End With
End Sub

 

[kolbm18]

Still not quite working. To be clear, the column I'm outputting the information to is the bonus they should receive. So it will show up as "100" if the employee has been working for more than 6 months or "0" if they have been working for less than 6 months.

 

[kolbm18]

Assuming first Employee is in Column A cell A2, Hire Date is in Column B cell B2; enter this formula in Cell C2 on the same row 2:

=IF(DATEDIF(B2,TODAY(),"M")>6,100*DATEDIF(B2,TODAY(),"M"),0)

I got this to work but I need to be able to enter in a date using a message box, not only using the actual current date.

This is the coding I have so far:

Sub InputBoxDate ()

Dim syntax As Date
Syntax = InputBox("Enter Today's Date", , "12/9/2015")

End Sub

So how do I get that date that gets entered in the message box to be considered "todays date" so that I can know if a employee should receive a bonus on any date past or future?

 

[kolbm18]

Try code below.
Used akmatz formula in VBA

Sub Test()Call SpeedOn
Range("C2:C" & Cells(Rows.Count, "A").End(xlUp).Row).Formula = "=IF(DATEDIF(B2,TODAY(),""M"")>6,100*DATEDIF(B2,TODAY(),""M""),0)"
Call SpeedOff
End Sub
Private Sub SpeedOn()
    'Speeding Up VBA Code
    With Application
        .ScreenUpdating = False 'Prevent screen flickering
        .Calculation = xlCalculationManual 'Preventing calculation
        .DisplayAlerts = False 'Turn OFF alerts
        .EnableEvents = False 'Prevent All Events
    End With
End Sub
Private Sub SpeedOff()
    'Speeding Up VBA Code
    With Application
        .ScreenUpdating = True 'Prevent screen flickering
        .Calculation = xlAutomatic 'Preventing calculation
        .DisplayAlerts = True 'Turn OFF alerts
        .EnableEvents = True 'Prevent All Events
    End With
End Sub

I got this to work but I need to be able to enter in a date using a message box, not only using the actual current date.

This is the coding I have so far:

Sub InputBoxDate ()

Dim syntax As Date
Syntax = InputBox("Enter Today's Date", , "12/9/2015")

End Sub

So how do I get that date that gets entered in the message box to be considered "todays date" so that I can know if a employee should receive a bonus on any date past or future?