Wednesday, December 13, 2017
  • Index
  •  » Code Exchange
  •  » A handler for calculating number of workingdays between 2 dates.

#1 2015-05-12 07:12:17 am

McUsrII
Member
Registered: 2012-11-21
Posts: 3046
Website

A handler for calculating number of workingdays between 2 dates.

Hello.

Calculate the number of workdays between two dates.


It does what it says, it calculates any workdays between a startDate and an endDate. The current day is included  if it is a workday. The end day is not included. It handles time of day in a peculiar way: If the time of the start day is greater than the time of the end day, then the start day is subtracted, unless the end date is during a weekend.

The idea is that when you track something during a week, then it is about workdays, so if you have an event scheduled during a working day, then you count down whole working days to that event. If the event falls within a weekend, then the hours of a working day isn't considered, so say a friday before something happening on a saturday is considered to be a full day.

If you have other days you want to subtract from workingdays, after you have checked that they are within the timespan, and that the days didn't fall on a saturday or sunday this year, then you subtract those from the number of days returned from this handler. smile

You should check that the difference between the end date and start date is not lesser than 0 days.

Applescript:


on workDays(startDate, endDate)
   -- [url]http://macscripter.net/viewtopic.php?pid=180688#p180688[/url]
   -- Copyright © 2015 McUsr, all rights reserved, you may not publish this standalone anywhere else, or take credit for it.
   -- You may use it as you see fit, if its functionality is needed for something else.
   -- Returns the number of working days in a date interval.
   -- working days are the days mondays thru friday.
   -- There are two regimes here:
   -- 1. The end date is on a working date. We consider the hours.
   -- 2. The end date is during a weekend. -We ignore any hours.
   copy {startDate, endDate} to {startProbe, endProbe}
   set startProbe's time to 0
   set endProbe's time to 0
   set daydiff to (endProbe - startProbe) div days
   set wkdStart to startProbe's weekday as integer
   set wkdEnd to endProbe's weekday as integer
   
   if daydiff > 0 then
       
       if daydiff < 7 then
           if wkdStart > wkdEnd then
               if wkdStart < 7 and wkdEnd > 1 then
                   set workingdays to daydiff - 2
               else
                   set workingdays to daydiff - 1
               end if
           else
               set workingdays to daydiff -- the current day counts as a workingday
               if wkdEnd = 7 and wkdStart = 1 then set workingdays to workingdays - 1 -- wkdEnd can't be 1
           end if
           if wkdEnd is not in {1, 7} and wkdStart is not in {1, 7} and startDate's time > endDate's time then ¬
               set workingdays to workingdays - 1
           return workingdays
       else if daydiff mod 7 = 0 then
           -- Thanks to Nigel Garvey for spotting the bug.
           set workingdays to daydiff - 2 * daydiff div 7
           if wkdStart is not in {1, 7} and startDate's time > endDate's time then set workingdays to workingdays - 1
           return workingdays
       else
           
           if wkdStart > 1 then
               set wkStartWkDays to (7 - wkdStart)
               if wkStartWkDays > 0 then
                   set daydiff to daydiff - wkStartWkDays
               else
                   set daydiff to daydiff - 2
               end if
           else
               set daydiff to daydiff - 1
               set wkStartWkDays to 0
           end if
           
           if wkdEnd < 7 and wkdEnd > 1 then
               set wkEndWkDays to wkdEnd - 2 -- The end day doesn't count
               set daydiff to daydiff - wkdEnd
           else
               set daydiff to daydiff - 2
               set wkEndWkDays to 0
           end if
           
           set daydiff to daydiff - ((daydiff div 7) * 2)
           set workingdays to wkStartWkDays + daydiff + wkEndWkDays
       end if
       
       if (wkdStart is not in {1, 7} and wkdEnd is not in {1, 7}) and startDate's time > endDate's time then
           set workingdays to workingdays - 1
       else if wkdStart is in {1, 7} and wkdEnd is in {1, 7} and startDate's time > endDate's time then
           set workingdays to workingdays + 1
       end if
       return workingdays
   else
       return 0
   end if
end workDays

Edit
14:00 CEST: I have simplified the handler, just a little bit, without changing any functionality.
2015/05/15 04.55 CEST: Removed some unnecessary code, given the way it works now.

Last edited by McUsrII (2015-05-14 09:44:38 pm)


