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.

 

[Dave Patton]

Excel 2010
	D	E	F	G	H	I	J	K
1	Total with formula		25			Total		25
2
3			30-Jun-18			24-Aug-18		5
4			30-Jun-18			14-Aug-18		0
5			30-Jun-18					5
6			20-Jun-18					15
7
4b

Cell Formulas
Range	Formula
F1	=SUMPRODUCT(--(I3:I6-F3:F6>50),--(I3:I6>0),I3:I6-F3:F6-50)+SUMPRODUCT(--(TODAY()-F3:F6>50),--(I3:I6=0),TODAY()-F3:F6-50)
K1	=SUM(K3:K6)
K3	=((I3-F3)>50)*(I3-F3-50)
K4	=((I4-F4)>50)*(I4-F4-50)
K5	=((IF(ISNUMBER(I5),I5,TODAY())-F5)>50)*(IF(ISNUMBER(I5),I5,TODAY())-F5-50)
K6	=(((MAX(I6,TODAY())-F6)>50)*(MAX(I6,TODAY())-F6-50))

You do not need Datedif to calculate the number of days.
You can play with the above example and possibly make it more efficient.

 

[asdsparky]

I did review several samples similar to the ones you have here, however, I need to run a VBA code because I will be clearing all of the form data (except the macro button) and using copy/ paste to insert the new info every month from other users' worksheet. The tables are the same but they do not calculate the dates and I don't want to have to add the formulas each time I import new info.

 

[Dave Patton]

Thanks for the feedback.

"and I don't want to have to add the formula each time I import new info."

I appreciate your concern about adding a formula each month.

 

[MickG]

VBA as An alternative :-

[COLOR="Navy"]Sub[/COLOR] MG25Aug01
[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(Dn.Offset(, 3).Value) [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]

Thanks MickG. This did the trick. I had to tweak some of my other codes so Column K would import into a summary sheet but it does what I need.

 

[MickG]

You're welcome

 

[asdsparky]

MickG, I'm curious about another variable for this one. What if I wanted to insert a date into cell K2 and use that date instead of todays date for "Dt2"? I need to calculate out to a specific date rather than to whatever date I run the calculation. I tried using

Dt2 = .Cells (2,11).Value

and

Dt2 = Range("K2")

along with several other variances of that command but I keep getting a runtime error (different errors depending on what I type there). Any thoughts?

 

[MickG]

Perhaps this:-

[COLOR="Navy"]Sub[/COLOR] MG07Sep03
[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="Green"][B]If IsDate(Dn.Offset(, 3).Value) Then[/B][/COLOR]
            [COLOR="Navy"]If[/COLOR] IsDate(Range("k2").Value) [COLOR="Navy"]Then[/COLOR]
               '[COLOR="Green"][B] Dt2 = Dn.Offset(, 3).Value[/B][/COLOR]
                Dt2 = Range("K2").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]

Is there a way to incorporate both methods for Dt2? If there is a date entered into "K2" then Dt2 would be that value. I no date is entered, the formula would look for a date entered into (Dn.Offset(, 3).Value) before defaulting to today's date.
This should give you an idea of what I'm looking for but obviously this didn't work. I assume I cannot use the "Else" line twice for one "If/Then" function.

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
            Else
                If 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