Solved

need to edit script to check for fridays and bankholidays

Posted on 2016-08-10
26
40 Views
Last Modified: 2016-08-16
Hi I have a script which change the date to next working date i need some amendments if possible please.

Option Explicit

' ==============================================================================
' C O N S T A N T S   &   V A R I A B L E S
' ==============================================================================

' Define needed constants
Const ForReading = 1
Const ForWriting = 2
Const TriStateUseDefault = -2

' Define global variables
Dim objFSO
Dim strCheckFolder

' Specify path to folder of files to process
strCheckFolder = "D:\LABELS\ChessODBCFiles\BedfordSO"

' ==============================================================================
' I N I T I A L I Z A T I O N
' ==============================================================================

' Create filesystem object
Set objFSO = CreateObject("Scripting.FileSystemObject")

' ==============================================================================
' M A I N   L O G I C
' ==============================================================================

' Process the base folder
ProcessFolder strCheckFolder

' ==============================================================================
' W R A P U P
' ==============================================================================

' Done, cleanup and exit
Wscript.Quit

' ==============================================================================
' S U B R O U T I N E S   &   F U N C T I O N S
' ==============================================================================

Sub ProcessFolder(strCheckFolder)

   ' Define local variables
   Dim objCheckFolder
   Dim objCheckFile

   ' Make sure the folder exists
   If Not objFSO.FolderExists(strCheckFolder) Then
      Wscript.Echo "*ERROR* Folder [" & strCheckFolder & "] does not exist, quitting."
      Wscript.Quit
   End If

   ' Access the folder
   Set objCheckFolder = objFSO.GetFolder(strCheckFolder)

   ' Look at all files in this folder, process each one
   For Each objCheckFile In objCheckFolder.Files
      ProcessFile objCheckFile
   Next

End Sub

Sub ProcessFile(objCheckFile)

   ' Define local varaibles
   Dim objFile
   Dim strData
   Dim arrLines
   Dim arrFields
   Dim blnFileChanged
   Dim i

   ' Read the file contents into an array for processing
   Set objFile = objFSO.OpenTextFile(objCheckFile.Path, ForReading, False, TriStateUseDefault)
   strData = objFile.ReadAll
   arrLines = Split(strData, vbCrLf)
   objFile.Close
   Set objFile = Nothing

   ' Assume no changes needed to this file
   blnFileChanged = False

   ' Look at each line of the file and check the date field
   For i = 0 To UBound(arrLines)

      ' Split the fields of this line apart into an array (comma delim)
      arrFields = Split(arrLines(i), ",")

      ' Make sure we found at least 7 columns (array indexes are 0 based...)
      If UBound(arrFields) > 5 Then
         ' Check if 7th column is in the future, if not set it to tomorrows date
         If DateDiff("d", Now, StringToDate(Trim(arrFields(6)))) < 1 Then
            arrFields(6) = DateToString(DateAdd("d", 1, Now))
            arrLines(i) = Join(arrFields, ",")
            blnFileChanged = True
         End If
      End If

   Next

   ' If we changed any data in the file, then rewrite it now
   If blnFileChanged Then
      strData = Join(arrLines, vbCrLf)
      Set objFile = objFSO.OpenTextFile(objCheckFile.Path, ForWriting, True)
      objFile.Write(strData)
      objFile.Close
      Set objFile = Nothing
   End If

End Sub

Function StringToDate(strDate)
   ' Convert a string inb format YYYYMMDD to a date type value
   StringToDate = CDate(Mid(strDate, 5, 2) & "/" & Mid(strDate, 7, 2) & "/" & Mid(strDate, 1, 4))
End Function

Function DateToString(datDate)
   ' Convert a date type value to a text string in format YYYYMMDD
   DateToString = Year(datDate) & LPad(Month(datDate), 2, "0") & LPad(Day(datDate), 2, "0")
End Function

Function LPad( strText, intLen, chrPad )
   ' Left pad a string to any length with a specified character
   LPad = Right( String( intLen, chrPad ) & strText, intLen )
End Function

Open in new window




This script look for delivery date which is less then today's date and change it to next todays+1 if delivery date is = today's date then it leaves the file as it is.

What i want is if the order date is friday then change the delivery date to next working day which is Monday and also if its bank holiday then its next working day.
0
Comment
Question by:ammartahir1978
  • 14
  • 8
  • 4
26 Comments
 
LVL 48

Expert Comment

by:Rgonzo1971
ID: 41750315
Hi,

pls try to replace
arrFields(6) = DateToString(DateAdd("d", 1, Now))

Open in new window

with
    Result = DateAdd("d", 1, Now)
    Do Until (Weekday(Result) <> vbSaturday) And (Weekday(Result) <> vbSunday) And (Not IsHoliday(Result))
        Result = DateAdd("d", 1, Result)
    Loop
    arrFields(6) = DateToString(Result)

Open in new window

You will have to write the IsHoliday Function

Regards
0
 
LVL 48

Expert Comment

by:Rgonzo1971
ID: 41750343
If you are looking for US bank Holidays
pls try
Function IsHoliday(sDate)
'Checks to see if passed date is a holiday
Dim iDay, iTmpDay, i

IsHoliday = 0
iDay = Day(sDate)

