Jump to content
Sign in to follow this  
eyegeegeewhy

help with business days

Recommended Posts

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

#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

Share this post


Link to post
Share on other sites
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

#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.

:)


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.