Excel Vba select case compare Dates and put earliest to start and recent to end

[mshome]

Hi
Windows 7 Excel 2010

what iam trying to do is
in A2 User Input Date "1-Sep-2015"

This Formula in B2 =FSGetDates(A2,B1) Function Return Should be "02-Sep-2015 06:01:10" first it should check Cycle Date if match user input Compare Dates To The right of Execution Date Time then Get earliest

This Formula in C2 =FSGetDAtes(A2,C1) Function Return Should be "02-Sep-2015 06:03:56" first it should check Cycle Date if match user input Compare Dates To The right of Completed at then Get recent

here is my code

Function FSGetDates(DateStr As String, ReturnType As String) As Date    Dim myfile As String
   Dim FileName As String
   Dim TempDate As String
  
  
   folderName = Cells(2, 11).Value
   i = 1
   myfile = Dir(folderName & "\" & "*.txt")
   Do While myfile <> ""
       Open folderName & "\" & myfile For Input Access Read As #1

       While Not EOF(1)
           Line Input #1, EntireLine
           If InStr(1, EntireLine, "Execution Date Time:") > 0 And InStr(1, EntireLine, DateStr) > 0 Then
               Select Case LCase(ReturnType)
                   Case Is = "start"
                       If IsDate(Trim(Right(EntireLine, InStr(1, EntireLine, "Execution Date Time:") - 1))) And TempDate = "" Then
                           TempDate = Trim(Right(EntireLine, InStr(1, EntireLine, "Execution Date Time:") - 1))
                       ElseIf StartDate <> "" And Trim(Right(EntireLine, InStr(1, EntireLine, "Execution Date Time:") - 1)) < TempDate Then
                           TempDate = Trim(Right(EntireLine, InStr(1, EntireLine, "Execution Date Time:") - 1))
                       End If
                   Case Is = "end"
                       If IsDate(Trim(Right(EntireLine, InStr(1, EntireLine, "Completed at:") - 1))) And TempDate = "" Then
                           TempDate = Trim(Right(EntireLine, InStr(1, EntireLine, "Completed at:") - 1))
                       ElseIf TempDate <> "" And Trim(Right(EntireLine, InStr(1, EntireLine, "Completed at:") - 1)) > TempDate Then
                           TempDate = Trim(Right(EntireLine, InStr(1, EntireLine, "Completed at:") - 1))
                       End If
               End Select
           End If
       Wend
       Close #1


       myfile = Dir


   Loop
FSGetDates = DateValue(TempDate) + TimeValue(TempDate)

End Function

sample Data

======================================== Execution Date Time:02-Sep-2015 06:01:10 AM ========================================

------------------------- Cycle Date 01-Sep-2015                  Product Z2     -------------------


xx:[xxxxx  xxxxx]          0
xx:[xxxxx xxx  xxxx]       0
xx:[xxx xxxx]              17


From Data                  17


xxxxxx x                   0
xxx xxxxxx                 6
xxxx xx   x                4
xxxxxx    x                7
xxxxxx xxxxx  x            7
------------------------------------------------------------------------------ Completed at: 02-Sep-2015 06:02:56 AM ---------------------------------------    -----------------------------
======================================== Execution Date Time:02-Sep-2015 06:03:10 AM ========================================


------------------------- Cycle Date 01-Sep-2015                  Product Z1 -------------------


xx:[xxxxx xxxx]            0
xx:[xxxxx xxx  xxxx]       0
xx:[xxx xxxx]              18


From Data                  18


xxxxxx x                   0
xxx xxxxxx                 6
xxxx xx   x                4
xxxxxx    x                7
xxxxxx xxxxx  x            7
------------------------------------------------------------------------------ Completed at: 02-Sep-2015 06:03:56 AM --------------------------------------------------------------------

my code return #VALUE!
Thanks

 

[NARAYANK991]

Hi ,

Surely instead of taking the trouble to post the code and the data , it would have made it easier for those who wish to help if you could upload the workbook , with the code and data in it.

Narayan

 

[Marc L]

Hi !

You already have the way to achieve it within your previous post !
​

 

[mshome]

Hi ,

Surely instead of taking the trouble to post the code and the data , it would have made it easier for those who wish to help if you could upload the workbook , with the code and data in it.

Narayan

here file uploaded.

Hi !

You already have the way to achieve it within your previous post !
​

i tried it but keep getting wrong date

Function FSGetDate6(CyDA As String) As Date
      Dim myfile As String, FrDA As String
    folderName$ = Cells(2, 11).Value & "\"
        myfile = Dir(folderName & "FS_QC_Count_Log_" & "*.txt")

    Do While myfile > ""
        Open folderName & myfile For Input Access Read As #1

        Do While Not EOF(1)
            Line Input #1, EntireLine$
            If FLAG% Then
                If EntireLine Like "Cycle Date *" Then
                    FLAG = 0
                    FrDA = FrDA + Split(Application.Trim(EntireLine))(2)
                End If
            ElseIf EntireLine Like "* Execution Date Time: *" Then
                FLAG = Split(Split(EntireLine, " Execution Date Time: ")(1))(0) = CyDA
            End If
        Loop

        Close #1
          myfile = Dir
    Loop
        FSGetDate6 = FrDA
End Function

that's the changes i made in your code din't work for me

 

[Marc L]

I can't see any logic in your changes …

In your previous thread, separate each Split in a variable
and check their values in Locals window in step by step mode
via F8 key : that will help you to understand how it works …
​

 

[mshome]

I can't see any logic in your changes …

In your previous thread, separate each Split in a variable
and check their values in Locals window in step by step mode
via F8 key : that will help you to understand how it works …
​

hmmmmm
previous thread we had line "From Data" always comes after "Cycle Date"
but now line "Execution Date Time:" comes before "Cycle Date"
when checking with f8 it does not flag "Execution Date Time:" from first line in first text file it flag from the next line in the same file ?

Function FSGetDate6(CyDA As String) As Long
      Dim myfile As String, FrDA As Long, TempNum As String
    folderName$ = Cells(3, 11).Value & "\"
        myfile = Dir(folderName & "FS_QC_Count_Log_" & "*.txt")

    Do While myfile > ""
        Open folderName & myfile For Input Access Read As #1

        Do While Not EOF(1)
            Line Input #1, EntireLine$
            If FLAG% Then
                If EntireLine Like "* Execution Date Time: *" Then
                    FLAG = 0
                  FrDA = Split(Application.Trim(EntireLine))(2)
           
             
                End If
            ElseIf EntireLine Like "* Cycle Date *" Then
                FLAG = Split(Split(EntireLine, " Cycle Date ")(1))(0) = CyDA
            End If
        Loop

        Close #1
          myfile = Dir
    Loop
        FSGetDate6 = FrDA
End Function
Sub Demo()
    MsgBox FSGetDate6("01-Sep-2015")
End Sub

this code returns 0

 

[Surendran Setty]

Hi,

Try attached file. I have modified the code to capture start and end date time.




I got the output as above.
When testing at your end, modify the text files folder path in cell K2 as appropriate.

Regards,
Surendran

 

[mshome]

Hi,

Try attached file. I have modified the code to capture start and end date time.

View attachment 23743


I got the output as above.
When testing at your end, modify the text files folder path in cell K2 as appropriate.

Regards,
Surendran

Thank you Verey Much Mr.Surendran
your Code Works Like Charm.

 

[Marc L]

Following the easy logic from your previous thread using Split,
burning a couple of neurones, function needs less than 30 codelines !

​

 

[Surendran Setty]

Thank you Verey Much Mr.Surendran
your Code Works Like Charm.

Welcome

Surendran

 

[mshome]

Following the easy logic from your previous thread using Split,

burning a couple of neurones, function needs less than 30 codelines !

View attachment 23764​

your code works too Thanks.

Welcome

Surendran

since i have 2 different location for the logs
i copy and pasted FSGetDates,FSGetDates2 and change the Dir
iam trying to get the date from both locations
using MIN Function
Like =Min(FSGetDates,FSGetDates2) it work if both folder have the date the function look for, but if date not found it return 0
when i check both function FSGetDates return 0 ,FSGetDates2 return date
how can i use Min Function And ignore 0 return ?

 

[Surendran Setty]

“it work if both folder have the date the function look for, but if date not found it return 0”

Since exceptions like the above are usually encountered, I used function return type as Variant.

Now I have modified the code to handle two folders. Also the function requires four arguments now as given here.

FSGetDates(DateStr, ReturnType, FolderPath1, FolderPath2)

Now to get Date1 and Date2, I have modified the code in FSGetDates which would call another function FSGetDatesFromDir(DateStr, ReturnType, FolderPath1). Based on Date1 and Date2 return values, the final return value logic has been modified.

Check the attached file. Enter both folders path in K2 and K3. I created two sample files in two different folders for testing and the code is working for me. Check at your end.

I have also optimised the code to come out if required task is met. i.e. Suppose you have 10 files, and if Start or End is found in say 4th file, files from 5 to 10 will not be processed. Also the code would run only the statements respective to Start or End.

Regards,
Surendran

 

[mshome]

“it work if both folder have the date the function look for, but if date not found it return 0”

Since exceptions like the above are usually encountered, I used function return type as Variant.

Now I have modified the code to handle two folders. Also the function requires four arguments now as given here.

FSGetDates(DateStr, ReturnType, FolderPath1, FolderPath2)

Now to get Date1 and Date2, I have modified the code in FSGetDates which would call another function FSGetDatesFromDir(DateStr, ReturnType, FolderPath1). Based on Date1 and Date2 return values, the final return value logic has been modified.

Check the attached file. Enter both folders path in K2 and K3. I created two sample files in two different folders for testing and the code is working for me. Check at your end.

I have also optimised the code to come out if required task is met. i.e. Suppose you have 10 files, and if Start or End is found in say 4th file, files from 5 to 10 will not be processed. Also the code would run only the statements respective to Start or End.

Regards,
Surendran

Start case works fine
But end case return last file completed at:
Like If I have 1 until 30 of September text files
B4 cycle date 1-Sep-2015
Start is correct time
End 30-Sep-2015 10:01:31 AM which is last file in logs folder with cycle date 28-Sep-2015

 

[mshome]

Start case works fine
But end case return last file completed at:
Like If I have 1 until 30 of September text files
B4 cycle date 1-Sep-2015
Start is correct time
End 30-Sep-2015 10:01:31 AM which is last file in logs folder with cycle date 28-Sep-2015

Ops I meant the opposite end case works fine But start it get the first file extension date and time no matter what cycle I try

 

[Marc L]

So just correct illogic condition !

And try with ♫ my way ♪ …

​

 

[Surendran Setty]

Ops I meant the opposite end case works fine But start it get the first file extension date and time no matter what cycle I try

Could you please clarify whether Cycle Date is unique to one file or it could exist in more than one file.

Could you create a sample data for 4 or 5 files in an Excel sheet with only the pertinent data as given below (which is for one file) and then explain what is actually required.

 

[mshome]

Could you please clarify whether Cycle Date is unique to one file or it could exist in more than one file.

Could you create a sample data for 4 or 5 files in an Excel sheet with only the pertinent data as given below (which is for one file) and then explain what is actually required.

View attachment 23824

sorry for the delay
it could exist in more than one file.

any cycle could be found in 1 or more text files

 

[mshome]

4th file

 

[Surendran Setty]

Check attached. In sheet 2, you can pull data for any directory which would be useful for testing. For Start, Min(Date1, Date2) and for End, Max(Date1 and Date2) is used. Even if Dir 2 has no date, the macro would work.

 

[mshome]

it works fine at home will test on Friday at work
Thank you very much for your effort and time Mr.Surendran

 

[mshome]

Mr.Surendran
is there anything i need to change if the text file cycle Date
contain 01-Sep-15 instead of 01-Sep-2015 ?

 

[mshome]

because i want the folder path to be in vba not in excel cells
like
Function FSGetDates(DateStr As String, ReturnType As String) As Variant
Dim Date1 As Variant, Date2 As Variant, FolderPath1 As String, FolderPath2 As String

then
FolderPath1 = "C:\LOGS\LOG 1" 'Dir 1
FolderPath2 = "C:\LOGS\LOG 2" 'Dir 2

then use =FSGetDates(A2,B1)

it return "01-Sep-2015 Date found in both folders"

 

[Surendran Setty]

Modify the code as below.

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

'Modify here when folder path requires change
    FolderPath1 = "C:\Users\Setty\Desktop\XL VBA\Chandoo\Dir 3" 'Dir 1
    FolderPath2 = "C:\Users\Setty\Desktop\XL VBA\Chandoo\Dir 2" 'Dir 2

    Dim Date1 As Variant, Date2 As Variant
    Date1 = FSGetDatesFromDir(DateStr, ReturnType, FolderPath1)
    Date2 = FSGetDatesFromDir(DateStr, ReturnType, FolderPath2)
  
    If IsDate(Date1) And IsDate(Date2) Then
'        Debug.Print "Date found in both folders"
        If LCase(ReturnType) = "start" Then
            FSGetDates = Format(WorksheetFunction.Min(CDate(Date1), CDate(Date2)), "dd-mmm-yyyy hh:nn:ss")
        Else
            FSGetDates = Format(WorksheetFunction.Max(CDate(Date1), CDate(Date2)), "dd-mmm-yyyy hh:nn:ss")
        End If
    ElseIf IsDate(Date1) And Not IsDate(Date2) Then
'        Debug.Print "Date found only in one folder - " & FolderPath1
        FSGetDates = Format(Date1, "dd-mmm-yyyy hh:nn:ss")
    ElseIf Not IsDate(Date1) And IsDate(Date2) Then
'        Debug.Print "Date found only in one folder - " & FolderPath2
        FSGetDates = Format(Date2, "dd-mmm-yyyy hh:nn:ss")
    ElseIf Not IsDate(Date1) And Not IsDate(Date2) Then
'        Debug.Print "Date not found in both folders"
        FSGetDates = DateStr & " is not found in both folders"
    Else
        FSGetDates = "Not known. Debug and find the error."
    End If
  
    Debug.Print "FSGetDates function output for " & ReturnType & " - " & FSGetDates

End Function

Sheet2 has been enhanced to get Start and End for all Cycle Dates in Dir 1 or Dir 2 or both.

Also for Cycle Date, the code would work for both formats dd-mmm-yy or dd-mmm-yyyy

Check attached file.

 

[mshome]

nice work
hmmmm still text file with 01-Sep-2015 it works fine but 01-Sep-15 it return "01-Sep-2015 is not found in both folders"
here test on these files
i only change the dir 1 & 2

 

[mshome]

if you put product type too in sheet 2 that would be awesome