'Check if valid date first
If IsDate(sDate) Then
    Select Case Month(sDate)

        Case 1  'Jan
            If iDay = 1 Then  'New Years
                IsHoliday = 1
            Else
         If iDay = 2 Then  'Make sure new years doesn't fall on sunday. 
                           'If so, today is a holiday.
                        if Weekday(DateAdd("d", -1, sDate)) = 1 then
                IsHoliday = 1
            end if
             Else
            For i = 0 To 30     'Martin Luther King B-Day
                If Weekday(DateAdd("d", i, CDate("1/1/" & Year(sDate)))) _
                              = 2 Then
                    If CDate(sDate) = CDate(DateAdd("d", i + 14, _
                          CDate("1/1/" & Year(sDate)))) Then
                        IsHoliday = 1
                    End If
                    Exit For  'PG 1/28
                End If
            Next 
        End If
            End If

        Case 2  'Feb
        For i = 0 To 27     'President's Day
           If Weekday(DateAdd("d", i, CDate("2/1/" & Year(sDate)))) = 2 _
                    Then
            If CDate(sDate) = CDate(DateAdd("d", i + 14, _
                    CDate("2/1/" & Year(sDate)))) Then
               IsHoliday = 1
            End If
            Exit For 
         End If
      Next 

        Case 3  'Mar
        Case 4  'Apr

        Case 5  'May
            For i = 1 To 7  'Memorial Day
                If Weekday(DateAdd("d", "-" & i, _
                     CDate("5/31/" & Year(sDate)))) = 2 Then
                    If CDate(sDate) = CDate(DateAdd("d", "-" & i, _
                      CDate("5/31/" & Year(sDate)))) Then
                        IsHoliday = 1
                    End If
                    Exit For
                End If
            Next 

        Case 6  'Jun

        Case 7  'Jul
    If iDay = 4 Then  'Independence Day
        IsHoliday = 1
    Else
        If iDay = 3 Then  'Make sure Independence Day doesn't 
                     'fall on saturday. If so, Friday is a holiday.
                      if Weekday(DateAdd("d", 1, sDate)) = 7 then
                IsHoliday = 1
            end if
        Else    
            If iDay = 5 Then  'Make sure Independence 
                    'Day doesn't fall on sunday. If so, Monday is a holiday.
                if Weekday(DateAdd("d", -1, sDate)) = 1 then
                    IsHoliday = 1
                end if
            End If
        End If
    End If

        Case 8 'Aug

        Case 9 'Sep
            For i = 0 To 13  'Labor Day
                If Weekday(DateAdd("d", i, CDate("9/1/" & _
                        Year(sDate)))) = 2 Then
                    If CDate(sDate) = CDate(DateAdd("d", i, _
                           CDate("9/1/" & Year(sDate)))) Then
                        IsHoliday = 1
                    End If
                    Exit For
                End If
            Next 

        Case 10 'Oct
    For i = 0 To 13  'Columbus Day
       If Weekday(DateAdd("d", i, CDate("10/1/" & _
                      Year(sDate)))) = 2 Then
          If CDate(sDate) = CDate(DateAdd("d", i + 7, CDate("10/1/" & _
                  Year(sDate)))) Then
             IsHoliday = 1
          End If
            Exit For
       End If
    Next 

        Case 11 'Nov
    If iDay = 11 Then  'Veteran's Day
       IsHoliday = 1
    Else
       If iDay = 10 Then  'Make sure Veterans Day doesn't fall 
                   'on saturday. If so, Friday is a holiday.
          if Weekday(DateAdd("d", 1, sDate)) = 7 then
             IsHoliday = 1
          end if
       Else    
          If iDay = 12 Then  'Make sure Veterans Day doesn't 
                   'fall on sunday. If so, Monday is a holiday.
             if Weekday(DateAdd("d", -1, sDate)) = 1 then
                IsHoliday = 1
             end if
          Else
             For i = 0 To 28     'Thanksgiving & the Day After
                If Weekday(DateAdd("d", i, CDate("11/1/" & _
                    Year(sDate)))) = 5 Then 'this is the first 
                               'thursday of the month
                   if datediff("d", sDate,  DateAdd("d", i + 21, _
                         CDate("11/1/" & Year(sDate)))) = 0 then 'add 3 
                             'weeks to the first to get the 4th (thanksgiving)
                      IsHoliday = 1
                      Exit For
                    End If
                End if
                If Weekday(DateAdd("d", i, CDate("11/1/" & _
                     Year(sDate)))) = 6 Then 'this is the day 
                              'after thanksgiving
                   if datediff("d", sDate,  DateAdd("d", i + 21,_
                        CDate("11/1/" & Year(sDate)))) = 0 then
                      IsHoliday = 1
                      Exit For
                   End If            
                 End if
             Next
          End If
       End If
    End If

        Case 12 'Dec
    If iDay = 25 Then  'Christmas
       IsHoliday = 1
    Else
       If iDay = 24 Then  'Make sure Christmas Day doesn't 
              'fall on saturday. If so, Friday is a holiday.
          if Weekday(DateAdd("d", 1, sDate)) = 7 then
             IsHoliday = 1
          end if
       Else    
          If iDay = 26 Then  'Make sure Christmas 
               'Day doesn't fall on sunday. If so, Monday is a holiday.
             if Weekday(DateAdd("d", -1, sDate)) = 1 then
                IsHoliday = 1
             end if
          Else
             If iDay = 31 Then  'Make sure new years 
                  'doesn't fall on saturday. If so, today is a holiday.
                if Weekday(DateAdd("d", 1, sDate)) = 7 then
                   IsHoliday = 1
                End if
             End if
          End if
       End if
    End If

        Case Else
            'Do nothing but return false

    End Select
End If

End Function

Open in new window

0
 

Author Comment

by:ammartahir1978
ID: 41753486
Hi Razor,

thank you very much.

I am not very good in scripting can you amend mine?
0
 

Author Comment

by:ammartahir1978
ID: 41753487
Sorry got your name wrong Rgonzo1971
0
 
LVL 48

Expert Comment

by:Rgonzo1971
ID: 41753493
then try
Option Explicit

' ==============================================================================
' C O N S T A N T S   &   V A R I A B L E S
' ==============================================================================

' Define needed constants
Const ForReading = 1
Const ForWriting = 2
Const TriStateUseDefault = -2

' Define global variables
Dim objFSO
Dim strCheckFolder

' Specify path to folder of files to process
strCheckFolder = "D:\LABELS\ChessODBCFiles\BedfordSO"

' ==============================================================================
' I N I T I A L I Z A T I O N
' ==============================================================================

' Create filesystem object
Set objFSO = CreateObject("Scripting.FileSystemObject")

' ==============================================================================
' M A I N   L O G I C
' ==============================================================================

' Process the base folder
ProcessFolder strCheckFolder

' ==============================================================================
' W R A P U P
' ==============================================================================

' Done, cleanup and exit
Wscript.Quit

' ==============================================================================
' S U B R O U T I N E S   &   F U N C T I O N S
' ==============================================================================

Sub ProcessFolder(strCheckFolder)

   ' Define local variables
   Dim objCheckFolder
   Dim objCheckFile

   ' Make sure the folder exists
   If Not objFSO.FolderExists(strCheckFolder) Then
      Wscript.Echo "*ERROR* Folder [" & strCheckFolder & "] does not exist, quitting."
      Wscript.Quit
   End If

   ' Access the folder
   Set objCheckFolder = objFSO.GetFolder(strCheckFolder)

   ' Look at all files in this folder, process each one
   For Each objCheckFile In objCheckFolder.Files
      ProcessFile objCheckFile
   Next

End Sub

Sub ProcessFile(objCheckFile)

   ' Define local varaibles
   Dim objFile
   Dim strData
   Dim arrLines
   Dim arrFields
   Dim blnFileChanged
   Dim i

   ' Read the file contents into an array for processing
   Set objFile = objFSO.OpenTextFile(objCheckFile.Path, ForReading, False, TriStateUseDefault)
   strData = objFile.ReadAll
   arrLines = Split(strData, vbCrLf)
   objFile.Close
   Set objFile = Nothing

   ' Assume no changes needed to this file
   blnFileChanged = False

   ' Look at each line of the file and check the date field
   For i = 0 To UBound(arrLines)

      ' Split the fields of this line apart into an array (comma delim)
      arrFields = Split(arrLines(i), ",")

      ' Make sure we found at least 7 columns (array indexes are 0 based...)
      If UBound(arrFields) > 5 Then
         ' Check if 7th column is in the future, if not set it to tomorrows date
         If DateDiff("d", Now, StringToDate(Trim(arrFields(6)))) < 1 Then
             Result = DateAdd("d", 1, Now)
             Do Until (Weekday(Result) <> vbSaturday) And (Weekday(Result) <> vbSunday) And (Not IsHoliday(Result))
                 Result = DateAdd("d", 1, Result)
             Loop
             arrFields(6) = DateToString(Result)

            arrLines(i) = Join(arrFields, ",")
            blnFileChanged = True
         End If
      End If

   Next

   ' If we changed any data in the file, then rewrite it now
   If blnFileChanged Then
      strData = Join(arrLines, vbCrLf)
      Set objFile = objFSO.OpenTextFile(objCheckFile.Path, ForWriting, True)
      objFile.Write(strData)
      objFile.Close
      Set objFile = Nothing
   End If

