help with business days

[eyegeegeewhy]

Hi

I am having trouble writing a script to reduce a date by a set number of business days. My basic logic is to reduce the date read in from a text file by 15 days using _DateAdd but with a minus number. That seems ok. But this does not take business days into account so Im using a fuction i found here on the forums to check the businessdays differance and then doing a while loop and keep knocking another day off until the check comes back as ok. Probably not the fastest way of doing it but i could think of a better way. Unfortunatly this doesnt seem to work if i check my output manualy using a calender. the dates can come back as weekend dates. Is the problem that the function only works for adding dates?

Any help with this would be great thanks.

Oh my example text file looks like the following see 19/03/2009 as an example problem date.

13/03/2009
17/03/2009
19/03/2009
30/03/2009
31/03/2009
09/04/2009
14/04/2009
17/04/2009
20/04/2009
01/05/2009
27/04/2009
05/05/2009
06/05/2009

expandcollapse popup

#Include <Array.au3>
#include <File.au3>
#include <Date.au3>

Global $DATA_List, $originaldate, $newdateformat,$sNewDate, $newdatearray
$file = FileOpenDialog("select file to process", @ScriptDir & "", "text (*.txt)", 1)

If @error Then
    MsgBox(4096,"","No File(s) chosen")
    Exit
Else
_FileReadToArray($file, $DATA_List)

For $i = 1 To $DATA_List[0]
$originaldate=StringSplit($DATA_List[$i],"/")
$newdateformat=$originaldate[3]&"/"&$originaldate[2]&"/"&$originaldate[1]
$sNewDate = _DateAdd( 'd',-15,$newdateformat)

while 1
if _CalcBusinessDays($newdateformat, $sNewDate) = 15 then ExitLoop
$sNewDate = _DateAdd( 'd',-1,$sNewDate)
WEnd

FileWriteLine($file & "_modified.txt",$newdateformat&","&$sNewDate)
Next
    MsgBox(4096,"","File created " & $file & "_modified.txt")
EndIf


Func _CalcBusinessDays($Date1, $Date2); Dates must be in format YYYY/MM/DD
    Local $TempDate,$dummy
    Local $Days = Abs(_DateDiff("d",$Date1, $Date2))                                                    
    Local $Weeks = Abs(_DateDiff("w",$Date1, $Date2))
    Local $Rest = $Days - ($Weeks * 7)
    Local $BDays = $Weeks * 5
    For $x = 1 To $rest
            _DateTimeSplit(_DateAdd("D",$x * -1,$Date2),$TempDate,$dummy)
            $DayOfWeek = _DateToDayOfWeek($TempDate[1],$TempDate[2],$TempDate[3])
            If Not ($DayOfWeek = 1 Or $DayOfWeek = 7) Then
                $BDays = $BDays + 1
            EndIf
    Next
    Return $BDays
EndFunc

[PsaltyDS]

Hi

I am having trouble writing a script to reduce a date by a set number of business days. My basic logic is to reduce the date read in from a text file by 15 days using _DateAdd but with a minus number. That seems ok. But this does not take business days into account so Im using a fuction i found here on the forums to check the businessdays differance and then doing a while loop and keep knocking another day off until the check comes back as ok. Probably not the fastest way of doing it but i could think of a better way. Unfortunatly this doesnt seem to work if i check my output manualy using a calender. the dates can come back as weekend dates. Is the problem that the function only works for adding dates?

Any help with this would be great thanks.

Oh my example text file looks like the following see 19/03/2009 as an example problem date.

13/03/2009
17/03/2009
19/03/2009
30/03/2009
31/03/2009
09/04/2009
14/04/2009
17/04/2009
20/04/2009
01/05/2009
27/04/2009
05/05/2009
06/05/2009

expandcollapse popup

#Include <Array.au3>
#include <File.au3>
#include <Date.au3>

Global $DATA_List, $originaldate, $newdateformat,$sNewDate, $newdatearray
$file = FileOpenDialog("select file to process", @ScriptDir & "", "text (*.txt)", 1)

If @error Then
    MsgBox(4096,"","No File(s) chosen")
    Exit
Else
_FileReadToArray($file, $DATA_List)

For $i = 1 To $DATA_List[0]
$originaldate=StringSplit($DATA_List[$i],"/")
$newdateformat=$originaldate[3]&"/"&$originaldate[2]&"/"&$originaldate[1]
$sNewDate = _DateAdd( 'd',-15,$newdateformat)

while 1
if _CalcBusinessDays($newdateformat, $sNewDate) = 15 then ExitLoop
$sNewDate = _DateAdd( 'd',-1,$sNewDate)
WEnd

FileWriteLine($file & "_modified.txt",$newdateformat&","&$sNewDate)
Next
    MsgBox(4096,"","File created " & $file & "_modified.txt")
EndIf

Func _CalcBusinessDays($Date1, $Date2); Dates must be in format YYYY/MM/DD
    Local $TempDate,$dummy
    Local $Days = Abs(_DateDiff("d",$Date1, $Date2))                                                    
    Local $Weeks = Abs(_DateDiff("w",$Date1, $Date2))
    Local $Rest = $Days - ($Weeks * 7)
    Local $BDays = $Weeks * 5
    For $x = 1 To $rest
            _DateTimeSplit(_DateAdd("D",$x * -1,$Date2),$TempDate,$dummy)
            $DayOfWeek = _DateToDayOfWeek($TempDate[1],$TempDate[2],$TempDate[3])
            If Not ($DayOfWeek = 1 Or $DayOfWeek = 7) Then
                $BDays = $BDays + 1
            EndIf
    Next
    Return $BDays
EndFunc

This seems simpler for a fixed value of 15 days previous:

#include <Date.au3>

Global $avDates[13] = ["13/03/2009", "17/03/2009", "19/03/2009", "30/03/2009", _
        "31/03/2009", "09/04/2009", "14/04/2009", "17/04/2009", "20/04/2009", _
        "01/05/2009", "27/04/2009", "05/05/2009", "06/05/2009"]
        
Global $iOffset, $avDateSplit, $sNewDate, $sNewDayOfWeek


For $n = 0 to UBound($avDates) - 1
    $avDateSplit = StringSplit($avDates[$n], "/")
    Switch _DateToDayOfWeek($avDateSplit[3], $avDateSplit[2], $avDateSplit[1])
        Case 1; Sunday
            $iOffset = -27
        Case 2 To 6; Monday thru Friday
            $iOffset = -21
        Case 7; Saturday
            $iOffset = -26
    EndSwitch
    $sNewDate = _DateAdd("D", $iOffset, $avDateSplit[3] & "/" & $avDateSplit[2] & "/" & $avDateSplit[1])
    $avDateSplit = StringSplit($sNewDate, "/")
    $sNewDate = $avDateSplit[3] & "/" & $avDateSplit[2] & "/" & $avDateSplit[1]
    ConsoleWrite("Fifteen business days before " & $avDates[$n] & " was " & $sNewDate & "." & @LF)
Next

It would be even simpler if your dates were formatted logically YYYY/MM/DD.

Making it take a variable number of business days only requires the math to set $iOffset differently.