calculating business days in SPSS

[LaraVDB]

Hi,

I am trying to calculate the business days between 2 dates.
The simple calculation below is actually not good enough because the difference is often less than 7 days:

COMPUTE difference=CTIME.DAYS(date1-date2)*5/7.
EXECUTE.

I found some examples on this forum using 'loop', but those are not working when I try.

Can someone help me?

KR,
Lara

[David Marso]

"I found some examples on this forum using 'loop', but those are not working when I try.
Can someone help me? "


Errr, maybe you should  repost these and explain exactly what seems to be the problem?
What exactly do you define as business days?  
You mean Xmas and Easter? Thanksgiving? blah blah blah etc?
Try again with an explicit definition of exactly what you are referring to.

LaraVDB wrote

Hi,

I am trying to calculate the business days between 2 dates.
The simple calculation below is actually not good enough because the difference is often less than 7 days:

COMPUTE difference=CTIME.DAYS(date1-date2)*5/7.
EXECUTE.

I found some examples on this forum using 'loop', but those are not working when I try.

Can someone help me?

KR,
Lara

[Jon Peck]

If you just want to exclude specific weekdays, you can do this using the SPSSINC TRANS extension command and the extendedTransforms module, both of which are normally installed with Statistics.  If extendedTransforms.py is not found, it would need to be installed from the SPSS Community website (more info available if needed).

 

Here is an example.

SPSSINC TRANS result = diff

/formula "extendedTransforms.countWkdays(startdate=startdate,enddate=enddate)".

startdate and enddate are SPSS date variables

The full definition of the functions is

def countWkdays(startdate, enddate, inclusive=True, exclude=None):

    """Count days between start and end dates that are not in excluded days

    

    start and end are dates in SPSS date formats with start <= end.

    inclusive = True includes the end date; False omits it.

    exclude is a list defining the days to be omitted using 3-letter day names in lower case.

    The time portion of a date is ignored."""

On Wed, Feb 22, 2017 at 6:57 AM, David Marso <[hidden email]> wrote:

"I found some examples on this forum using 'loop', but those are not working
when I try.
Can someone help me? "


Errr, maybe you should  repost these and explain exactly what seems to be
the problem?
What exactly do you define as business days?
You mean Xmas and Easter? Thanksgiving? blah blah blah etc?
Try again with an explicit definition of exactly what you are referring to.




LaraVDB wrote
> Hi,
>
> I am trying to calculate the business days between 2 dates.
> The simple calculation below is actually not good enough because the
> difference is often less than 7 days:
>
> COMPUTE difference=CTIME.DAYS(date1-date2)*5/7.
> EXECUTE.
>
> I found some examples on this forum using 'loop', but those are not
> working when I try.
>
> Can someone help me?
>
> KR,
> Lara





-----
Please reply to the list and not to my personal email.
Those desiring my consulting or training services please feel free to email me.
---
"Nolite dare sanctum canibus neque mittatis margaritas vestras ante porcos ne forte conculcent eas pedibus suis."
Cum es damnatorum possederunt porcos iens ut salire off sanguinum cliff in abyssum?"
--
View this message in context: http://spssx-discussion.1045642.n5.nabble.com/calculating-business-days-in-SPSS-tp5733871p5733872.html

Sent from the SPSSX Discussion mailing list archive at Nabble.com.

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

--

===================== To manage your subscription to SPSSX-L, send a message to [hidden email] (not to SPSSX-L), with no body text except the command. To leave the list, send the command SIGNOFF SPSSX-L For a list of commands to manage subscriptions, send the command INFO REFCARD

[LaraVDB]

So I want to calculate the business/working days between 2 dates (without taking into account holidays because the ones from Europe differ from those of America). When a weekend falls within the interval, those days should not be counted.