End Sub

Function StringToDate(strDate)
   ' Convert a string inb format YYYYMMDD to a date type value
   StringToDate = CDate(Mid(strDate, 5, 2) & "/" & Mid(strDate, 7, 2) & "/" & Mid(strDate, 1, 4))
End Function

Function DateToString(datDate)
   ' Convert a date type value to a text string in format YYYYMMDD
   DateToString = Year(datDate) & LPad(Month(datDate), 2, "0") & LPad(Day(datDate), 2, "0")
End Function

Function LPad( strText, intLen, chrPad )
   ' Left pad a string to any length with a specified character
   LPad = Right( String( intLen, chrPad ) & strText, intLen )
End Function¨

Function IsHoliday(dDate)
'Checks to see if passed date is a holiday
Dim iDay, iTmpDay, i

IsHoliday = 0
iDay = Day(dDate)

'Check if valid date first
If IsDate(dDate) Then
    Select Case Month(dDate)

        Case 1  'Jan
            If iDay = 1 Then  'New Years
                IsHoliday = 1
            Else
         If iDay = 2 Then  'Make sure new years doesn't fall on sunday.
                           'If so, today is a holiday.
                        If Weekday(DateAdd("d", -1, dDate)) = 1 Then
                IsHoliday = 1
            End If
             Else
            For i = 0 To 30     'Martin Luther King B-Day
                If Weekday(DateAdd("d", i, CDate("1/1/" & Year(dDate)))) _
                              = 2 Then
                    If CDate(dDate) = CDate(DateAdd("d", i + 14, _
                          CDate("1/1/" & Year(dDate)))) Then
                        IsHoliday = 1
                    End If
                    Exit For  'PG 1/28
                End If
            Next
        End If
            End If

        Case 2  'Feb
        For i = 0 To 27     'President's Day
           If Weekday(DateAdd("d", i, CDate("2/1/" & Year(dDate)))) = 2 _
                    Then
            If CDate(dDate) = CDate(DateAdd("d", i + 14, _
                    CDate("2/1/" & Year(dDate)))) Then
               IsHoliday = 1
            End If
            Exit For
         End If
      Next

        Case 3  'Mar
        Case 4  'Apr

        Case 5  'May
            For i = 1 To 7  'Memorial Day
                If Weekday(DateAdd("d", "-" & i, _
                     CDate("5/31/" & Year(dDate)))) = 2 Then
                    If CDate(dDate) = CDate(DateAdd("d", "-" & i, _
                      CDate("5/31/" & Year(dDate)))) Then
                        IsHoliday = 1
                    End If
                    Exit For
                End If
            Next

        Case 6  'Jun

        Case 7  'Jul
    If iDay = 4 Then  'Independence Day
        IsHoliday = 1
    Else
        If iDay = 3 Then  'Make sure Independence Day doesn't
                     'fall on saturday. If so, Friday is a holiday.
                      If Weekday(DateAdd("d", 1, dDate)) = 7 Then
                IsHoliday = 1
            End If
        Else
            If iDay = 5 Then  'Make sure Independence
                    'Day doesn't fall on sunday. If so, Monday is a holiday.
                If Weekday(DateAdd("d", -1, dDate)) = 1 Then
                    IsHoliday = 1
                End If
            End If
        End If
    End If

        Case 8 'Aug

        Case 9 'Sep
            For i = 0 To 13  'Labor Day
                If Weekday(DateAdd("d", i, CDate("9/1/" & _
                        Year(dDate)))) = 2 Then
                    If CDate(dDate) = CDate(DateAdd("d", i, _
                           CDate("9/1/" & Year(dDate)))) Then
                        IsHoliday = 1
                    End If
                    Exit For
                End If
            Next

        Case 10 'Oct
    For i = 0 To 13  'Columbus Day
       If Weekday(DateAdd("d", i, CDate("10/1/" & _
                      Year(dDate)))) = 2 Then
          If CDate(dDate) = CDate(DateAdd("d", i + 7, CDate("10/1/" & _
                  Year(dDate)))) Then
             IsHoliday = 1
          End If
            Exit For
       End If
    Next

        Case 11 'Nov
    If iDay = 11 Then  'Veteran's Day
       IsHoliday = 1
    Else
       If iDay = 10 Then  'Make sure Veterans Day doesn't fall
                   'on saturday. If so, Friday is a holiday.
          If Weekday(DateAdd("d", 1, dDate)) = 7 Then
             IsHoliday = 1
          End If
       Else
          If iDay = 12 Then  'Make sure Veterans Day doesn't
                   'fall on sunday. If so, Monday is a holiday.
             If Weekday(DateAdd("d", -1, dDate)) = 1 Then
                IsHoliday = 1
             End If
          Else
             For i = 0 To 28     'Thanksgiving & the Day After
                If Weekday(DateAdd("d", i, CDate("11/1/" & _
                    Year(dDate)))) = 5 Then 'this is the first
                               'thursday of the month
                   If DateDiff("d", dDate, DateAdd("d", i + 21, _
                         CDate("11/1/" & Year(dDate)))) = 0 Then 'add 3
                             'weeks to the first to get the 4th (thanksgiving)
                      IsHoliday = 1
                      Exit For
                    End If
                End If
                If Weekday(DateAdd("d", i, CDate("11/1/" & _
                     Year(dDate)))) = 6 Then 'this is the day
                              'after thanksgiving
                   If DateDiff("d", dDate, DateAdd("d", i + 21, CDate("11/1/" & Year(dDate)))) = 0 Then
                      IsHoliday = 1
                      Exit For
                   End If
                 End If
             Next
          End If
       End If
    End If

        Case 12 'Dec
    If iDay = 25 Then  'Christmas
       IsHoliday = 1
    Else
       If iDay = 24 Then  'Make sure Christmas Day doesn't
              'fall on saturday. If so, Friday is a holiday.
          If Weekday(DateAdd("d", 1, dDate)) = 7 Then
             IsHoliday = 1
          End If
       Else
          If iDay = 26 Then  'Make sure Christmas
               'Day doesn't fall on sunday. If so, Monday is a holiday.
             If Weekday(DateAdd("d", -1, dDate)) = 1 Then
                IsHoliday = 1
             End If
          Else
             If iDay = 31 Then  'Make sure new years
                  'doesn't fall on saturday. If so, today is a holiday.
                If Weekday(DateAdd("d", 1, dDate)) = 7 Then
                   IsHoliday = 1
                End If
             End If
          End If
       End If
    End If

        Case Else
            'Do nothing but return false

    End Select
End If

End Function

Open in new window

0
 

Author Comment

by:ammartahir1978
ID: 41753556
Hi

This is the error i get




D:\LABELS\ChessODBCFiles>cscript TESTSOBackOrderDateChange27072016.VBS
Microsoft (R) Windows Script Host Version 5.6
Copyright (C) Microsoft Corporation 1996-2001. All rights reserved.

D:\LABELS\ChessODBCFiles\TESTSOBackOrderDateChange27072016.VBS(133, 13) Microso
t VBScript compilation error: Invalid character


I have attached the file as well which i tested.
A12082016_19301_CHESSPL.CSV
0
 
LVL 48

Assisted Solution

by:Rgonzo1971
Rgonzo1971 earned 250 total points
ID: 41753593
then try
Option Explicit

