min function return earliest date from 2 other functions

[sarah1991]

Hi I have a function called getdates and getdates2 basicly they are you same function but folder Dir is different
I want to get earliest date using min function
Like =min (getdates, getdates2) will work fine if there is text file with specific date if no date found then it will return 0
When I check each function individualy one return 0 and the other a date
So how can I use min function and ignore the function that return 0 ?

Excel 2010 win 7

 

[Aladin Akyurek]

Hi I have a function called getdates and getdates2 basicly they are you same function but folder Dir is different
I want to get earliest date using min function
Like =min (getdates, getdates2) will work fine if there is text file with specific date if no date found then it will return 0
When I check each function individualy one return 0 and the other a date
So how can I use min function and ignore the function that return 0 ?

Excel 2010 win 7

Shouldn't that be MAX, not MIN?

 

[sarah1991]

Sorry I meant latest using MIN function

 

[Aladin Akyurek]

Sorry I meant latest using MIN function

Are getdates and gerdates2 functions in VBA? If so, how do you invoke them?

 

[sarah1991]

Are getdates and gerdates2 functions in VBA? If so, how do you invoke them?

yes vba functions FSGetDate,FSGetDate2


here is my code for FSGetDate theother is the same just DIR difference

Option Explicit

Sub TestFSGetDates()


Dim DateStr As String, ReturnType As String


'Dim ExeDtTime As Variant, CompDtTime As Variant
'
'If Len(ExeDtTime & "") = 0 Then
'    Debug.Print "ExeDtTime has no value"
'End If
'
'If IsNull(ExeDtTime) Then
'    Debug.Print "ExeDtTime is null"
'End If
'
'If IsEmpty(ExeDtTime) Then
'    Debug.Print "ExeDtTime is empty or not initialized"
'End If


Debug.Print


DateStr = ThisWorkbook.Worksheets("Sheet1").Range("A2") '"01-Sep-2015"
ReturnType = ThisWorkbook.Worksheets("Sheet1").Range("B1")
FSGetDates DateStr, ReturnType


Debug.Print


DateStr = "01-Sep-2015"
ReturnType = "end"
FSGetDates DateStr, ReturnType


'Debug.Print Len("======================================== ") '41
'Debug.Print Len("Execution Date Time:") '20
'Debug.Print Len("02-Sep-2015 06:01:10 AM") '23
'Debug.Print Len("------------------------- Cycle Date ") '37
'Debug.Print Len("01-Sep-2015") '11


End Sub


Function FSGetDates(DateStr As String, ReturnType As String) As Variant


