|
|
|||||||
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. |
||||||||
|
|
|||||||
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:Untested, but one manual calculation gave the correct result. YMMV$starttdate=serialdate($startdate) |
||||||||
|
|
|||||||
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:BrianFunction MOD($num1,$num2) |
||||||||
|
|
|||||||
There's already a MOD() UDF posted, too. |
||||||||
|
|
|||||||
It was almost as quick to write one as to look up one.. although I admit.. the other one is better. Brian |
||||||||
|
|
|||||||
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 [ 04 June 2002, 22:49: Message edited by: New Mexico Mark ] |
||||||||
|
|
|||||||
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:Yes, I'm sure there is an easier way... my brain is semi-fried though... good luck.$weekdayarray = 0,1,1,1,1,1,0,0,1,1,1,1,1,0 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 ] |
||||||||
|
|
|||||||
Thanks everyone for your help. Here is the latest version. Instead of an array, I just check ranges. code:New Mexico MarkFUNCTION WorkDaysDiff($sStrtDte, $sEndDte) |
||||||||
|
|
|||||||
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 |
||||||||
|
|
|||||||
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 |