' ==============================================================================
' C O N S T A N T S   &   V A R I A B L E S
' ==============================================================================

' Define needed constants
Const ForReading = 1
Const ForWriting = 2
Const TriStateUseDefault = -2

' Define global variables
Dim objFSO
Dim strCheckFolder

' Specify path to folder of files to process
strCheckFolder = "D:\LABELS\ChessODBCFiles\BedfordSO"

' ==============================================================================
' I N I T I A L I Z A T I O N
' ==============================================================================

' Create filesystem object
Set objFSO = CreateObject("Scripting.FileSystemObject")

' ==============================================================================
' M A I N   L O G I C
' ==============================================================================

' Process the base folder
ProcessFolder strCheckFolder

' ==============================================================================
' W R A P U P
' ==============================================================================

' Done, cleanup and exit
Wscript.Quit

' ==============================================================================
' S U B R O U T I N E S   &   F U N C T I O N S
' ==============================================================================

Sub ProcessFolder(strCheckFolder)

   ' Define local variables
   Dim objCheckFolder
   Dim objCheckFile

   ' Make sure the folder exists
   If Not objFSO.FolderExists(strCheckFolder) Then
      Wscript.Echo "*ERROR* Folder [" & strCheckFolder & "] does not exist, quitting."
      Wscript.Quit
   End If

   ' Access the folder
   Set objCheckFolder = objFSO.GetFolder(strCheckFolder)

   ' Look at all files in this folder, process each one
   For Each objCheckFile In objCheckFolder.Files
      ProcessFile objCheckFile
   Next

End Sub

Sub ProcessFile(objCheckFile)

   ' Define local varaibles
   Dim objFile
   Dim strData
   Dim arrLines
   Dim arrFields
   Dim blnFileChanged
   Dim i

   ' Read the file contents into an array for processing
   Set objFile = objFSO.OpenTextFile(objCheckFile.Path, ForReading, False, TriStateUseDefault)
   strData = objFile.ReadAll
   arrLines = Split(strData, vbCrLf)
   objFile.Close
   Set objFile = Nothing

   ' Assume no changes needed to this file
   blnFileChanged = False

   ' Look at each line of the file and check the date field
   For i = 0 To UBound(arrLines)

      ' Split the fields of this line apart into an array (comma delim)
      arrFields = Split(arrLines(i), ",")

      ' Make sure we found at least 7 columns (array indexes are 0 based...)
      If UBound(arrFields) > 5 Then
         ' Check if 7th column is in the future, if not set it to tomorrows date
         If DateDiff("d", Now, StringToDate(Trim(arrFields(6)))) < 1 Then
             Result = DateAdd("d", 1, Now)
             Do Until (Weekday(Result) <> vbSaturday) And (Weekday(Result) <> vbSunday) And (Not IsHoliday(Result))
                 Result = DateAdd("d", 1, Result)
             Loop
             arrFields(6) = DateToString(Result)

            arrLines(i) = Join(arrFields, ",")
            blnFileChanged = True
         End If
      End If

   Next

   ' If we changed any data in the file, then rewrite it now
   If blnFileChanged Then
      strData = Join(arrLines, vbCrLf)
      Set objFile = objFSO.OpenTextFile(objCheckFile.Path, ForWriting, True)
      objFile.Write(strData)
      objFile.Close
      Set objFile = Nothing
   End If

End Sub

Function StringToDate(strDate)
   ' Convert a string inb format YYYYMMDD to a date type value
   StringToDate = CDate(Mid(strDate, 5, 2) & "/" & Mid(strDate, 7, 2) & "/" & Mid(strDate, 1, 4))
End Function

Function DateToString(datDate)
   ' Convert a date type value to a text string in format YYYYMMDD
   DateToString = Year(datDate) & LPad(Month(datDate), 2, "0") & LPad(Day(datDate), 2, "0")
End Function

Function LPad( strText, intLen, chrPad )
   ' Left pad a string to any length with a specified character
   LPad = Right( String( intLen, chrPad ) & strText, intLen )
End Function

Function IsHoliday(dDate)
'Checks to see if passed date is a holiday
Dim iDay, iTmpDay, i

IsHoliday = 0
iDay = Day(dDate)

'Check if valid date first
If IsDate(dDate) Then
    Select Case Month(dDate)

        Case 1  'Jan
            If iDay = 1 Then  'New Years
                IsHoliday = 1
            Else
         If iDay = 2 Then  'Make sure new years doesn't fall on sunday.
                           'If so, today is a holiday.
                        If Weekday(DateAdd("d", -1, dDate)) = 1 Then
                IsHoliday = 1
            End If
             Else
            For i = 0 To 30     'Martin Luther King B-Day
                If Weekday(DateAdd("d", i, CDate("1/1/" & Year(dDate)))) _
                              = 2 Then
                    If CDate(dDate) = CDate(DateAdd("d", i + 14, _
                          CDate("1/1/" & Year(dDate)))) Then
                        IsHoliday = 1
                    End If
                    Exit For  'PG 1/28
                End If
            Next
        End If
            End If

        Case 2  'Feb
        For i = 0 To 27     'President's Day
           If Weekday(DateAdd("d", i, CDate("2/1/" & Year(dDate)))) = 2 _
                    Then
            If CDate(dDate) = CDate(DateAdd("d", i + 14, _
                    CDate("2/1/" & Year(dDate)))) Then
               IsHoliday = 1
            End If
            Exit For
         End If
      Next

        Case 3  'Mar
        Case 4  'Apr

        Case 5  'May
            For i = 1 To 7  'Memorial Day
                If Weekday(DateAdd("d", "-" & i, _
                     CDate("5/31/" & Year(dDate)))) = 2 Then
                    If CDate(dDate) = CDate(DateAdd("d", "-" & i, _
                      CDate("5/31/" & Year(dDate)))) Then
                        IsHoliday = 1
                    End If
                    Exit For
                End If
            Next

        Case 6  'Jun

        Case 7  'Jul
    If iDay = 4 Then  'Independence Day
        IsHoliday = 1
    Else
        If iDay = 3 Then  'Make sure Independence Day doesn't
                     'fall on saturday. If so, Friday is a holiday.
                      If Weekday(DateAdd("d", 1, dDate)) = 7 Then
                IsHoliday = 1
            End If
        Else
            If iDay = 5 Then  'Make sure Independence
                    'Day doesn't fall on sunday. If so, Monday is a holiday.
                If Weekday(DateAdd("d", -1, dDate)) = 1 Then
                    IsHoliday = 1
                End If
            End If
        End If
    End If

        Case 8 'Aug

        Case 9 'Sep
            For i = 0 To 13  'Labor Day
                If Weekday(DateAdd("d", i, CDate("9/1/" & _
                        Year(dDate)))) = 2 Then
                    If CDate(dDate) = CDate(DateAdd("d", i, _
                           CDate("9/1/" & Year(dDate)))) Then
                        IsHoliday = 1
                    End If
                    Exit For
                End If
            Next

        Case 10 'Oct
    For i = 0 To 13  'Columbus Day
       If Weekday(DateAdd("d", i, CDate("10/1/" & _
                      Year(dDate)))) = 2 Then
          If CDate(dDate) = CDate(DateAdd("d", i + 7, CDate("10/1/" & _
                  Year(dDate)))) Then
             IsHoliday = 1
          End If
            Exit For
       End If
    Next

        Case 11 'Nov
    If iDay = 11 Then  'Veteran's Day
       IsHoliday = 1
    Else
       If iDay = 10 Then  'Make sure Veterans Day doesn't fall
                   'on saturday. If so, Friday is a holiday.
          If Weekday(DateAdd("d", 1, dDate)) = 7 Then
             IsHoliday = 1
          End If
       Else
          If iDay = 12 Then  'Make sure Veterans Day doesn't
                   'fall on sunday. If so, Monday is a holiday.
             If Weekday(DateAdd("d", -1, dDate)) = 1 Then
                IsHoliday = 1
             End If
          Else
             For i = 0 To 28     'Thanksgiving & the Day After
                If Weekday(DateAdd("d", i, CDate("11/1/" & _
                    Year(dDate)))) = 5 Then 'this is the first
                               'thursday of the month
                   If DateDiff("d", dDate, DateAdd("d", i + 21, _
                         CDate("11/1/" & Year(dDate)))) = 0 Then 'add 3
                             'weeks to the first to get the 4th (thanksgiving)
                      IsHoliday = 1
                      Exit For
                    End If
                End If
                If Weekday(DateAdd("d", i, CDate("11/1/" & _
                     Year(dDate)))) = 6 Then 'this is the day
                              'after thanksgiving
                   If DateDiff("d", dDate, DateAdd("d", i + 21, CDate("11/1/" & Year(dDate)))) = 0 Then
                      IsHoliday = 1
                      Exit For
                   End If
                 End If
             Next
          End If
       End If
    End If

        Case 12 'Dec
    If iDay = 25 Then  'Christmas
       IsHoliday = 1
    Else
       If iDay = 24 Then  'Make sure Christmas Day doesn't
              'fall on saturday. If so, Friday is a holiday.
          If Weekday(DateAdd("d", 1, dDate)) = 7 Then
             IsHoliday = 1
          End If
       Else
          If iDay = 26 Then  'Make sure Christmas
               'Day doesn't fall on sunday. If so, Monday is a holiday.
             If Weekday(DateAdd("d", -1, dDate)) = 1 Then
                IsHoliday = 1
             End If
          Else
             If iDay = 31 Then  'Make sure new years
                  'doesn't fall on saturday. If so, today is a holiday.
                If Weekday(DateAdd("d", 1, dDate)) = 7 Then
                   IsHoliday = 1
                End If
             End If
          End If
       End If
    End If

        Case Else
            'Do nothing but return false

    End Select
