Source language: Translate to:

Count working days

Questions about using NeoBook's scripting language

Moderator: Neosoft Support

Count working days

Postby smartmedia » Sat Apr 07, 2007 8:26 am

Hi...

I have a problem and i need help...
I like to count the working days between two dates...

Rules:
The working days is 5. From Monday to Friday...
Some days are off because are national celebrations.

Pattern:
Start date: dd/mm/YYYY
End date: dd/mm/YYYY

Example:
I want to count how many working days are from 15/02/2008 until 05/03/2008. In the example there is not any day off.


Any workaround...???
I thing Ronnie has a section for days in rtSystem Manager but i didn't try yet ...


( Sam ) i need your knowledge...
Thanks
User avatar
smartmedia
 
Posts: 889
Joined: Fri Apr 01, 2005 6:50 am
Location: Hellas

Postby Sam Cox » Sat Apr 07, 2007 8:55 am

You will need two things:

1) A function or subroutine to compute the day of week given a date.
2) A list of dates that are non-working (e.g., holidays).

For the first, Gaev once wrote a subroutine to return a day-of-week number. If I recall correctly, 0=Sunday, 1=Monday, ..., 6=Saturday. He may still have and be willing to share that code.

Given 1) and 2), the basic algorithm is:
Code: Select all
set [NumWorkingDays] = 0
for each [Date]
  compute [DayNumber] from [Date] using Gaev's subroutine
  if [DayNumber] is normally a working day
    if [Date] is not a holiday (learned by looking in list of holidays)
      add 1 to [NumWorkingDays]
    endif
  endif
end
User avatar
Sam Cox
 
Posts: 768
Joined: Fri Apr 01, 2005 7:30 am
Location: Loveland CO USA

Postby smartmedia » Sun Apr 08, 2007 12:53 am

Hi...

Thanks Sam, i try some that i have in mind and be back with the results...

Happy Easter...

Thanks
User avatar
smartmedia
 
Posts: 889
Joined: Fri Apr 01, 2005 6:50 am
Location: Hellas

Postby Gaev » Sun Apr 08, 2007 3:55 am

Sam:

I remember discussing a Javascript based solution ... but I had to do a search on the forum to find my own (other) suggestion from years ago :-)

However, after NeoBook added the DateToNum command, it becomes quite easy to get the Day of the Week for any given Date ... by calibrating ...

i) either [Year], [MonthNum], [DayNum] against [Day]

ii) or picking a very old date against its known Day of the week

... and then comparing the DateToNum results of a given date against that for one of the above ... divide the difference by 7 ... adjust for the remainder (modulus) ... and you can get the Day of the week for any specified Date.


smartmedia:

Sam's suggested method is perfect for calculating number of days between short/medium date ranges ... but it might take considerable time (with NeoBook scripting) to loop through date ranges that are (say) 2 or more years (730+ days) apart.

In which case one might consider another approach ... where all "dates" are in the converted "number" format

a) beginning with StartDate, find the first non-holiday and non-weekend date ... i.e. if StartDate falls on a Saturday, add 2 to it ; if it falls on a Sunday add 1 to it ; else add nothing.

b) reversing from EndDate, find the first non-holiday and non-weekend date ... i.e. if EndDate falls on a Saturday, subtract 1 from it ; if it falls on a Sunday subtract 2 from it ; else subtract nothing.

c) calculate number of "whole weeks" and "extra days" in the difference between (a) and (b)

d) for each "national holiday" that falls on/between (a) and (b) ... and does not fall on a weekend ... add to a "count of weekday holidays"

e) Finally ...

- multiply "whole weeks" in (c) by 5
- add "extra days" in (c)
- subtract "count of weekday holidays"

... more code to be developed ... but faster execution time to get results for long date ranges.
User avatar
Gaev
 
Posts: 3734
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada


Return to NeoBook Action Commands

Who is online

Users browsing this forum: No registered users and 1 guest