When I try this (from another post):
Compute businessdays=0.
Compute #tdate=date1.
Set mxloops=100. /* make sure this number is greater than the date2-date1+1.
Loop .
+  if (xdate.wkday(#tdate) ge 2 and xdate.wkday(date1) le 6) businessdays=businessdays+1.
Compute #tdate=datesum(date1,1,"days").
End loop If (#tdate gt date2).

SPSS gives an error 'End loop -> Incorrect variable name...'

When I try this:
LOOP #D=date1 TO date2 BY 86400.
COMPUTE businessdays=SUM(businessdays,RANGE(XDATE.WKDAY(#D),2,6)).
END LOOP.

SPSS says 'LOOP -> incorrect variable name...' and 'END LOOP -> The End LOOP command does not follow an unclosed LOOP command...'


Any idea what I did wrong?

Thanks!

[LaraVDB]

Unfortunately this is also not working.

Now it says: 'The formula references an undefined variable or could not be evaluated: 'NoneType' object has no attribute 'date'

I'm using 2 date variables in the same format, so I have no idea what the problem might be...

[David Marso]

A quick scan indicates that date2 is NOT the name of the variable for your endpoint.
This is supported by the errors from both exemplars.

In the first example "xdate.wkday(date1) le 6) "
I suspect that date1 should be #tdate?
Simple Copy/Pasta error.

LaraVDB wrote

So I want to calculate the business/working days between 2 dates (without taking into account holidays because the ones from Europe differ from those of America). When a weekend falls within the interval, those days should not be counted.

When I try this (from another post):
Compute businessdays=0.
Compute #tdate=date1.
Set mxloops=100. /* make sure this number is greater than the date2-date1+1.
Loop .
+  if (xdate.wkday(#tdate) ge 2 and xdate.wkday(date1) le 6) businessdays=businessdays+1.
Compute #tdate=datesum(date1,1,"days").
End loop If (#tdate gt date2).

SPSS gives an error 'End loop -> Incorrect variable name...'

When I try this:
LOOP #D=date1 TO date2 BY 86400.
COMPUTE businessdays=SUM(businessdays,RANGE(XDATE.WKDAY(#D),2,6)).
END LOOP.

SPSS says 'LOOP -> incorrect variable name...' and 'END LOOP -> The End LOOP command does not follow an unclosed LOOP command...'


Any idea what I did wrong?

Thanks!

[Jon Peck]

The cause is most likely missing values in one of the date variables.  You would need to remove those cases or insert a substitute date in order for this to run successfully.  A date cannot be extracted from a system-missing value.

On Wed, Feb 22, 2017 at 8:03 AM, LaraVDB <[hidden email]> wrote:

Unfortunately this is also not working.

Now it says: 'The formula references an undefined variable or could not be
evaluated: 'NoneType' object has no attribute 'date'

I'm using 2 date variables in the same format, so I have no idea what the
problem might be...



--
View this message in context: http://spssx-discussion.1045642.n5.nabble.com/calculating-business-days-in-SPSS-tp5733871p5733878.html

Sent from the SPSSX Discussion mailing list archive at Nabble.com.

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

--

===================== To manage your subscription to SPSSX-L, send a message to [hidden email] (not to SPSSX-L), with no body text except the command. To leave the list, send the command SIGNOFF SPSSX-L For a list of commands to manage subscriptions, send the command INFO REFCARD

[Jon Peck]

It occurred to me that there is another function in that module that handles missing values, returning sysmis when either date is missing.  You can use it like this to exclude Saturdays and Sundays.  

spssinc trans result = diff 

/initial  'extendedTransforms.countDaysWExclusions()'

/formula 'func(startdate=sdate, enddate=edate, exclude=["sat","sun"])'.

On Wed, Feb 22, 2017 at 8:28 AM, Jon Peck <[hidden email]> wrote:

The cause is most likely missing values in one of the date variables.  You would need to remove those cases or insert a substitute date in order for this to run successfully.  A date cannot be extracted from a system-missing value.

On Wed, Feb 22, 2017 at 8:03 AM, LaraVDB <[hidden email]> wrote:

Unfortunately this is also not working.

Now it says: 'The formula references an undefined variable or could not be
evaluated: 'NoneType' object has no attribute 'date'

I'm using 2 date variables in the same format, so I have no idea what the
problem might be...



--
View this message in context: http://spssx-discussion.1045642.n5.nabble.com/calculating-business-days-in-SPSS-tp5733871p5733878.html

Sent from the SPSSX Discussion mailing list archive at Nabble.com.

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

--

--

===================== To manage your subscription to SPSSX-L, send a message to [hidden email] (not to SPSSX-L), with no body text except the command. To leave the list, send the command SIGNOFF SPSSX-L For a list of commands to manage subscriptions, send the command INFO REFCARD

[LaraVDB]

This is working! Thank you so much!