End If

End Function

Open in new window

1
 

Author Comment

by:ammartahir1978
ID: 41753607
script ran but didnt change the date from 13 to 15 08.

Regards,

A
0
 
LVL 51

Expert Comment

by:Bill Prew
ID: 41753608
It looks like there is an odd character at the end of line 133, try deleting the last character in that line.

End Function¨

~bp

EDIT: Whoops, that's already been addressed...
0
 

Author Comment

by:ammartahir1978
ID: 41753613
Hi Bill i have tried that script run but doesnt change date.

So the logic i want is :


Anything placed on Fridays should be Next working day which is Monday but if Monday is Bank holiday then its TUESDAY, and rest of the Logic stays same which is if Despatch date is Less then TODAY change it to TODAY+1 but if TODAY + 1 is Bank holiday change it to (TODAY+1)+1
0
 
LVL 51

Expert Comment

by:Bill Prew
ID: 41753618
It's still 12 AUG 2016 here, is it already 13 AUG 2016 where you are?  If not then it should change the 13th yet, should it?

~bp
0
 

Author Comment

by:ammartahir1978
ID: 41753620
It is 12th August which is FRiday so order is placed on FRIDAY (TODAY 12th august) any order placed on Friday should change the despatch date in this case is 13th to 15th becasue 13 is saturday and 14 is sunday so next working day is 15th.

It should only do this when despatch date is Saturday in this case all orders should change to 15th apart from the order which already have 15 or any future dates.

I hope it make sense

Thanks
0
 
LVL 51

Accepted Solution

by:
Bill Prew earned 250 total points
ID: 41753649
Okay, made some adjustments to try and correct that.  Also fixed the end of line delimiter used for splitting the data into lines, since in the file you uploaded it was a single line feed character rather than the Windows standard.  It should handle the typical ones found in text files.  Give this a test...

Option Explicit

' ==============================================================================
' C O N S T A N T S   &   V A R I A B L E S
' ==============================================================================

' Define needed constants
Const ForReading = 1
Const ForWriting = 2
Const TriStateUseDefault = -2

' Define global variables
Dim objFSO
Dim strCheckFolder

' Specify path to folder of files to process
strCheckFolder = "D:\LABELS\ChessODBCFiles\BedfordSO"

' ==============================================================================
' I N I T I A L I Z A T I O N
' ==============================================================================

' Create filesystem object
Set objFSO = CreateObject("Scripting.FileSystemObject")

' ==============================================================================
' M A I N   L O G I C
' ==============================================================================

' Process the base folder
ProcessFolder strCheckFolder

' ==============================================================================
' W R A P U P
' ==============================================================================

' Done, cleanup and exit
Wscript.Quit

' ==============================================================================
' S U B R O U T I N E S   &   F U N C T I O N S
' ==============================================================================

Sub ProcessFolder(strCheckFolder)

   ' Define local variables
   Dim objCheckFolder
   Dim objCheckFile

   ' Make sure the folder exists
   If Not objFSO.FolderExists(strCheckFolder) Then
      Wscript.Echo "*ERROR* Folder [" & strCheckFolder & "] does not exist, quitting."
      Wscript.Quit
   End If

   ' Access the folder
   Set objCheckFolder = objFSO.GetFolder(strCheckFolder)

   ' Look at all files in this folder, process each one
   For Each objCheckFile In objCheckFolder.Files
      ProcessFile objCheckFile
   Next

End Sub

Sub ProcessFile(objCheckFile)

   ' Define local varaibles
   Dim objFile
   Dim strData
   Dim arrLines
   Dim arrFields
   Dim blnFileChanged
   Dim i
   Dim datNextFutureWorkingDay
   Dim strDelim

   ' Read the file contents into an array for processing
   Set objFile = objFSO.OpenTextFile(objCheckFile.Path, ForReading, False, TriStateUseDefault)
   strData = objFile.ReadAll
   objFile.Close
   Set objFile = Nothing

   ' Determine what is used in the file for line delimiters, and split file into lines
   If InStr(strData, vbCrLf) > 0 Then
      strDelim = vbCrLf
   ElseIf InStr(strData, vbLf) > 0 Then
      strDelim = vbLf
   ElseIf InStr(strData, vbCr) > 0 Then
      strDelim = vbCr
   End If
   arrLines = Split(strData, strDelim)

   ' Assume no changes needed to this file
   blnFileChanged = False

   ' Look at each line of the file and check the date field
   For i = 0 To UBound(arrLines)

      ' Split the fields of this line apart into an array (comma delim)
      arrFields = Split(arrLines(i), ",")

      ' Make sure we found at least 7 columns (array indexes are 0 based...)
      If UBound(arrFields) > 5 Then
         ' Check if 7th column is in the future, if not set it to tomorrows date
         datNextFutureWorkingDay = NextFutureWorkingDay()
         If DateDiff("d", datNextFutureWorkingDay, StringToDate(Trim(arrFields(6)))) < 0 Then
            arrFields(6) = DateToString(datNextFutureWorkingDay)
            arrLines(i) = Join(arrFields, ",")
            blnFileChanged = True
         End If

      End If

   Next

   ' If we changed any data in the file, then rewrite it now
   If blnFileChanged Then
      strData = Join(arrLines, vbCrLf)
      Set objFile = objFSO.OpenTextFile(objCheckFile.Path, ForWriting, True)
      objFile.Write(strData)
      objFile.Close
      Set objFile = Nothing
   End If

