Page 1 of 1 1
Topic Options
#66103 - 2002-06-04 09:35 PM Question for math minds
New Mexico Mark Offline
Hey THIS is FUN
****

Registered: 2002-01-03
Posts: 223
Loc: Columbia, SC
I'm trying to come up with an algorithm to calculate the number of week days (i.e. Monday through Fridays) between today and a date in the past. Because I eventually plan to translate this to P-SQL, I need to keep it very simple. Any ideas?

Getting the number of days difference and the current day of the week is easy. But I'm stuck on how to eliminate Saturdays and Sundays from the day count using math only.

Any help would be greatly appreciated.

Top
#66104 - 2002-06-04 09:52 PM Re: Question for math minds
Sealeopard Offline
KiX Master
*****

Registered: 2001-04-25
Posts: 11162
Loc: Boston, MA, USA
Use the SerialDate function to get the number of days between your two dates. Substract the number of days from today to the last Sunday. Then divide the rest through 7. This will give you the number of weeks from last Sunday back to your target date. Substract 2 from the MOD 7. This is the number of weekdays from the very first Sunday.
code:
$starttdate=serialdate($startdate)
$enddate=serialdate($enddate)
$numberdays=$enddate-$startdate+1
$thisweek=@WDAYNO
$numberdays=$numberdays-$thisweek
$numberweeks=$numberdays/7
$numberdays=$numberdays MOD 7
$numberdays=$numberdays-2
$numberdays=$numberdays+$numberweeks*5
$numberdays=$numberdays+$thisweek

Untested, but one manual calculation gave the correct result. YMMV
_________________________
There are two types of vessels, submarines and targets.

Top
#66105 - 2002-06-04 10:12 PM Re: Question for math minds
BrianTX Offline
Korg Regular

Registered: 2002-04-01
Posts: 895
What version of KiXtart supports MOD?

Here is a MOD function if you don't have the latest (RC) version (could be adapted to non-function form):

code:
Function MOD($num1,$num2)
$n1 = $num1/$num2
$c=0
Do
$c=$c+1
$n2 = ($num1+$c)/$num2
Until $n2 <> $n1
$mod = $num2 - $c
EndFunction

Brian

Top
#66106 - 2002-06-04 10:17 PM Re: Question for math minds
Sealeopard Offline
KiX Master
*****

Registered: 2001-04-25
Posts: 11162
Loc: Boston, MA, USA
There's already a MOD() UDF posted, too.
_________________________
There are two types of vessels, submarines and targets.

Top
#66107 - 2002-06-04 10:28 PM Re: Question for math minds
BrianTX Offline
Korg Regular

Registered: 2002-04-01
Posts: 895
It was almost as quick to write one as to look up one.. although I admit.. the other one is better.

Brian

Top
#66108 - 2002-06-04 10:36 PM Re: Question for math minds
New Mexico Mark Offline
Hey THIS is FUN
****

Registered: 2002-01-03
Posts: 223
Loc: Columbia, SC
Here is my test code. I'm still not getting values I would expect. For instance, I would expect 6/3/02 to 6/4/02 to return 1, but 6/2/02 to 6/3/02 to return 0.

code:
FOR $i = 1 TO 7
FOR $j = 1 TO 7
$tDt=Flip_SerDate(@DATE)
$sDt=Flip_SerDate($tDt - ($j + $i))
'The start date is: ' + $sDt ?
$eDt=Flip_SerDate($tDt - $j)
'The end date is: ' + $eDt ?
'Elapsed week days: ' + WeekDaysDiff($sDt,$eDt) ? ?
NEXT
NEXT

FUNCTION WeekDaysDiff($sStrtDte, $sEndDte)
DIM $iDaysDiff, $iStrtDte, $iEndDte, $iWeeks
$iStrtDte = Flip_SerDate($sStrtDte)
$iEndDte = Flip_SerDate($sEndDte)
$iDaysDiff = $iEndDte - $iStrtDte + 1
$iDaysDiff = $iDaysDiff - @WDAYNO
$iWeeks = $iDaysDiff / 7
$iDaysDiff = $iDaysDiff-(($iDaysDiff/7)*7) ; $iDaysDiff mod 7
$WeekDaysDiff = $iDaysDiff + ($iWeeks * 5) + @WDAYNO
ENDFUNCTION

FUNCTION Flip_SerDate($Var)
; Parameter $Var must be either a date (yyyy/mm/dd) or an integer previously
; derived by this function.
; If passed a date, it returns an integer and vice versa.
; Algorithms were obtained from: http://www.capecod.net/~pbaum/date/date0.htm
DIM $Z, $H, $A, $B, $C, $Y, $M, $D, $aDate
IF InStr($Var,'/') ; date passed
$aDate = Split($Var+'///','/',3)
$Y=Val($aDate[0]) $M=Val($aDate[1]) $D=Val($aDate[2])
IF $M < 3 $M=$M+12 $Y=$Y-1 ENDIF
; return an integer
$Flip_SerDate=$D+(153*$M-457)/5+365*$Y+$Y/4-$Y/100+$Y/400-306
ELSE ; integer passed
$Z = Val($Var)+306 ; force numeric
$H = 100*$z-25
$A = $H/3652425
$B = $A-$A/4
$Y = (100*$B+$H)/36525
$C = $B+$Z-365*$Y-$Y/4
$M = (5*$C+456)/153
$D = $C-(153*$M-457)/5
IF $M > 12 $Y=$Y+1 $M=1 ENDIF
$M=Right('00'+$M,2)
$D=Right('00'+$D,2)
; return a string date
$Flip_SerDate=''+$Y+'/'+$M+'/'+$D
ENDIF
ENDFUNCTION