Filed under: date, working days

Offline

 

#2 2015-05-13 07:20:11 am

Nigel Garvey
Moderator
From:: Warwickshire, England
Registered: 2002-11-20
Posts: 4452

Re: A handler for calculating number of workingdays between 2 dates.

Hi McUsrII.

If the start date and end date are exactly a week apart, your handler returns 5 as expected. But with other multiples of a week, it returns the appropriate multiple of 5 plus one. I haven't yet checked its consistency in other respects.

Here's my take on the task:

Applescript:


(* Given two AppleScript dates, return how many working calendar days they are apart. The dates needn't be working days themselves. No provision is made for public holidays. *)

on workDays(startDate, endDate)
   -- Get the difference between the dates in calendar days.
   set dayDiff to (endDate - (startDate - (startDate's time))) div days
   -- There are five working days in each complete set of seven.
   set dayDiffWholeWeeks to dayDiff div 7 * 5
   -- Get the number of days left over from the grouping into sevens.
   set dayDiffOddDays to dayDiff mod 7
   -- If any, check the weekdays of the first and last. The first has the same weekday as the start date.
   if (dayDiffOddDays > 0) then
       set {startWeekday, endWeekday} to {startDate's weekday, endDate's weekday}
       if ((startWeekday is Saturday) or (endWeekday is Saturday)) then
           -- If either the first or the last of these odd days is a Saturday, subtract a day.
           set dayDiffOddDays to dayDiffOddDays - 1
       else if (endWeekday comes before startWeekday) then
           -- Otherwise, if they're in different calendar weeks, subtract two days.
           set dayDiffOddDays to dayDiffOddDays - 2
       end if
   end if
   
   return dayDiffWholeWeeks + dayDiffOddDays
end workDays

Edit: Changed the "odd days" handling, as the original didn't work in all cases.

Last edited by Nigel Garvey (2015-05-14 08:14:57 am)


NG

Offline

 

#3 2015-05-13 11:47:20 am

McUsrII
Member
Registered: 2012-11-21
Posts: 3046
Website

Re: A handler for calculating number of workingdays between 2 dates.

Hello.

Thanks for spotting the bug! It is now fixed. I must say your handler turned out very sleek, I like especially how you solved the overlapping weekday problem, which is far more elegant than what I came up with. As for consistency of my handler, if the current day is a work day, then it is supposed to count as a work day, in other words, it is included among the work days. And, if the end day is a workday, then it is included 2, so for instance my handler counts a time interval from friday 1 of may, until monday 4th as 2 work days, and so on. (Whole weeks are excempt from this, because a whole week always contains 5 workdays, before any public hollidays are considered. smile

Edit

Further testing revealed one more bug: when the days were fewer than seven, and todays daynumber came before the endday, then the handler returned a day to little.

Last edited by McUsrII (2015-05-13 01:00:03 pm)

Offline

 

#4 2015-05-13 01:14:53 pm

McUsrII
Member
Registered: 2012-11-21
Posts: 3046
Website

Re: A handler for calculating number of workingdays between 2 dates.

Hello.

I have inspected my handler a little, and reworked the logic: It now doesn't count the last day among working days, if the end date happens on a working day. That way you can change granularity to hours when it is less than one day left.
I also removed a rounding error caused to an "as integer" coercion.

The idea is, that say if your holliday starts on friday 2 in the after noon, it will report 1 day left at thursday before 2, but zero days after 2 o'clock on thursday. Likewise, if your holliday starts on monday at 4 o'clock, then it should report 1 day left on friday before 4 o'clock. If today is friday, and your holliday starts next friday, then it reports that you have 4 working days left, because the friday it happens on, doesn't count as a day.

There is still an inaccuracy in here, for what its worth: say if it is after working hours on a workingday, then it reports one day too much in some cases.

Last edited by McUsrII (2015-05-13 02:07:29 pm)

Offline

 

#5 2015-05-13 02:15:20 pm

McUsrII
Member
Registered: 2012-11-21
Posts: 3046
Website

Re: A handler for calculating number of workingdays between 2 dates.

Hello.

The handler should now also be totally accurate with respect to time, I'll rewrite how it works soon.

Offline

 

#6 2015-05-13 02:48:15 pm

McUsrII
Member
Registered: 2012-11-21
Posts: 3046
Website

Re: A handler for calculating number of workingdays between 2 dates.

Hello.

It is further amended, so that it doesn't consider the end time of a workingday, if the end date is during a weekend.

Say something is happening at saturday at 2 o'clock in the afternoon. then if it is 5 o'clock in the afternoon at the friday 1 week before, then that counts as 6 working days. (The friday right before saturday, now counts as 1 workingday in this case.)

Offline

 

#7 2015-05-13 03:35:16 pm

McUsrII
Member
Registered: 2012-11-21
Posts: 3046
Website

Re: A handler for calculating number of workingdays between 2 dates.

Hello.

I have added the provision for when the start time is greater than the end time for the clause for days less than 7 days and rewritten the description of what the handler does.

Offline

 

#8 2015-05-13 04:11:34 pm

McUsrII
Member
Registered: 2012-11-21
Posts: 3046
Website

Re: A handler for calculating number of workingdays between 2 dates.

Hello.

I'm not done with this, I am going to rewrite the whole thing, the times I added complicated it too much.

Offline

 

#9 2015-05-14 06:26:41 am

McUsrII
Member
Registered: 2012-11-21
Posts: 3046
Website

Re: A handler for calculating number of workingdays between 2 dates.

Hello.

It should work as intended now, I really had to use regression testing in order to make it work in all cases, all in all at least an experience. smile


I have stated above how the handler in post #1 works, but just to sum it up, it counts up working days, in an "hour aware manner": If you have a start date, with a timestamp, that is after the timestamp of the end date, then one day is subtracted. -Unless, the end date happens in a weekend, then the timestamp arent considered. The enddate is not considered to be a work day, so we count up work days until some day.

Example. The startdate is a monday, at 12 o'clock, the end date is at friday at 2 o'clock: then you get 4 workingdays returned. If the startdate is still monday, but the time is 3 o'clock in the afternoon, and we relate to the same friday, then the handler returns 3 workdays.

if the endate occurs on the first saturday, at 11 o'clock, then 5 workdays are returned, since the time is not considered during weekends.

Last edited by McUsrII (2015-05-14 06:27:03 am)

Offline

 

#10 2015-05-14 08:22:57 am

Nigel Garvey
Moderator
From:: Warwickshire, England
Registered: 2002-11-20
Posts: 4452

Re: A handler for calculating number of workingdays between 2 dates.

McUsrII wrote:

I must say your handler turned out very sleek, I like especially how you solved the overlapping weekday problem, which is far more elegant than what I came up with.


Too elegant for its own good.  wink  It turned out not to work in all cases. I've now modified the code in post #2 to use a different method with "odd days".


NG

Offline

 

#11 2015-05-14 09:11:38 am

McUsrII
Member
Registered: 2012-11-21
Posts: 3046
Website

Re: A handler for calculating number of workingdays between 2 dates.

Hello Nigel.

It wasn't as easy as I thought it to be either, though I have added some extra functionality. I must say that your handler looks a whole lot better than mine. But I have to say, that my handler became a totally different beast.

This was actually a very discomforting experience, I had to isolate the different ongoings, (which is why I return from 3 different places), I also had to isolate the times from the dates, -like you do, in order to make it work as I intended.

I am going to use my handler, and not do anything that makes the regression tests fail, -that is, that break the heuristics. smile

Offline

 

#12 2015-05-14 10:59:46 am

Nigel Garvey
Moderator
From:: Warwickshire, England
Registered: 2002-11-20
Posts: 4452

Re: A handler for calculating number of workingdays between 2 dates.

Hi McUsrII.

I'm afraid your handler still seems to be producing inconsistent results — according to this test script, anyway (add your handler before running!):

Applescript:


set testResults to {}
set startDate to date "Sunday 10 May 2015 00:00:00"

-- Build a list of seven lists, each containing a different start date weekday and the workDay() results for 8 end dates (same as the start date to one week later). All the dates have the same time.
repeat 7 times
   set l to {startDate's weekday}
   repeat with d from 0 to 7
       set endDate to startDate + d * days
       set end of l to workDays(startDate, endDate)
   end repeat
   
   set end of testResults to l
   set startDate to startDate + days
end repeat

testResults
--> {{Sunday, 0, 1, 2, 3, 4, 5, 5, 5}, {Monday, 0, 1, 2, 3, 4, 5, 4, 5}, {Tuesday, 0, 1, 2, 3, 4, 3, 4, 5}, {Wednesday, 0, 1, 2, 3, 2, 3, 4, 5}, {Thursday, 0, 1, 2, 1, 2, 3, 4, 5}, {Friday, 0, 1, 0, 1, 2, 3, 4, 5}, {Saturday, 0, 0, 1, 2, 3, 4, 5, 5}}

For comparison, the results with my handler are:

Applescript:

--> {{Sunday, 0, 1, 2, 3, 4, 5, 5, 5}, {Monday, 0, 1, 2, 3, 4, 4, 4, 5}, {Tuesday, 0, 1, 2, 3, 3, 3, 4, 5}, {Wednesday, 0, 1, 2, 2, 2, 3, 4, 5}, {Thursday, 0, 1, 1, 1, 2, 3, 4, 5}, {Friday, 0, 0, 0, 1, 2, 3, 4, 5}, {Saturday, 0, 0, 1, 2, 3, 4, 5, 5}}

Last edited by Nigel Garvey (2015-05-14 11:00:04 am)


NG

Offline

 

#13 2015-05-14 12:11:28 pm

McUsrII
Member
Registered: 2012-11-21
Posts: 3046
Website

Re: A handler for calculating number of workingdays between 2 dates.

Hello Nigel.

Great test script! smile

I may have forgotten to update my script with the last changes, (I have done so now) because when I run my handler through your test, then I get a result that is not the same as yours, but consistent, given how my handler works, (all results have been carefully checked.) 

Here are my results, and, I'm glad it didn't fail. big_smile

{Sunday, 0, 1, 2, 3, 4, 5, 5, 5}, {Monday, 0, 1, 2, 3, 4, 5, 5, 5}, {Tuesday, 0, 1, 2, 3, 4, 4, 4, 5}, {Wednesday, 0, 1, 2, 3, 3, 3, 4, 5}, {Thursday, 0, 1, 2, 2, 2, 3, 4, 5}, {Friday, 0, 1, 1, 1, 2, 3, 4, 5}, {Saturday, 0, 0, 1, 2, 3, 4, 5, 5}


By the way, below is my take on regression testing. I wrote tests, that I let the handler go through, while I adjusted it, so that I didn't introduce any new errors, while I improved it.

Applescript:

set sd to (current date)
copy sd to fd

# Test cases for end day is on a weekend
-- Normal case, earlier on start day than on end day.
set hours of sd to 13
set day of sd to 15
set day of fd to 16
set hours of fd to 14


set wd to workDays(sd, fd)
if wd = 1 then
   log "passed 1"
else
   log "Failed 1"
end if
-- Special case, later on start day than on end day.


set hours of sd to 15
set day of sd to 15
set day of fd to 16
set hours of fd to 14

set wd to workDays(sd, fd)

if wd = 1 then
   log "passed 2"
else
   log "Failed 2"
end if

# Test case for days < 7
-- earlier on start day than on end day

set hours of sd to 13
set day of sd to 12
set day of fd to 14
set hours of fd to 14

set wd to workDays(sd, fd)

if wd = 2 then
   log "passed 3"
else
   log "Failed 3"
end if

-- later on start date, should return 1 day less.
(*
set hours of sd to 15
set wd to workDays(sd, fd)

if wd = 1 then
   log "passed 4"
else
   log "Failed 4"
end if
*)


# days < 7 but weekday of enddate before weekday of startdate

set hours of sd to 13
set day of sd to 8
set day of fd to 14
set hours of fd to 14

set wd to workDays(sd, fd)

if wd = 4 then
   log "passed 5"
else
   log "Failed 5"
end if


set hours of sd to 15

set wd to workDays(sd, fd)

if wd = 3 then
   log "passed 6"
else
   log "Failed 6"
end if

# Tests of whole weeks.

-- The "normal case" :

set hours of sd to 13
set day of sd to 7
set day of fd to 14
set hours of fd to 14

set wd to workDays(sd, fd)


if wd = 5 then
   log "passed 7"
else
   log "Failed 7"
end if





-- The case when it ends on a saturday

set hours of sd to 13
set day of sd to 10
set day of fd to 16
set hours of fd to 14

set wd to workDays(sd, fd)


if wd = 5 then
   log "passed 8"
else
   log "Failed 8"
end if



-- it ends on a saturday, and the start sunday is after end on sat

set hours of sd to 15

-- The case with multiple weeks
set wd to workDays(sd, fd)


if wd = 5 then
   log "passed 9"
else
   log "Failed 9"
end if


# Test for a whole week: from saturday to saturday

set hours of sd to 13
set day of sd to 9
set day of fd to 16
set hours of fd to 14

set wd to workDays(sd, fd)


if wd = 5 then
   log "passed 10"
else
   log "Failed 10"
end if

# From saturday to saturday, but ends earlier than starts.

set hours of sd to 15

set wd to workDays(sd, fd)


if wd = 5 then
   log "passed 11"
else
   log "Failed 11"
end if

# Test for a whole week: from monday to monday

set hours of sd to 13
set day of sd to 11
set day of fd to 18
set hours of fd to 14

set wd to workDays(sd, fd)


if wd = 5 then
   log "passed 12"
else
   log "Failed 12"
end if

# Test for a whole week: from monday to monday but ends earlier than starts.

set hours of sd to 15

set wd to workDays(sd, fd)


if wd = 4 then
   log "passed 13"
else
   log "Failed 13"
end if

# test for a whole week when we originally started out with 8 days
set hours of sd to 13
set day of sd to 11
set day of fd to 19
set hours of fd to 14
set wd to workDays(sd, fd)


if wd = 6 then
   log "passed 14"
else
   log "Failed 14"
end if

# test for a whole week when we originally started out with 8 days from sunday to sunday
set day of sd to 9
set hours of sd to 15
set day of fd to 17
set hours of fd to 14
set wd to workDays(sd, fd)


if wd = 5 then
   log "passed 15"
else
   log "Failed 15"
end if



# Test for a whole WEEKS: from monday to monday but ends earlier than starts.

set hours of sd to 15
set day of sd to 11
set day of fd to 25
set hours of fd to 14

set wd to workDays(sd, fd)

if wd = 9 then
   log "passed 16"
else
   log "Failed 16"
end if


set hours of sd to 13

set wd to workDays(sd, fd)

if wd = 10 then
   log "passed 17"
else
   log "Failed 17"
end if


# Test for a random number of days:

-- Wed the first week until thursday next week with one week in between

set day of sd to 6
set hours of sd to 13
set day of fd to 21
set hours of fd to 14

set wd to workDays(sd, fd)

if wd = 11 then
   log "passed 18"
else
   log "Failed 18"
end if

-- Wed the first week until thursday next week with one week in between, wednesday after thursday.

set hours of sd to 15
set wd to workDays(sd, fd)

if wd = 10 then
   log "passed 19"
else
   log "Failed 19"
end if

set day of sd to 15
set hours of sd to 13
set day of fd to 25
set hours of fd to 14
set wd to workDays(sd, fd)

if wd = 6 then
   log "passed 20"
else
   log "Failed 20"
end if

set day of sd to 16
set hours of sd to 13
set day of fd to 25
set hours of fd to 14
set wd to workDays(sd, fd)

if wd = 5 then
   log "passed 21"
else
   log "Failed 21"
end if

set day of sd to 15
set hours of sd to 15
set day of fd to 25
set hours of fd to 14
set wd to workDays(sd, fd)

if wd = 5 then
   log "passed 22"
else
   log "Failed 22"
end if

set day of sd to 16
set hours of sd to 15
set day of fd to 25
set hours of fd to 14
set wd to workDays(sd, fd)

if wd = 5 then
   log "passed 23"
else
   log "Failed 23"
end if

set day of sd to 17
set hours of sd to 15
set day of fd to 25
set hours of fd to 14
set wd to workDays(sd, fd)

if wd = 5 then
   log "passed 24"
else
   log "Failed 24"
end if

set day of sd to 15
set hours of sd to 16
set day of fd to 16
set hours of fd to 14
set wd to workDays(sd, fd)
if wd = 1 then
   log "passed 24"
else
   log "Failed 24"
end if

Last edited by McUsrII (2015-05-14 12:20:47 pm)

Offline

 
  • Index
  •  » Code Exchange
  •  » A handler for calculating number of workingdays between 2 dates.

Board footer

Powered by FluxBB

RSS (new topics) RSS (active topics)