End Sub

Function NextFutureWorkingDay()
    ' Calculate the next working day (no weekends, no holidays) in the future
    NextFutureWorkingDay = DateAdd("d", 1, Now)
    Do Until (Weekday(NextFutureWorkingDay) <> vbSaturday) And (Weekday(NextFutureWorkingDay) <> vbSunday) And (Not IsHoliday(NextFutureWorkingDay))
        NextFutureWorkingDay = DateAdd("d", 1, NextFutureWorkingDay)
    Loop
End Function

Function StringToDate(strDate)
   ' Convert a string inb format YYYYMMDD to a date type value
   StringToDate = CDate(Mid(strDate, 5, 2) & "/" & Mid(strDate, 7, 2) & "/" & Mid(strDate, 1, 4))
End Function

Function DateToString(datDate)
   ' Convert a date type value to a text string in format YYYYMMDD
   DateToString = Year(datDate) & LPad(Month(datDate), 2, "0") & LPad(Day(datDate), 2, "0")
End Function

Function LPad( strText, intLen, chrPad )
   ' Left pad a string to any length with a specified character
   LPad = Right( String( intLen, chrPad ) & strText, intLen )
End Function

Function IsHoliday(dDate)
'Checks to see if passed date is a holiday
Dim iDay, iTmpDay, i

IsHoliday = 0
iDay = Day(dDate)

'Check if valid date first
If IsDate(dDate) Then
    Select Case Month(dDate)

        Case 1  'Jan
            If iDay = 1 Then  'New Years
                IsHoliday = 1
            Else
         If iDay = 2 Then  'Make sure new years doesn't fall on sunday.
                           'If so, today is a holiday.
                        If Weekday(DateAdd("d", -1, dDate)) = 1 Then
                IsHoliday = 1
            End If
             Else
            For i = 0 To 30     'Martin Luther King B-Day
                If Weekday(DateAdd("d", i, CDate("1/1/" & Year(dDate)))) _
                              = 2 Then
                    If CDate(dDate) = CDate(DateAdd("d", i + 14, _
                          CDate("1/1/" & Year(dDate)))) Then
                        IsHoliday = 1
                    End If
                    Exit For  'PG 1/28
                End If
            Next
        End If
            End If

        Case 2  'Feb
        For i = 0 To 27     'President's Day
           If Weekday(DateAdd("d", i, CDate("2/1/" & Year(dDate)))) = 2 _
                    Then
            If CDate(dDate) = CDate(DateAdd("d", i + 14, _
                    CDate("2/1/" & Year(dDate)))) Then
               IsHoliday = 1
            End If
            Exit For
         End If
      Next

        Case 3  'Mar
        Case 4  'Apr

        Case 5  'May
            For i = 1 To 7  'Memorial Day
                If Weekday(DateAdd("d", "-" & i, _
                     CDate("5/31/" & Year(dDate)))) = 2 Then
                    If CDate(dDate) = CDate(DateAdd("d", "-" & i, _
                      CDate("5/31/" & Year(dDate)))) Then
                        IsHoliday = 1
                    End If
                    Exit For
                End If
            Next

        Case 6  'Jun

        Case 7  'Jul
    If iDay = 4 Then  'Independence Day
        IsHoliday = 1
    Else
        If iDay = 3 Then  'Make sure Independence Day doesn't
                     'fall on saturday. If so, Friday is a holiday.
                      If Weekday(DateAdd("d", 1, dDate)) = 7 Then
                IsHoliday = 1
            End If
        Else
            If iDay = 5 Then  'Make sure Independence
                    'Day doesn't fall on sunday. If so, Monday is a holiday.
                If Weekday(DateAdd("d", -1, dDate)) = 1 Then
                    IsHoliday = 1
                End If
            End If
        End If
    End If

        Case 8 'Aug

        Case 9 'Sep
            For i = 0 To 13  'Labor Day
                If Weekday(DateAdd("d", i, CDate("9/1/" & _
                        Year(dDate)))) = 2 Then
                    If CDate(dDate) = CDate(DateAdd("d", i, _
                           CDate("9/1/" & Year(dDate)))) Then
                        IsHoliday = 1
                    End If
                    Exit For
                End If
            Next

        Case 10 'Oct
    For i = 0 To 13  'Columbus Day
       If Weekday(DateAdd("d", i, CDate("10/1/" & _
                      Year(dDate)))) = 2 Then
          If CDate(dDate) = CDate(DateAdd("d", i + 7, CDate("10/1/" & _
                  Year(dDate)))) Then
             IsHoliday = 1
          End If
            Exit For
       End If
    Next

        Case 11 'Nov
    If iDay = 11 Then  'Veteran's Day
       IsHoliday = 1
    Else
       If iDay = 10 Then  'Make sure Veterans Day doesn't fall
                   'on saturday. If so, Friday is a holiday.
          If Weekday(DateAdd("d", 1, dDate)) = 7 Then
             IsHoliday = 1
          End If
       Else
          If iDay = 12 Then  'Make sure Veterans Day doesn't
                   'fall on sunday. If so, Monday is a holiday.
             If Weekday(DateAdd("d", -1, dDate)) = 1 Then
                IsHoliday = 1
             End If
          Else
             For i = 0 To 28     'Thanksgiving & the Day After
                If Weekday(DateAdd("d", i, CDate("11/1/" & _
                    Year(dDate)))) = 5 Then 'this is the first
                               'thursday of the month
                   If DateDiff("d", dDate, DateAdd("d", i + 21, _
                         CDate("11/1/" & Year(dDate)))) = 0 Then 'add 3
                             'weeks to the first to get the 4th (thanksgiving)
                      IsHoliday = 1
                      Exit For
                    End If
                End If
                If Weekday(DateAdd("d", i, CDate("11/1/" & _
                     Year(dDate)))) = 6 Then 'this is the day
                              'after thanksgiving
                   If DateDiff("d", dDate, DateAdd("d", i + 21, CDate("11/1/" & Year(dDate)))) = 0 Then
                      IsHoliday = 1
                      Exit For
                   End If
                 End If
             Next
          End If
       End If
    End If

        Case 12 'Dec
    If iDay = 25 Then  'Christmas
       IsHoliday = 1
    Else
       If iDay = 24 Then  'Make sure Christmas Day doesn't
              'fall on saturday. If so, Friday is a holiday.
          If Weekday(DateAdd("d", 1, dDate)) = 7 Then
             IsHoliday = 1
          End If
       Else
          If iDay = 26 Then  'Make sure Christmas
               'Day doesn't fall on sunday. If so, Monday is a holiday.
             If Weekday(DateAdd("d", -1, dDate)) = 1 Then
                IsHoliday = 1
             End If
          Else
             If iDay = 31 Then  'Make sure new years
                  'doesn't fall on saturday. If so, today is a holiday.
                If Weekday(DateAdd("d", 1, dDate)) = 7 Then
                   IsHoliday = 1
                End If
             End If
          End If
       End If
    End If

        Case Else
            'Do nothing but return false

    End Select