On Error GoTo Err_Handler
    
    If IsDate(DateStr) Then
        DateStr = Format(DateStr, "dd-mmm-yyyy")
    Else
        FSGetDates = "DateStr '" & DateStr & "' is invalid date"
        Exit Function
    End If
    
    Debug.Print "DateStr:" & DateStr


    Dim myfile As String, LineNum As Integer, ExeLineNum As Integer, CycleDateLineNum As Integer
    Dim blExeDtTimeSet As Boolean
    
    Dim FileName As String
    Dim TempDate As String


    Dim FolderName As String, i As Integer, EntireLine As String
    Dim ExeDtTime As Variant, CompDtTime As Variant
    Dim ExeDtStartNum As Integer, CompDtStartNum As Integer
    Dim blCycleDateMatchUserDate As Boolean


    FolderName = "C:\LOGS\LOG 1"


    i = 0


    myfile = Dir(FolderName & "\" & "*.txt")


    Do While myfile <> ""


        Open FolderName & "\" & myfile For Input Access Read As #1


        While Not EOF(1)


            Line Input #1, EntireLine


            LineNum = LineNum + 1


'            Debug.Print LineNum & " - " & EntireLine


            If InStr(1, EntireLine, "Execution Date Time:") > 0 Then


'                Debug.Print i & " - " & EntireLine
'                Debug.Print InStr(1, EntireLine, "Execution Date Time:")
'                Debug.Print Mid(EntireLine, 62, 23)


                ExeDtStartNum = InStr(1, EntireLine, "Execution Date Time:") + Len("Execution Date Time:")


                If Len(ExeDtTime & "") = 0 And Not blExeDtTimeSet Then
                    ExeLineNum = LineNum
                    ExeDtTime = Mid(EntireLine, ExeDtStartNum, 23)
                End If
                
            End If


            If InStr(1, EntireLine, "Cycle Date") > 0 Then
                
                If InStr(1, EntireLine, DateStr, vbTextCompare) Then
                
                    CycleDateLineNum = LineNum
                    blCycleDateMatchUserDate = True
                    blExeDtTimeSet = True


                End If
                
            End If
            
            If LCase(ReturnType) = LCase("start") Then
                If blCycleDateMatchUserDate And blExeDtTimeSet Then
                    'If start exit do loop
                    Close #1
                    Exit Do
                End If
            End If


            If InStr(1, EntireLine, "Completed at") > 0 Then


                If blCycleDateMatchUserDate = True Then
                
                    CompDtStartNum = InStr(1, EntireLine, "Completed at:") + Len("Completed at:") + 1
                    CompDtTime = Mid(EntireLine, CompDtStartNum, 23)




                End If


            End If


        Wend


        Close #1


        myfile = Dir


    Loop


If blCycleDateMatchUserDate = True Then


    If LCase(ReturnType) = LCase("start") Then
        
        If Len(Trim(ExeDtTime & "")) > 0 And IsDate(Trim(ExeDtTime & "")) Then
            FSGetDates = ExeDtTime
        Else
            FSGetDates = "Cycle Date match User Date but Execution Date Time is missing / not valid"
        End If


    ElseIf LCase(ReturnType) = LCase("end") Then
        
        If Len(Trim(CompDtTime & "")) > 0 And IsDate(Trim(CompDtTime & "")) Then
            FSGetDates = CompDtTime
        Else
            FSGetDates = "Cycle Date match User Date but 'Completed at' Date Time is missing / not valid"
        End If


    End If


Else
    FSGetDates = "Cycle Date does not match User Date in any of the text files in " & FolderName
End If


Exit_Here:


    On Error Resume Next
    Close #1
    Debug.Print ReturnType & " - " & FSGetDates


    Exit Function


Err_Handler:
    
    FSGetDates = "Error - " & Err.Number & " " & Err.Description
    Resume Exit_Here


End Function

 

[Aladin Akyurek]

(a) Try to put the results in cells of their own and run an appropriate native function on those two cells or (b) try to modify your macros such that the "latest" date obtains. By the way, 1-Nov-2015 would be called "latest" when compoared with a date that is smaller (earlier) than 1-Nov-2015.

 

[sarah1991]

A already tested this still return 0
B Marco have case select "start" will get the earliest "end" will get the latest
hamm even =MIN(FSGetDates>0,FSGetDates) return 0

 

[Aladin Akyurek]

A already tested this still return 0
B Marco have case select "start" will get the earliest "end" will get the latest
hamm even =MIN(FSGetDates>0,FSGetDates) return 0

In which cell do these macro's output the results?

 

[sarah1991]

Case start at C2
Case end at D2
Cycle Date at B4
Function =min (FSGETDATES(B4,C2) FSGetDates2(B4,C2)) at C4

Function =max(FSGETDATES(B4,D2) FSGetDates2(B4,D2)) at D4

Max works fine

 

[Aladin Akyurek]

Case start at C2
Case end at D2
Cycle Date at B4
Function =min (FSGETDATES(B4,C2) FSGetDates2(B4,C2)) at C4

Function =max(FSGETDATES(B4,D2) FSGetDates2(B4,D2)) at D4

Max works fine

Care to post the values of C2, D2, and B4, that is, the values these cells currently house?