[ 04 June 2002, 22:49: Message edited by: New Mexico Mark ]

Top
#66109 - 2002-06-04 11:14 PM Re: Question for math minds
BrianTX Offline
Korg Regular

Registered: 2002-04-01
Posts: 895
Hmmm:

1. Why are you adding 1 to the difference between your start and end date?

If you get this number first, you should be able to caculate the number of weekdays between except for the modulus part..

2. Once you get that number taken care of, all you have to do is find how many weekdays have passed on the past MODULUS number of days.

If you have the @WDAYNO you can figure out how many Weekdays came before.

I'm sure a pure math formula will do this, but because I can't think of one...

code:
$weekdayarray = 0,1,1,1,1,1,0,0,1,1,1,1,1,0
$wdayno = VAL("@Wdayno")
IF $MOD > $wdayno $Wdayno = $wdayno + 7 endif

for $n = ($WDAYNO - 1) to ($WDAYNO - $MOD) STEP -1
$weekdaysinmodulus = $weekdaysinmodulus + $weekdayarray[($n-1)]
Next

Yes, I'm sure there is an easier way... my brain is semi-fried though... good luck.

Brian
{edit} bear with me.. still messing with this.. not working right..

{edit} it appears to work.. needs further testing..

[ 04 June 2002, 23:35: Message edited by: BrianTX ]

Top
#66110 - 2002-06-05 03:16 PM Re: Question for math minds
New Mexico Mark Offline
Hey THIS is FUN
****

Registered: 2002-01-03
Posts: 223
Loc: Columbia, SC
Thanks everyone for your help. Here is the latest version. Instead of an array, I just check ranges.

code:
FUNCTION WorkDaysDiff($sStrtDte, $sEndDte)
; $sStrtDte and $sEndDte are date strings in the form YYYY/MM/DD
; Function returns the integer number of work days between the two
; dates.
; Dependencies: Flip_SerDate() or SerialDate()
; Note: If a serial date function is added to KiXtart, the iEndDOW
; calculation may need to be adjusted to get the correct DOW.
DIM $iDaysDiff, $iEndDte, $iWeeks, $iEndDOW, $iCtr
$iEndDte=Flip_SerDate($sEndDte)
$iDaysDiff=$iEndDte-Flip_SerDate($sStrtDte)
$iWeeks=$iDaysDiff/7 ; Remove full weeks, since each contains 5 work days
$iDaysDiff=$iDaysDiff-(($iDaysDiff/7)*7) ; $iDaysDiff mod 7 = remainder of days
$iEndDOW=($iEndDte-(($iEndDte/7)*7))+1 ; Returns correct DOW for the end date
$iStrtDOW=$iEndDOW-$iDaysDiff
IF $iStrtDOW < 0 $iStrtDOW=$iStrtDOW+7 $iEndDOW=$iEndDOW+7 ENDIF
FOR $iCtr = $iStrtDOW TO $iEndDOW-1 ; Eliminate last day from count
IF (($iCtr>1) AND ($iCtr<7)) OR (($iCtr>8) AND ($iCtr<12))
$WorkDaysDiff=$WorkDaysDiff+1
ENDIF
NEXT
$WorkDaysDiff=$WorkDaysDiff+($iWeeks*5)
ENDFUNCTION

New Mexico Mark

Top
#66111 - 2002-06-05 11:54 PM Re: Question for math minds
BrianTX Offline
Korg Regular

Registered: 2002-04-01
Posts: 895
That looks great, Mark! As far as the day of the week.. I don't know if you need to add a constant or not. It should be pretty simple to test, though.

Brian

Top
#66112 - 2002-06-06 05:53 AM Re: Question for math minds
New Mexico Mark Offline
Hey THIS is FUN
****

Registered: 2002-01-03
Posts: 223
Loc: Columbia, SC
I checked. Essentially, the algorithm for SerialDate returns an integer. Taking the result mod 7 gives a number that is one lower than the current DOW.

I'm pulling my hair out again since I decided to make this a UDF for submission. I changed the logic some so the syntax is:

function WorkDaysDiff(optional date, optional diff)
Date can be any date (today by default) and diff can be either another date or an integer.

This provides a great deal of flexibility to the function, but makes the programming a little trickier, since we can now be looking back or forward from the start date.

But that is half the fun!

Thanks for the help.

New Mexico Mark

Top
Page 1 of 1 1


Moderator:  Glenn Barnas, NTDOC, Arend_, Jochen, Radimus, Allen, ShaneEP, Ruud van Velsen, Mart 
Hop to:
Shout Box

Who's Online
1 registered (ShaneEP) and 237 anonymous users online.
Newest Members
dimi575, Drecksnacken, Nlill, webtools321, multiman
17734 Registered Users

Generated in 0.034 seconds in which 0.012 seconds were spent on a total of 12 queries. Zlib compression enabled.

Search the board with:
superb Board Search
or try with google:
Google
Web kixtart.org