End If

End Function

Open in new window

~bp
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

Author Comment

by:ammartahir1978
ID: 41753658
Thank Bill i have check that now
0
 

Author Comment

by:ammartahir1978
ID: 41753668
Hi Bill i have just tested this file

W,2120011,HI/0102142/0000,0001/2120011/001,PO0029657       ,20160826,20160827,0001/2120011/001,0100,345768,1        ,1        ,UNIT,C


you can see the order date is 26th aug which is friday so script should change it to 29th but it didnt and also 29 th is bank holiday so it should change the date to 20th aug.

can you check please
0
 
LVL 51

Expert Comment

by:Bill Prew
ID: 41753703
That date is still in the future, so it should not be changed yet.

~bp
0
 

Author Comment

by:ammartahir1978
ID: 41753820
Excellent
0
 
LVL 51

Expert Comment

by:Bill Prew
ID: 41753881
@ammartahir1978,

I reopened the question, and would respectfully ask that you close it sharing points and recognition with Rgonzo1971 as well.  Their contribution of the logic for weekends and holidays should not be overlooked.

~bp
0
 

Author Comment

by:ammartahir1978
ID: 41757650
Hi Phil,

I have tested the script you have posted and unfortunately it is not working its not changing dates.

Can you please have a relook?

Option Explicit

' ==============================================================================
' C O N S T A N T S   &   V A R I A B L E S
' ==============================================================================

' Define needed constants
Const ForReading = 1
Const ForWriting = 2
Const TriStateUseDefault = -2

' Define global variables
Dim objFSO
Dim strCheckFolder

' Specify path to folder of files to process
strCheckFolder = "D:\LABELS\ChessODBCFiles\BedfordSO"

' ==============================================================================
' I N I T I A L I Z A T I O N
' ==============================================================================

' Create filesystem object
Set objFSO = CreateObject("Scripting.FileSystemObject")

' ==============================================================================
' M A I N   L O G I C
' ==============================================================================

' Process the base folder
ProcessFolder strCheckFolder

' ==============================================================================
' W R A P U P
' ==============================================================================

' Done, cleanup and exit
Wscript.Quit

' ==============================================================================
' S U B R O U T I N E S   &   F U N C T I O N S
' ==============================================================================

Sub ProcessFolder(strCheckFolder)

   ' Define local variables
   Dim objCheckFolder
   Dim objCheckFile

   ' Make sure the folder exists
   If Not objFSO.FolderExists(strCheckFolder) Then
      Wscript.Echo "*ERROR* Folder [" & strCheckFolder & "] does not exist, quitting."
      Wscript.Quit
   End If

   ' Access the folder
   Set objCheckFolder = objFSO.GetFolder(strCheckFolder)

   ' Look at all files in this folder, process each one
   For Each objCheckFile In objCheckFolder.Files
      ProcessFile objCheckFile
   Next

End Sub

Sub ProcessFile(objCheckFile)

   ' Define local varaibles
   Dim objFile
   Dim strData
   Dim arrLines
   Dim arrFields
   Dim blnFileChanged
   Dim i
   Dim datNextFutureWorkingDay
   Dim strDelim

   ' Read the file contents into an array for processing
   Set objFile = objFSO.OpenTextFile(objCheckFile.Path, ForReading, False, TriStateUseDefault)
   strData = objFile.ReadAll
   objFile.Close
   Set objFile = Nothing

   ' Determine what is used in the file for line delimiters, and split file into lines
   If InStr(strData, vbCrLf) > 0 Then
      strDelim = vbCrLf
   ElseIf InStr(strData, vbLf) > 0 Then
      strDelim = vbLf
   ElseIf InStr(strData, vbCr) > 0 Then
      strDelim = vbCr
   End If
   arrLines = Split(strData, strDelim)

   ' Assume no changes needed to this file
   blnFileChanged = False

   ' Look at each line of the file and check the date field
   For i = 0 To UBound(arrLines)

      ' Split the fields of this line apart into an array (comma delim)
      arrFields = Split(arrLines(i), ",")

      ' Make sure we found at least 7 columns (array indexes are 0 based...)
      If UBound(arrFields) > 5 Then
         ' Check if 7th column is in the future, if not set it to tomorrows date
         datNextFutureWorkingDay = NextFutureWorkingDay()
         If DateDiff("d", datNextFutureWorkingDay, StringToDate(Trim(arrFields(6)))) < 0 Then
            arrFields(6) = DateToString(datNextFutureWorkingDay)
            arrLines(i) = Join(arrFields, ",")
            blnFileChanged = True
         End If

      End If

   Next

   ' If we changed any data in the file, then rewrite it now
   If blnFileChanged Then
      strData = Join(arrLines, vbCrLf)
      Set objFile = objFSO.OpenTextFile(objCheckFile.Path, ForWriting, True)
      objFile.Write(strData)
      objFile.Close
      Set objFile = Nothing
   End If

End Sub

Function NextFutureWorkingDay()
    ' Calculate the next working day (no weekends, no holidays) in the future
    NextFutureWorkingDay = DateAdd("d", 1, Now)
    Do Until (Weekday(NextFutureWorkingDay) <> vbSaturday) And (Weekday(NextFutureWorkingDay) <> vbSunday) And (Not IsHoliday(NextFutureWorkingDay))
        NextFutureWorkingDay = DateAdd("d", 1, NextFutureWorkingDay)
    Loop
End Function

Function StringToDate(strDate)
   ' Convert a string inb format YYYYMMDD to a date type value
   StringToDate = CDate(Mid(strDate, 5, 2) & "/" & Mid(strDate, 7, 2) & "/" & Mid(strDate, 1, 4))
End Function

Function DateToString(datDate)
   ' Convert a date type value to a text string in format YYYYMMDD
   DateToString = Year(datDate) & LPad(Month(datDate), 2, "0") & LPad(Day(datDate), 2, "0")
End Function

Function LPad( strText, intLen, chrPad )
   ' Left pad a string to any length with a specified character
   LPad = Right( String( intLen, chrPad ) & strText, intLen )
End Function

Function IsHoliday(dDate)
'Checks to see if passed date is a holiday
Dim iDay, iTmpDay, i

IsHoliday = 0
iDay = Day(dDate)

