DateDif help.

[asdsparky]

I need to compare dates between columns F and I of a spreadsheet and return the number of days in excess of 50 into column K.
Parameters:
1. Calculation only needs to happen if a date is entered into column F
2. Dates will only be entered into column F if a position is vacant.
3. Dates will only be entered into column I when a vacant position is filled.
4. Column K will be used to contain the total # of days beyond 50. (if 56 days are between vacant and filed, the result will be 6, if less than 50, no calculation is necessary)
5. If no date is entered into column I (position filled), the calculation should be based on today's date. (only if more than 50 days)
6. calculation needs to apply to 200+ rows.

I have looked at datedif function but I cannot simply fill in the cells with the calculation because all worksheet data will be replaced monthly (text and formatting). I've added an activex button that will remain on the sheet and want a VBA code to run with the push of the button.
I've also looked at several vba codes that deal with single line date comparisons but nothing that addresses my concerns.
Any assistance is greatly appreciated.

 

[MickG]

Perhaps this:-

[COLOR="Navy"]Sub[/COLOR] MG07Sep18
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, Dt1 [COLOR="Navy"]As[/COLOR] Date, Dt2 [COLOR="Navy"]As[/COLOR] Date, Num [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("F2"), Range("F" & Rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] IsDate(Dn.Value) [COLOR="Navy"]Then[/COLOR]
        Dt1 = Dn.Value
          [COLOR="Navy"]If[/COLOR] IsDate(Range("K2")) [COLOR="Navy"]Then[/COLOR]
              Dt2 = Range("K2").Value
          [COLOR="Navy"]ElseIf[/COLOR] IsDate(Dn.Offset(, 3)) [COLOR="Navy"]Then[/COLOR]
                Dt2 = Dn.Offset(, 3).Value
          [COLOR="Navy"]Else[/COLOR]
              Dt2 = Date
          [COLOR="Navy"]End[/COLOR] If
        Num = DateDiff("d", Dt1, Dt2)
        [COLOR="Navy"]If[/COLOR] Num > 50 [COLOR="Navy"]Then[/COLOR] Dn.Offset(, 5).Value = Num - 50
        Num = 0
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[asdsparky]

I figured it out MickG. Thanks for your help on this.

Private Sub CalculateDays_Click()
Dim Rng As Range, Dn As Range, Dt1 As Date, Dt2 As Date, Num As Long
Set Rng = Range(Range("F2"), Range("F" & Rows.Count).End(xlUp))
For Each Dn In Rng
    If IsDate(Dn.Value) Then
        Dt1 = Dn.Value
                If IsDate(Range("K2").Value) Then
                Dt2 = Range("K2").Value
                [COLOR=#ff0000]ElseIf[/COLOR] IsDate(Dn.Offset(, 3).Value) Then
                Dt2 = Dn.Offset(, 3).Value
                Else
                Dt2 = Date
            End If
        Num = DateDiff("d", Dt1, Dt2)
        If Num > 50 Then Dn.Offset(, 5).Value = Num - 51
        'Num = 0
    End If
Next Dn
End Sub

 

[MickG]

You've got it !!
Regds Mck

 

[asdsparky]

...apparently right before I read your response.