'Check if valid date first
If IsDate(dDate) Then
    Select Case Month(dDate)

        Case 1  'Jan
            If iDay = 1 Then  'New Years
                IsHoliday = 1
            Else
         If iDay = 2 Then  'Make sure new years doesn't fall on sunday.
                           'If so, today is a holiday.
                        If Weekday(DateAdd("d", -1, dDate)) = 1 Then
                IsHoliday = 1
            End If
             Else
            For i = 0 To 30     'Martin Luther King B-Day
                If Weekday(DateAdd("d", i, CDate("1/1/" & Year(dDate)))) _
                              = 2 Then
                    If CDate(dDate) = CDate(DateAdd("d", i + 14, _
                          CDate("1/1/" & Year(dDate)))) Then
                        IsHoliday = 1
                    End If
                    Exit For  'PG 1/28
                End If
            Next
        End If
            End If

        Case 2  'Feb
        For i = 0 To 27     'President's Day
           If Weekday(DateAdd("d", i, CDate("2/1/" & Year(dDate)))) = 2 _
                    Then
            If CDate(dDate) = CDate(DateAdd("d", i + 14, _
                    CDate("2/1/" & Year(dDate)))) Then
               IsHoliday = 1
            End If
            Exit For
         End If
      Next

        Case 3  'Mar
        Case 4  'Apr

        Case 5  'May
            For i = 1 To 7  'Memorial Day
                If Weekday(DateAdd("d", "-" & i, _
                     CDate("5/31/" & Year(dDate)))) = 2 Then
                    If CDate(dDate) = CDate(DateAdd("d", "-" & i, _
                      CDate("5/31/" & Year(dDate)))) Then
                        IsHoliday = 1
                    End If
                    Exit For
                End If
            Next

        Case 6  'Jun

        Case 7  'Jul
    If iDay = 4 Then  'Independence Day
        IsHoliday = 1
    Else
        If iDay = 3 Then  'Make sure Independence Day doesn't
                     'fall on saturday. If so, Friday is a holiday.
                      If Weekday(DateAdd("d", 1, dDate)) = 7 Then
                IsHoliday = 1
            End If
        Else
            If iDay = 5 Then  'Make sure Independence
                    'Day doesn't fall on sunday. If so, Monday is a holiday.
                If Weekday(DateAdd("d", -1, dDate)) = 1 Then
                    IsHoliday = 1
                End If
            End If
        End If
    End If

        Case 8 'Aug

        Case 9 'Sep
            For i = 0 To 13  'Labor Day
                If Weekday(DateAdd("d", i, CDate("9/1/" & _
                        Year(dDate)))) = 2 Then
                    If CDate(dDate) = CDate(DateAdd("d", i, _
                           CDate("9/1/" & Year(dDate)))) Then
                        IsHoliday = 1
                    End If
                    Exit For
                End If
            Next

        Case 10 'Oct
    For i = 0 To 13  'Columbus Day
       If Weekday(DateAdd("d", i, CDate("10/1/" & _
                      Year(dDate)))) = 2 Then
          If CDate(dDate) = CDate(DateAdd("d", i + 7, CDate("10/1/" & _
                  Year(dDate)))) Then
             IsHoliday = 1
          End If
            Exit For
       End If
    Next

        Case 11 'Nov
    If iDay = 11 Then  'Veteran's Day
       IsHoliday = 1
    Else
       If iDay = 10 Then  'Make sure Veterans Day doesn't fall
                   'on saturday. If so, Friday is a holiday.
          If Weekday(DateAdd("d", 1, dDate)) = 7 Then
             IsHoliday = 1
          End If
       Else
          If iDay = 12 Then  'Make sure Veterans Day doesn't
                   'fall on sunday. If so, Monday is a holiday.
             If Weekday(DateAdd("d", -1, dDate)) = 1 Then
                IsHoliday = 1
             End If
          Else
             For i = 0 To 28     'Thanksgiving & the Day After
                If Weekday(DateAdd("d", i, CDate("11/1/" & _
                    Year(dDate)))) = 5 Then 'this is the first
                               'thursday of the month
                   If DateDiff("d", dDate, DateAdd("d", i + 21, _
                         CDate("11/1/" & Year(dDate)))) = 0 Then 'add 3
                             'weeks to the first to get the 4th (thanksgiving)
                      IsHoliday = 1
                      Exit For
                    End If
                End If
                If Weekday(DateAdd("d", i, CDate("11/1/" & _
                     Year(dDate)))) = 6 Then 'this is the day
                              'after thanksgiving
                   If DateDiff("d", dDate, DateAdd("d", i + 21, CDate("11/1/" & Year(dDate)))) = 0 Then
                      IsHoliday = 1
                      Exit For
                   End If
                 End If
             Next
          End If
       End If
    End If

        Case 12 'Dec
    If iDay = 25 Then  'Christmas
       IsHoliday = 1
    Else
       If iDay = 24 Then  'Make sure Christmas Day doesn't
              'fall on saturday. If so, Friday is a holiday.
          If Weekday(DateAdd("d", 1, dDate)) = 7 Then
             IsHoliday = 1
          End If
       Else
          If iDay = 26 Then  'Make sure Christmas
               'Day doesn't fall on sunday. If so, Monday is a holiday.
             If Weekday(DateAdd("d", -1, dDate)) = 1 Then
                IsHoliday = 1
             End If
          Else
             If iDay = 31 Then  'Make sure new years
                  'doesn't fall on saturday. If so, today is a holiday.
                If Weekday(DateAdd("d", 1, dDate)) = 7 Then
                   IsHoliday = 1
                End If
             End If
          End If
       End If
    End If

        Case Else
            'Do nothing but return false

    End Select
End If

End Function

Open in new window

0
 
LVL 51

Expert Comment

by:Bill Prew
ID: 41757697
Please share the data file you are testing with, and which dates are not changing correctly.

~bp
0
 

Author Comment

by:ammartahir1978
ID: 41757701
Hi Bill,

I have attached all the files which i receive in bulk and tried your last script which didn't change dates so i did it manually.

in this zip file you will see the dates which are not changed are :

104102
102122
112120
115105
102116

Thanks can we not change the file name to Order number so can we rename it to A<DATE>_<ORDERNO>_TIME.CSV? its pain in the backside to go through every singal file and find the one which are not changed.

Thanks for your help.

Regards,

Ammar
A20160816_102111_CHESSPL.zip
0
 
LVL 51

Expert Comment

by:Bill Prew
ID: 41757719
I just ran a test with that set of files here, and all of the following were adjusted, which include the ones you mentioned.

A20160816_101101_CHESSPL.CSV
A20160816_112107_CHESSPL.CSV
A20160816_112113_CHESSPL.CSV
A20160816_112111_CHESSPL.CSV
A20160816_112120_CHESSPL.CSV
A20160816_115105_CHESSPL.CSV
A20160816_102116_CHESSPL.CSV
A20160816_102122_CHESSPL.CSV
A20160816_104102_CHESSPL.CSV

~bp
0
 

Author Comment

by:ammartahir1978
ID: 41757722
oh!

is there any restriction in the script to read x amount of files becasue when i ran it i had 150 files in folder and it doesnt even tell me that it finished the process so i just leave it for 10-20 sec and then transfer them to process them.
0
 
LVL 51

Expert Comment

by:Bill Prew
ID: 41757727
No, it processes all files in the folder.

~bp
0
 

Author Comment

by:ammartahir1978
ID: 41757730
that's very strange because i run the script from command line and also from schedule tasks as well.

thanks for your help.

i will post change of file name separately now. and another question.

Regards,

A
0
 

Author Closing Comment

by:ammartahir1978
ID: 41757731
Great Experts if i can afford i would hire them permanently.
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Recently I have been answering a lot of questions like this in IT forums that I frequent. The question posed is usually something along the lines of "We have software X installed and need to uninstall it for reason Y" or some other variant of the sa…
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

26 Experts available now in Live!

Get 1:1 Help Now