Date Difference Calculation that Excludes Non-Business Hours
Locked
Date Difference Calculation that Excludes Non-Business Hours
 
|
Hi, I’m a new subscriber but have consulted the archives for past syntax-related questions. Any help with the question below is greatly appreciated! In SPSS syntax, we would like to determine how much time, in minutes, it takes to review a court document after it’s been submitted. The calculation should be based upon business hours and, consequently, will exclude holidays, weekends, and all time outside of 8:00am – 4:30 pm Monday through Friday. So far, we have the syntax to exclude holidays and weekends but are getting stuck when we try to exclude non-business hours during the week. Essentially, we’d like to set a parameter that excludes all time outside of 8:00 am to 4:30 pm. We’ve figured out how to do this in Excel, but it’s taking way too long to calculate as we amass more data. For that reason, we’d love to find a solution in SPSS. For reference, the Excel formula is: =IF(AND(INT(S19)=INT(Y19), OR(MOD(Y19, 1) < 0.333333333, MOD(Y19, 1) > 0.6875), (OR(MOD(S19, 1) < 0.333333333, MOD(S19, 1) > 0.6875))), MOD(S19,1) - MOD(Y19,1), (NETWORKDAYS(Y19,S19, Holidays!A:A)-1)*("16:30" - "8:00")+IF(NETWORKDAYS(S19,S19, Holidays!A:A),MEDIAN(MOD(S19,1),"16:30","8:00"),"16:30")-MEDIAN(NETWORKDAYS(Y19,Y19, Holidays!A:A)*MOD(Y19,1),"16:30","8:00")) where column Y is the submitted date/time and column S is the review date/time. I’ve listed some test data below, in case it’s helpful. Test Data SubmitDate AcceptDate FinalAnswer(using syntax) Expected FinalAnswer (hand calculation) 18-Jan-2013 16:31:15 22-Jan-2013 10:01:00 1049.00 121.00 31-Dec-2012 16:22:00 2-Jan-2013 09:10:00 1008.00 78.00 17-Jan-2013 15:00:00 18-Jan-2013 09:30:00 1110.00 180.00 18-Jan-2013 08:00:00 18-Jan-2013 10:00:00 120.00 120.00 Tracy |
Re: Date Difference Calculation that Excludes Non-Business Hours
Administrator
|
What you are asking is not terribly straightforward!!!
First you need to build all the logic to deal with holidays (I will not do that for you). See XDATE.WKDAY function (you can ID Sat and Sun from that). CTIME.DAYS function will yield number of days. I would build a LOOP and accumulate partial days added to whatever full days fall out of the above exclusionary logic. Get started on the first bits and post tested working code for that and then the group can fill in the leftovers. --
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?" |
Re: Date Difference Calculation that Excludes Non-Business Hours
|
Administrator
|
This post was updated on .
******* EDITED!!!! *********.
** Replaced 3 IF statements with the DO IF ELSE IF structure. ** Was not correctly resolving same day in/out **. ****You will need to add all your holidays to the first data list business. -- This gives the correct answers to the example you posted. -- DATA LIST LIST / HDM HDD . BEGIN DATA 1 1 1 21 END DATA. COMPUTE Holiday=DATE.MDY(HDM,HDD,2013). COMPUTE TestDate=Holiday. SAVE OUTFILE "C:\TEMP\Holidays.sav". DATA LIST LIST / D1 (DATE) T1 (TIME) D2 (DATE) T2 (TIME) CALC (F8.3) True (F8.3). BEGIN DATA 18-Jan-2013 16:31:15 22-Jan-2013 10:01:00 1049.00 121.00 31-Dec-2012 16:22:00 2-Jan-2013 09:10:00 1008.00 78.00 17-Jan-2013 15:00:00 18-Jan-2013 09:30:00 1110.00 180.00 18-Jan-2013 08:00:00 18-Jan-2013 10:00:00 120.00 120.00 END DATA. COMPUTE ID=$CASENUM. SAVE OUTFILE "C:\TEMP\Rawdata.sav". LOOP TestDate=D1 TO D2 BY 86400. DO IF RANGE(XDATE.WKDAY(TestDate),2,6). XSAVE OUTFILE "C:\TEMP\WkDays.sav" / KEEP ID D1 T1 D2 T2 TestDate. END IF. END LOOP. EXE. GET FILE "C:\TEMP\WkDays.sav" . SORT CASES BY TestDate. MATCH FILES / FILE * / TABLE "C:\TEMP\Holidays.sav" / BY TestDate. FORMAT TESTDATE (DATE). SELECT IF MISSING(HDM). SORT CASES BY ID TESTDATE. MATCH FILES / FILE * / BY ID / FIRST=TOP/LAST=BOT. DO IF (TOP AND BOT). + COMPUTE HDM=(T2-T1)/60. ELSE IF TOP . + COMPUTE HDM=MAX(0,TIME.HMS(16,30,0)-T1) /60. ELSE IF BOT . + COMPUTE HDM=MAX(0,T2-TIME.HMS(8,0,0))/60. ELSE. + COMPUTE HDM=(TIME.HMS(16,30,0)-TIME.HMS(8,0,0))/60. END IF. AGGREGATE OUTFILE * / BREAK ID / Minutes=SUM(HDM). MATCH FILES / FILE "C:\TEMP\Rawdata.sav" / FILE * / BY ID. LIST.
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?" |
Re: Date Difference Calculation that Excludes Non-Business Hours
|
|
That's great thanks David.
|
Re: Date Difference Calculation that Excludes Non-Business Hours
|
|
In reply to this post by David Marso
Thank you for tackling this, David. To make sure that I fully understand the syntax, what does HDM represent?
Tracy -----Original Message----- From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of David Marso Sent: Tuesday, January 22, 2013 6:08 PM To: [hidden email] Subject: Re: Date Difference Calculation that Excludes Non-Business Hours You will need to add all your holidays to the first data list business. -- This gives the correct answers to the example you posted. -- DATA LIST LIST / HDM HDD . BEGIN DATA 1 1 1 21 END DATA. COMPUTE Holiday=DATE.MDY(HDM,HDD,2013). COMPUTE TestDate=Holiday. SAVE OUTFILE "C:\TEMP\Holidays.sav". DATA LIST LIST / D1 (DATE) T1 (TIME) D2 (DATE) T2 (TIME) CALC (F8.3) True (F8.3). BEGIN DATA 18-Jan-2013 16:31:15 22-Jan-2013 10:01:00 1049.00 121.00 31-Dec-2012 16:22:00 2-Jan-2013 09:10:00 1008.00 78.00 17-Jan-2013 15:00:00 18-Jan-2013 09:30:00 1110.00 180.00 18-Jan-2013 08:00:00 18-Jan-2013 10:00:00 120.00 120.00 END DATA. COMPUTE ID=$CASENUM. SAVE OUTFILE "C:\TEMP\Rawdata.sav". LOOP TestDate=D1 TO D2 BY 86400. DO IF RANGE(XDATE.WKDAY(TestDate),2,6). XSAVE OUTFILE "C:\TEMP\WkDays.sav" / KEEP ID D1 T1 D2 T2 TestDate. END IF. END LOOP. EXE. GET FILE "C:\TEMP\WkDays.sav" . SORT CASES BY TestDate. MATCH FILES / FILE * / TABLE "C:\TEMP\Holidays.sav" / BY TestDate. FORMAT TESTDATE (DATE). SELECT IF MISSING(HDM). SORT CASES BY ID TESTDATE. MATCH FILES / FILE * / BY ID / FIRST=TOP/LAST=BOT. IF TOP HDM=MAX(0,TIME.HMS(16,30,0)-T1)/60. IF BOT HDM=MAX(0,T2-TIME.HMS(8,0,0))/60. IF NOT(TOP) AND NOT(BOT) HDM=(TIME.HMS(16,30,0)-TIME.HMS(8,0,0))/60. AGGREGATE OUTFILE * / BREAK ID / Minutes=SUM(HDM). MATCH FILES / FILE "C:\TEMP\Rawdata.sav" / FILE * / BY ID. LIST. David Marso wrote > What you are asking is not terribly straightforward!!! > First you need to build all the logic to deal with holidays (I will > not do that for you). > See XDATE.WKDAY function (you can ID Sat and Sun from that). > CTIME.DAYS function will yield number of days. > I would build a LOOP and accumulate partial days added to whatever > full days fall out of the above exclusionary logic. > Get started on the first bits and post tested working code for that > and then the group can fill in the leftovers. > -- > Loynachan, Tracy wrote >> Hi, >> >> I'm a new subscriber but have consulted the archives for past >> syntax-related questions. Any help with the question below is greatly >> appreciated! >> >> In SPSS syntax, we would like to determine how much time, in minutes, >> it takes to review a court document after it's been submitted. The >> calculation should be based upon business hours and, consequently, >> will exclude holidays, weekends, and all time outside of 8:00am - >> 4:30 pm Monday through Friday. So far, we have the syntax to exclude >> holidays and weekends but are getting stuck when we try to exclude >> non-business hours during the week. >> >> Essentially, we'd like to set a parameter that excludes all time >> outside of 8:00 am to 4:30 pm. We've figured out how to do this in >> Excel, but it's taking way too long to calculate as we amass more >> data. For that reason, we'd love to find a solution in SPSS. For >> reference, the Excel formula is: >> >> =IF(AND(INT(S19)=INT(Y19), OR(MOD(Y19, 1) < 0.333333333, MOD(Y19, 1) >> > 0.6875), (OR(MOD(S19, 1) < 0.333333333, MOD(S19, 1) > 0.6875))), >> MOD(S19,1) - MOD(Y19,1), (NETWORKDAYS(Y19,S19, Holidays!A:A)-1)*("16:30" >> - "8:00")+IF(NETWORKDAYS(S19,S19, >> Holidays!A:A),MEDIAN(MOD(S19,1),"16:30","8:00"),"16:30")-MEDIAN(NETWO >> RKDAYS(Y19,Y19, >> Holidays!A:A)*MOD(Y19,1),"16:30","8:00")) where column Y is the >> submitted date/time and column S is the review date/time. >> I've listed some test data below, in case it's helpful. >> >> Test Data >> SubmitDate AcceptDate >> FinalAnswer(using syntax) Expected FinalAnswer (hand >> calculation) >> 18-Jan-2013 16:31:15 22-Jan-2013 10:01:00 1049.00 >> 121.00 >> 31-Dec-2012 16:22:00 2-Jan-2013 09:10:00 1008.00 >> 78.00 >> 17-Jan-2013 15:00:00 18-Jan-2013 09:30:00 1110.00 >> 180.00 >> 18-Jan-2013 08:00:00 18-Jan-2013 10:00:00 120.00 >> 120.00 >> >> >> >> Tracy ----- Please reply to the list and not to my personal email. Those desiring my consulting or training services please feel free to email me. -- View this message in context: http://spssx-discussion.1045642.n5.nabble.com/Date-Difference-Calculation-that-Excludes-Non-Business-Hours-tp5717587p5717600.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 |
Re: Date Difference Calculation that Excludes Non-Business Hours
Administrator
|
If I follow, HDM = holiday Month, HDD = holiday Day.
--
Bruce Weaver bweaver@lakeheadu.ca http://sites.google.com/a/lakeheadu.ca/bweaver/ "When all else fails, RTFM." PLEASE NOTE THE FOLLOWING: 1. My Hotmail account is not monitored regularly. To send me an e-mail, please use the address shown above. 2. The SPSSX Discussion forum on Nabble is no longer linked to the SPSSX-L listserv administered by UGA (https://listserv.uga.edu/). |
Re: Date Difference Calculation that Excludes Non-Business Hours
|
|
Thank you! I figured it had to be the date, but wanted to make sure that I wasn't misinterpreting anything.
Tracy ________________________________________ From: SPSSX(r) Discussion [[hidden email]] On Behalf Of Bruce Weaver [[hidden email]] Sent: Saturday, January 26, 2013 7:09 AM To: [hidden email] Subject: Re: Date Difference Calculation that Excludes Non-Business Hours If I follow, HDM = holiday Month, HDD = holiday Day. Loynachan, Tracy wrote > Thank you for tackling this, David. To make sure that I fully understand > the syntax, what does HDM represent? > > Tracy > > -----Original Message----- > From: SPSSX(r) Discussion [mailto: > SPSSX-L@.UGA > ] On Behalf Of David Marso > Sent: Tuesday, January 22, 2013 6:08 PM > To: > SPSSX-L@.UGA > Subject: Re: Date Difference Calculation that Excludes Non-Business Hours > > You will need to add all your holidays to the first data list business. > -- > This gives the correct answers to the example you posted. > -- > DATA LIST LIST / HDM HDD . > BEGIN DATA > 1 1 > 1 21 > END DATA. > COMPUTE Holiday=DATE.MDY(HDM,HDD,2013). > COMPUTE TestDate=Holiday. > SAVE OUTFILE "C:\TEMP\Holidays.sav". > > > DATA LIST LIST / D1 (DATE) T1 (TIME) D2 (DATE) T2 (TIME) CALC (F8.3) True > (F8.3). > BEGIN DATA > 18-Jan-2013 16:31:15 22-Jan-2013 10:01:00 1049.00 > 121.00 > 31-Dec-2012 16:22:00 2-Jan-2013 09:10:00 1008.00 > 78.00 > 17-Jan-2013 15:00:00 18-Jan-2013 09:30:00 1110.00 > 180.00 > 18-Jan-2013 08:00:00 18-Jan-2013 10:00:00 120.00 > 120.00 > END DATA. > COMPUTE ID=$CASENUM. > SAVE OUTFILE "C:\TEMP\Rawdata.sav". > > LOOP TestDate=D1 TO D2 BY 86400. > DO IF RANGE(XDATE.WKDAY(TestDate),2,6). > XSAVE OUTFILE "C:\TEMP\WkDays.sav" / KEEP ID D1 T1 D2 T2 TestDate. > END IF. > END LOOP. > EXE. > > GET FILE "C:\TEMP\WkDays.sav" . > SORT CASES BY TestDate. > MATCH FILES / FILE * / TABLE "C:\TEMP\Holidays.sav" / BY TestDate. > FORMAT TESTDATE (DATE). > SELECT IF MISSING(HDM). > SORT CASES BY ID TESTDATE. > MATCH FILES / FILE * / BY ID / FIRST=TOP/LAST=BOT. > > IF TOP HDM=MAX(0,TIME.HMS(16,30,0)-T1)/60. > IF BOT HDM=MAX(0,T2-TIME.HMS(8,0,0))/60. > IF NOT(TOP) AND NOT(BOT) HDM=(TIME.HMS(16,30,0)-TIME.HMS(8,0,0))/60. > AGGREGATE OUTFILE * / BREAK ID / Minutes=SUM(HDM). > MATCH FILES / FILE "C:\TEMP\Rawdata.sav" / FILE * / BY ID. > > LIST. > > David Marso wrote >> What you are asking is not terribly straightforward!!! >> First you need to build all the logic to deal with holidays (I will >> not do that for you). >> See XDATE.WKDAY function (you can ID Sat and Sun from that). >> CTIME.DAYS function will yield number of days. >> I would build a LOOP and accumulate partial days added to whatever >> full days fall out of the above exclusionary logic. >> Get started on the first bits and post tested working code for that >> and then the group can fill in the leftovers. >> -- >> Loynachan, Tracy wrote >>> Hi, >>> >>> I'm a new subscriber but have consulted the archives for past >>> syntax-related questions. Any help with the question below is greatly >>> appreciated! >>> >>> In SPSS syntax, we would like to determine how much time, in minutes, >>> it takes to review a court document after it's been submitted. The >>> calculation should be based upon business hours and, consequently, >>> will exclude holidays, weekends, and all time outside of 8:00am - >>> 4:30 pm Monday through Friday. So far, we have the syntax to exclude >>> holidays and weekends but are getting stuck when we try to exclude >>> non-business hours during the week. >>> >>> Essentially, we'd like to set a parameter that excludes all time >>> outside of 8:00 am to 4:30 pm. We've figured out how to do this in >>> Excel, but it's taking way too long to calculate as we amass more >>> data. For that reason, we'd love to find a solution in SPSS. For >>> reference, the Excel formula is: >>> >>> =IF(AND(INT(S19)=INT(Y19), OR(MOD(Y19, 1) < 0.333333333, MOD(Y19, 1) >>> > 0.6875), (OR(MOD(S19, 1) < 0.333333333, MOD(S19, 1) > 0.6875))), >>> MOD(S19,1) - MOD(Y19,1), (NETWORKDAYS(Y19,S19, Holidays!A:A)-1)*("16:30" >>> - "8:00")+IF(NETWORKDAYS(S19,S19, >>> Holidays!A:A),MEDIAN(MOD(S19,1),"16:30","8:00"),"16:30")-MEDIAN(NETWO >>> RKDAYS(Y19,Y19, >>> Holidays!A:A)*MOD(Y19,1),"16:30","8:00")) where column Y is the >>> submitted date/time and column S is the review date/time. >>> I've listed some test data below, in case it's helpful. >>> >>> Test Data >>> SubmitDate AcceptDate >>> FinalAnswer(using syntax) Expected FinalAnswer (hand >>> calculation) >>> 18-Jan-2013 16:31:15 22-Jan-2013 10:01:00 1049.00 >>> 121.00 >>> 31-Dec-2012 16:22:00 2-Jan-2013 09:10:00 1008.00 >>> 78.00 >>> 17-Jan-2013 15:00:00 18-Jan-2013 09:30:00 1110.00 >>> 180.00 >>> 18-Jan-2013 08:00:00 18-Jan-2013 10:00:00 120.00 >>> 120.00 >>> >>> >>> >>> Tracy > > > > > > ----- > Please reply to the list and not to my personal email. > Those desiring my consulting or training services please feel free to > email me. > -- > View this message in context: > http://spssx-discussion.1045642.n5.nabble.com/Date-Difference-Calculation-that-Excludes-Non-Business-Hours-tp5717587p5717600.html > Sent from the SPSSX Discussion mailing list archive at Nabble.com. > > ===================== > To manage your subscription to SPSSX-L, send a message to > LISTSERV@.UGA > (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 > LISTSERV@.UGA > (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 ----- -- Bruce Weaver [hidden email] http://sites.google.com/a/lakeheadu.ca/bweaver/ "When all else fails, RTFM." NOTE: My Hotmail account is not monitored regularly. To send me an e-mail, please use the address shown above. -- View this message in context: http://spssx-discussion.1045642.n5.nabble.com/Date-Difference-Calculation-that-Excludes-Non-Business-Hours-tp5717587p5717712.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 |
Re: Date Difference Calculation that Excludes Non-Business Hours
|
|
In reply to this post by David Marso
When I run the syntax, I get the correct answer - 120 minutes - for ID #4 but get incorrect responses for the other three. Have I made a mistake in my syntax? Below is the file I am using, with some notes for my own use. I greatly appreciate any assistance.
***Create a file with holidays, then convert to a purely numeric variable***. DATA LIST LIST / HolidayMonth HolidayDay HolidayYear . BEGIN DATA 1 1 2013 1 21 2013 [*All other dates for 2013 and 2012 added*] END DATA. Compute Holiday=Date.MDY(HolidayMonth,HolidayDay,HolidayYear). Formats Holiday(adate10). Compute TestDate=Holiday. Execute. Sort Cases by TestDate(a). ***Next, work within the main, test data file - the one with submit and accept times. To makes things easier, extract the date and time portions from the original variables (formatted as adate20). This will create a submit date, submit time, accept date and accept time***. DATA LIST LIST / Submit(adate20) Accept(adate20) CALC (F8.3) True (F8.3). BEGIN DATA 18-Jan-2013 16:31:15 22-Jan-2013 10:01:00 1049.00 121.00 31-Dec-2012 16:22:00 2-Jan-2013 09:10:00 1008.00 78.00 17-Jan-2013 15:00:00 18-Jan-2013 09:30:00 1110.00 180.00 18-Jan-2013 08:00:00 18-Jan-2013 10:00:00 120.00 120.00 END DATA. * Date and Time Wizard: SubmitTime. COMPUTE SubmitTime=XDATE.TIME(Submit). VARIABLE LABELS SubmitTime "Time Submitted". VARIABLE LEVEL SubmitTime(SCALE). FORMATS SubmitTime(TIME5). VARIABLE WIDTH SubmitTime(5). EXECUTE. * Date and Time Wizard: AcceptTime. COMPUTE AcceptTime=XDATE.TIME(Accept). VARIABLE LABELS AcceptTime "Time Accepted". VARIABLE LEVEL AcceptTime(SCALE). FORMATS AcceptTime(TIME5). VARIABLE WIDTH AcceptTime(5). EXECUTE. * Date and Time Wizard: SubmitDate. COMPUTE SubmitDate=XDATE.DATE(Submit). VARIABLE LABELS SubmitDate "Date Submitted". VARIABLE LEVEL SubmitDate(SCALE). FORMATS SubmitDate(DATE11). VARIABLE WIDTH SubmitDate(11). EXECUTE. * Date and Time Wizard: AccpetDate. COMPUTE AcceptDate=XDATE.DATE(Accept). VARIABLE LABELS AcceptDate "Date Accepted". VARIABLE LEVEL AcceptDate(SCALE). FORMATS AcceptDate(DATE11). VARIABLE WIDTH AcceptDate(11). EXECUTE. *Compute an id number - this is only necessary for test data - the actual data has a unique identifier*. Compute ID=$Casenum. Execute. *Save test data file*. ***Next, work within the file to create a weekday file. LOOP TestDate=SubmitDate TO AcceptDate BY 86400. DO IF RANGE(XDATE.WKDAY(TestDate),2,6). END IF. END LOOP. EXECUTE. SORT CASES BY TestDate. *Save weekday file*. ***Match the weekday file to the holiday file. Modify path so it saves in the correct directory***. MATCH FILES /FILE=* /TABLE='C:\Temp\Holidays.sav' /BY TestDate. EXECUTE. Formats TestDate (adate10). SELECT IF Missing(HolidayMonth). EXECUTE. SORT CASES BY ID TESTDATE. MATCH FILES / FILE * / BY ID / FIRST=TOP/LAST=BOT. Execute. DO IF (TOP AND BOT). + COMPUTE HolidayMonth=(AcceptTime-SubmitTime)/60. ELSE IF TOP . + COMPUTE HolidayMonth=MAX(0,TIME.HMS(16,30,0)-SubmitTime) /60. ELSE IF BOT . + COMPUTE HolidayMonth=MAX(0,AcceptTime-TIME.HMS(8,0,0))/60. ELSE. + COMPUTE HolidayMonth=(TIME.HMS(16,30,0)-TIME.HMS(8,0,0))/60. END IF. Execute. AGGREGATE OUTFILE * / BREAK ID / Minutes=SUM(HolidayMonth). MATCH FILES / FILE "C:\Temp\TestData.sav" / FILE * / BY ID. List. -----Original Message----- From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of David Marso Sent: Tuesday, January 22, 2013 6:08 PM To: [hidden email] Subject: Re: Date Difference Calculation that Excludes Non-Business Hours You will need to add all your holidays to the first data list business. -- This gives the correct answers to the example you posted. -- DATA LIST LIST / HDM HDD . BEGIN DATA 1 1 1 21 END DATA. COMPUTE Holiday=DATE.MDY(HDM,HDD,2013). COMPUTE TestDate=Holiday. SAVE OUTFILE "C:\TEMP\Holidays.sav". DATA LIST LIST / D1 (DATE) T1 (TIME) D2 (DATE) T2 (TIME) CALC (F8.3) True (F8.3). BEGIN DATA 18-Jan-2013 16:31:15 22-Jan-2013 10:01:00 1049.00 121.00 31-Dec-2012 16:22:00 2-Jan-2013 09:10:00 1008.00 78.00 17-Jan-2013 15:00:00 18-Jan-2013 09:30:00 1110.00 180.00 18-Jan-2013 08:00:00 18-Jan-2013 10:00:00 120.00 120.00 END DATA. COMPUTE ID=$CASENUM. SAVE OUTFILE "C:\TEMP\Rawdata.sav". LOOP TestDate=D1 TO D2 BY 86400. DO IF RANGE(XDATE.WKDAY(TestDate),2,6). XSAVE OUTFILE "C:\TEMP\WkDays.sav" / KEEP ID D1 T1 D2 T2 TestDate. END IF. END LOOP. EXE. GET FILE "C:\TEMP\WkDays.sav" . SORT CASES BY TestDate. MATCH FILES / FILE * / TABLE "C:\TEMP\Holidays.sav" / BY TestDate. FORMAT TESTDATE (DATE). SELECT IF MISSING(HDM). SORT CASES BY ID TESTDATE. MATCH FILES / FILE * / BY ID / FIRST=TOP/LAST=BOT. IF TOP HDM=MAX(0,TIME.HMS(16,30,0)-T1)/60. IF BOT HDM=MAX(0,T2-TIME.HMS(8,0,0))/60. IF NOT(TOP) AND NOT(BOT) HDM=(TIME.HMS(16,30,0)-TIME.HMS(8,0,0))/60. AGGREGATE OUTFILE * / BREAK ID / Minutes=SUM(HDM). MATCH FILES / FILE "C:\TEMP\Rawdata.sav" / FILE * / BY ID. LIST. David Marso wrote > What you are asking is not terribly straightforward!!! > First you need to build all the logic to deal with holidays (I will > not do that for you). > See XDATE.WKDAY function (you can ID Sat and Sun from that). > CTIME.DAYS function will yield number of days. > I would build a LOOP and accumulate partial days added to whatever > full days fall out of the above exclusionary logic. > Get started on the first bits and post tested working code for that > and then the group can fill in the leftovers. > -- > Loynachan, Tracy wrote >> Hi, >> >> I'm a new subscriber but have consulted the archives for past >> syntax-related questions. Any help with the question below is greatly >> appreciated! >> >> In SPSS syntax, we would like to determine how much time, in minutes, >> it takes to review a court document after it's been submitted. The >> calculation should be based upon business hours and, consequently, >> will exclude holidays, weekends, and all time outside of 8:00am - >> 4:30 pm Monday through Friday. So far, we have the syntax to exclude >> holidays and weekends but are getting stuck when we try to exclude >> non-business hours during the week. >> >> Essentially, we'd like to set a parameter that excludes all time >> outside of 8:00 am to 4:30 pm. We've figured out how to do this in >> Excel, but it's taking way too long to calculate as we amass more >> data. For that reason, we'd love to find a solution in SPSS. For >> reference, the Excel formula is: >> >> =IF(AND(INT(S19)=INT(Y19), OR(MOD(Y19, 1) < 0.333333333, MOD(Y19, 1) >> > 0.6875), (OR(MOD(S19, 1) < 0.333333333, MOD(S19, 1) > 0.6875))), >> MOD(S19,1) - MOD(Y19,1), (NETWORKDAYS(Y19,S19, Holidays!A:A)-1)*("16:30" >> - "8:00")+IF(NETWORKDAYS(S19,S19, >> Holidays!A:A),MEDIAN(MOD(S19,1),"16:30","8:00"),"16:30")-MEDIAN(NETWO >> RKDAYS(Y19,Y19, >> Holidays!A:A)*MOD(Y19,1),"16:30","8:00")) where column Y is the >> submitted date/time and column S is the review date/time. >> I've listed some test data below, in case it's helpful. >> >> Test Data >> SubmitDate AcceptDate >> FinalAnswer(using syntax) Expected FinalAnswer (hand >> calculation) >> 18-Jan-2013 16:31:15 22-Jan-2013 10:01:00 1049.00 >> 121.00 >> 31-Dec-2012 16:22:00 2-Jan-2013 09:10:00 1008.00 >> 78.00 >> 17-Jan-2013 15:00:00 18-Jan-2013 09:30:00 1110.00 >> 180.00 >> 18-Jan-2013 08:00:00 18-Jan-2013 10:00:00 120.00 >> 120.00 >> >> >> >> Tracy ----- Please reply to the list and not to my personal email. Those desiring my consulting or training services please feel free to email me. -- View this message in context: http://spssx-discussion.1045642.n5.nabble.com/Date-Difference-Calculation-that-Excludes-Non-Business-Hours-tp5717587p5717600.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 |
Re: Date Difference Calculation that Excludes Non-Business Hours
|
Administrator
|
Carefully compare your code to my code. Do you see something missing? ---
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?" |
Re: Date Difference Calculation that Excludes Non-Business Hours
|
|
I started over from scratch and, of course, it worked. Thanks so much for your help with this! The syntax will save us several hours of work every month.
We do still need to determine how we'll account for certain scenarios -specifically, how we'll account for time when documents are submitted during non-business hours. As the syntax is written, it yields a negative number if the submit time occurs on a weekend. In addition, it provides a real-time calculation if the submit time occurs before 8:00 am (e.g. SubmitTime of 7:00 am and AcceptTime of 8:30 am on the same day yields a result of 90 minutes when it actually represents 30 minutes of "business time"). I think, however, that these scenarios are uncommon and that the provided syntax will properly calculate elapsed time in the vast majority of cases. We'll just make any necessary adjustments as we move forward. Thanks again! Tracy -- Final Syntax *****This syntax calculates elapsed time based upon non-business hours. It requires three separate files - a file with holiday dates, a file that includes submit and accept dates/times, and a weekday file created in the syntax below*****. *****Step 1. Create Holiday File*****. *First, must create a holiday file that includes all relevant holidays. Do this by hand entering holidays, with separate variables for month, date, year and then a combined date (adate10). This step can be bypassed once the file is created and all relevant holidays are included*. COMPUTE TestDate=Holiday. EXECUTE. SAVE OUTFILE "C:\Users\LoynachanT\Desktop\Holidays.sav". *****Step 2. Prepare Main File (with Submit and Accept Times) for Analysis*****. *Extract date and time portions from submit and accept adate20 variables.* * Date and Time Wizard: SubmitTime. COMPUTE SubmitTime=XDATE.TIME(Submit). VARIABLE LABELS SubmitTime "Time Submitted". VARIABLE LEVEL SubmitTime(SCALE). FORMATS SubmitTime(TIME5). VARIABLE WIDTH SubmitTime(5). EXECUTE. * Date and Time Wizard: AcceptTime. COMPUTE AcceptTime=XDATE.TIME(Accept). VARIABLE LABELS AcceptTime "Time Accepted". VARIABLE LEVEL AcceptTime(SCALE). FORMATS AcceptTime(TIME5). VARIABLE WIDTH AcceptTime(5). EXECUTE. * Date and Time Wizard: SubmitDate. COMPUTE SubmitDate=XDATE.DATE(Submit). VARIABLE LABELS SubmitDate "Date Submitted". VARIABLE LEVEL SubmitDate(SCALE). FORMATS SubmitDate(DATE11). VARIABLE WIDTH SubmitDate(11). EXECUTE. * Date and Time Wizard: AccpetDate. COMPUTE AcceptDate=XDATE.DATE(Accept). VARIABLE LABELS AcceptDate "Date Accepted". VARIABLE LEVEL AcceptDate(SCALE). FORMATS AcceptDate(DATE11). VARIABLE WIDTH AcceptDate(11). EXECUTE. *Create an ID variable (for test data only - actual data has a unique identifier already). COMPUTE ID=$CASENUM. EXECUTE. SAVE OUTFILE='C:\Users\LoynachanT\Desktop\Test Data for DateDiff Calc.sav'. *****Step 3. Identify week days*****. LOOP TestDate=SubmitDate TO AcceptDate BY 86400. DO IF RANGE(XDATE.WKDAY(TestDate),2,6). XSAVE OUTFILE "C:\Users\LoynachanT\Desktop\WkDays.sav" / KEEP ID SubmitDate SubmitTime AcceptDate AcceptTime TestDate. END IF. END LOOP. EXECUTE. *****Step 4. Merge the newly created weekday file and the holiday file and run time calculations*****. GET FILE "C:\Users\LoynachanT\Desktop\WkDays.sav" . SORT CASES BY TestDate. MATCH FILES / FILE * / TABLE "C:\Users\LoynachanT\Desktop\Holidays.sav" / BY TestDate. FORMATS TESTDATE (DATE). SELECT IF MISSING(HolidayMonth). SORT CASES BY ID TESTDATE. MATCH FILES / FILE * / BY ID / FIRST=TOP/LAST=BOT. DO IF (TOP AND BOT). COMPUTE HolidayMonth=(AcceptTime-SubmitTime)/60. ELSE IF TOP . COMPUTE HolidayMonth=MAX(0,TIME.HMS(16,30,0)-SubmitTime) /60. ELSE IF BOT . COMPUTE HolidayMonth=MAX(0,AcceptTime-TIME.HMS(8,0,0))/60. ELSE. COMPUTE HolidayMonth=(TIME.HMS(16,30,0)-TIME.HMS(8,0,0))/60. END IF. EXECUTE. *****Step 5. Aggregate results and merge with the main file*****. AGGREGATE OUTFILE * / BREAK ID / Minutes=SUM(HolidayMonth). MATCH FILES / FILE 'C:\Users\LoynachanT\Desktop\Test Data for DateDiff Calc.sav' / FILE * / BY ID. EXECUTE. LIST. -----Original Message----- From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of David Marso Sent: Monday, January 28, 2013 12:55 AM To: [hidden email] Subject: Re: Date Difference Calculation that Excludes Non-Business Hours Carefully compare your code to my code. Do you see something missing? --- Loynachan, Tracy wrote > When I run the syntax, I get the correct answer - 120 minutes - for ID > #4 but get incorrect responses for the other three. Have I made a > mistake in my syntax? Below is the file I am using, with some notes for my own use. > I greatly appreciate any assistance. > > > ***Create a file with holidays, then convert to a purely numeric > variable***. > DATA LIST LIST / HolidayMonth HolidayDay HolidayYear . > BEGIN DATA > 1 1 2013 > 1 21 2013 > [*All other dates for 2013 and 2012 added*] END DATA. > > Compute Holiday=Date.MDY(HolidayMonth,HolidayDay,HolidayYear). > Formats Holiday(adate10). > Compute TestDate=Holiday. > Execute. > > Sort Cases by TestDate(a). > > ***Next, work within the main, test data file - the one with submit > and accept times. To makes things easier, extract the date and time > portions from the original variables (formatted as adate20). This > will create a submit date, submit time, accept date and accept > time***. > > DATA LIST LIST / Submit(adate20) Accept(adate20) CALC (F8.3) True (F8.3). > BEGIN DATA > 18-Jan-2013 16:31:15 22-Jan-2013 10:01:00 1049.00 > 121.00 > 31-Dec-2012 16:22:00 2-Jan-2013 09:10:00 1008.00 > 78.00 > 17-Jan-2013 15:00:00 18-Jan-2013 09:30:00 1110.00 > 180.00 > 18-Jan-2013 08:00:00 18-Jan-2013 10:00:00 120.00 > 120.00 > END DATA. > > * Date and Time Wizard: SubmitTime. > COMPUTE SubmitTime=XDATE.TIME(Submit). > VARIABLE LABELS SubmitTime "Time Submitted". > VARIABLE LEVEL SubmitTime(SCALE). > FORMATS SubmitTime(TIME5). > VARIABLE WIDTH SubmitTime(5). > EXECUTE. > > * Date and Time Wizard: AcceptTime. > COMPUTE AcceptTime=XDATE.TIME(Accept). > VARIABLE LABELS AcceptTime "Time Accepted". > VARIABLE LEVEL AcceptTime(SCALE). > FORMATS AcceptTime(TIME5). > VARIABLE WIDTH AcceptTime(5). > EXECUTE. > > * Date and Time Wizard: SubmitDate. > COMPUTE SubmitDate=XDATE.DATE(Submit). > VARIABLE LABELS SubmitDate "Date Submitted". > VARIABLE LEVEL SubmitDate(SCALE). > FORMATS SubmitDate(DATE11). > VARIABLE WIDTH SubmitDate(11). > EXECUTE. > > * Date and Time Wizard: AccpetDate. > COMPUTE AcceptDate=XDATE.DATE(Accept). > VARIABLE LABELS AcceptDate "Date Accepted". > VARIABLE LEVEL AcceptDate(SCALE). > FORMATS AcceptDate(DATE11). > VARIABLE WIDTH AcceptDate(11). > EXECUTE. > > *Compute an id number - this is only necessary for test data - the > actual data has a unique identifier*. > > Compute ID=$Casenum. > Execute. > > *Save test data file*. > > ***Next, work within the file to create a weekday file. > > LOOP TestDate=SubmitDate TO AcceptDate BY 86400. > DO IF RANGE(XDATE.WKDAY(TestDate),2,6). > END IF. > END LOOP. > EXECUTE. > > SORT CASES BY TestDate. > > *Save weekday file*. > > ***Match the weekday file to the holiday file. Modify path so it saves > in the correct directory***. > > MATCH FILES /FILE=* > /TABLE='C:\Temp\Holidays.sav' > /BY TestDate. > EXECUTE. > > Formats TestDate (adate10). > SELECT IF Missing(HolidayMonth). > EXECUTE. > SORT CASES BY ID TESTDATE. > MATCH FILES / FILE * / BY ID / FIRST=TOP/LAST=BOT. > Execute. > > DO IF (TOP AND BOT). > + COMPUTE HolidayMonth=(AcceptTime-SubmitTime)/60. > ELSE IF TOP . > + COMPUTE HolidayMonth=MAX(0,TIME.HMS(16,30,0)-SubmitTime) /60. > ELSE IF BOT . > + COMPUTE HolidayMonth=MAX(0,AcceptTime-TIME.HMS(8,0,0))/60. > ELSE. > + COMPUTE HolidayMonth=(TIME.HMS(16,30,0)-TIME.HMS(8,0,0))/60. > END IF. > Execute. > > AGGREGATE OUTFILE * / BREAK ID / Minutes=SUM(HolidayMonth). > MATCH FILES / FILE "C:\Temp\TestData.sav" / FILE * / BY ID. > > List. > > > -----Original Message----- > From: SPSSX(r) Discussion [mailto: > SPSSX-L@.UGA > ] On Behalf Of David Marso > Sent: Tuesday, January 22, 2013 6:08 PM > To: > SPSSX-L@.UGA > Subject: Re: Date Difference Calculation that Excludes Non-Business > Hours > > You will need to add all your holidays to the first data list business. > -- > This gives the correct answers to the example you posted. > -- > DATA LIST LIST / HDM HDD . > BEGIN DATA > 1 1 > 1 21 > END DATA. > COMPUTE Holiday=DATE.MDY(HDM,HDD,2013). > COMPUTE TestDate=Holiday. > SAVE OUTFILE "C:\TEMP\Holidays.sav". > > > DATA LIST LIST / D1 (DATE) T1 (TIME) D2 (DATE) T2 (TIME) CALC (F8.3) > True (F8.3). > BEGIN DATA > 18-Jan-2013 16:31:15 22-Jan-2013 10:01:00 1049.00 > 121.00 > 31-Dec-2012 16:22:00 2-Jan-2013 09:10:00 1008.00 > 78.00 > 17-Jan-2013 15:00:00 18-Jan-2013 09:30:00 1110.00 > 180.00 > 18-Jan-2013 08:00:00 18-Jan-2013 10:00:00 120.00 > 120.00 > END DATA. > COMPUTE ID=$CASENUM. > SAVE OUTFILE "C:\TEMP\Rawdata.sav". > > LOOP TestDate=D1 TO D2 BY 86400. > DO IF RANGE(XDATE.WKDAY(TestDate),2,6). > XSAVE OUTFILE "C:\TEMP\WkDays.sav" / KEEP ID D1 T1 D2 T2 TestDate. > END IF. > END LOOP. > EXE. > > GET FILE "C:\TEMP\WkDays.sav" . > SORT CASES BY TestDate. > MATCH FILES / FILE * / TABLE "C:\TEMP\Holidays.sav" / BY TestDate. > FORMAT TESTDATE (DATE). > SELECT IF MISSING(HDM). > SORT CASES BY ID TESTDATE. > MATCH FILES / FILE * / BY ID / FIRST=TOP/LAST=BOT. > > IF TOP HDM=MAX(0,TIME.HMS(16,30,0)-T1)/60. > IF BOT HDM=MAX(0,T2-TIME.HMS(8,0,0))/60. > IF NOT(TOP) AND NOT(BOT) HDM=(TIME.HMS(16,30,0)-TIME.HMS(8,0,0))/60. > AGGREGATE OUTFILE * / BREAK ID / Minutes=SUM(HDM). > MATCH FILES / FILE "C:\TEMP\Rawdata.sav" / FILE * / BY ID. > > LIST. > > David Marso wrote >> What you are asking is not terribly straightforward!!! >> First you need to build all the logic to deal with holidays (I will >> not do that for you). >> See XDATE.WKDAY function (you can ID Sat and Sun from that). >> CTIME.DAYS function will yield number of days. >> I would build a LOOP and accumulate partial days added to whatever >> full days fall out of the above exclusionary logic. >> Get started on the first bits and post tested working code for that >> and then the group can fill in the leftovers. >> -- >> Loynachan, Tracy wrote >>> Hi, >>> >>> I'm a new subscriber but have consulted the archives for past >>> syntax-related questions. Any help with the question below is >>> greatly appreciated! >>> >>> In SPSS syntax, we would like to determine how much time, in >>> minutes, it takes to review a court document after it's been >>> submitted. The calculation should be based upon business hours and, >>> consequently, will exclude holidays, weekends, and all time outside >>> of 8:00am - >>> 4:30 pm Monday through Friday. So far, we have the syntax to >>> exclude holidays and weekends but are getting stuck when we try to >>> exclude non-business hours during the week. >>> >>> Essentially, we'd like to set a parameter that excludes all time >>> outside of 8:00 am to 4:30 pm. We've figured out how to do this in >>> Excel, but it's taking way too long to calculate as we amass more >>> data. For that reason, we'd love to find a solution in SPSS. For >>> reference, the Excel formula is: >>> >>> =IF(AND(INT(S19)=INT(Y19), OR(MOD(Y19, 1) < 0.333333333, MOD(Y19, 1) >>> > 0.6875), (OR(MOD(S19, 1) < 0.333333333, MOD(S19, 1) > 0.6875))), >>> MOD(S19,1) - MOD(Y19,1), (NETWORKDAYS(Y19,S19, Holidays!A:A)-1)*("16:30" >>> - "8:00")+IF(NETWORKDAYS(S19,S19, >>> Holidays!A:A),MEDIAN(MOD(S19,1),"16:30","8:00"),"16:30")-MEDIAN(NETW >>> O >>> RKDAYS(Y19,Y19, >>> Holidays!A:A)*MOD(Y19,1),"16:30","8:00")) where column Y is the >>> submitted date/time and column S is the review date/time. >>> I've listed some test data below, in case it's helpful. >>> >>> Test Data >>> SubmitDate AcceptDate >>> FinalAnswer(using syntax) Expected FinalAnswer (hand >>> calculation) >>> 18-Jan-2013 16:31:15 22-Jan-2013 10:01:00 1049.00 >>> 121.00 >>> 31-Dec-2012 16:22:00 2-Jan-2013 09:10:00 1008.00 >>> 78.00 >>> 17-Jan-2013 15:00:00 18-Jan-2013 09:30:00 1110.00 >>> 180.00 >>> 18-Jan-2013 08:00:00 18-Jan-2013 10:00:00 120.00 >>> 120.00 >>> >>> >>> >>> Tracy > > > > > > ----- > Please reply to the list and not to my personal email. > Those desiring my consulting or training services please feel free to > email me. > -- > View this message in context: > http://spssx-discussion.1045642.n5.nabble.com/Date-Difference-Calculat > ion-that-Excludes-Non-Business-Hours-tp5717587p5717600.html > Sent from the SPSSX Discussion mailing list archive at Nabble.com. > > ===================== > To manage your subscription to SPSSX-L, send a message to > LISTSERV@.UGA > (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 > LISTSERV@.UGA > (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 ----- Please reply to the list and not to my personal email. Those desiring my consulting or training services please feel free to email me. -- View this message in context: http://spssx-discussion.1045642.n5.nabble.com/Date-Difference-Calculation-that-Excludes-Non-Business-Hours-tp5717587p5717746.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 |
Re: Date Difference Calculation that Excludes Non-Business Hours
|
Administrator
|
I suggest you add another line in the commented section at the top of your final syntax. Something like:
* This syntax is based on an example posted to SPSSX-L by David Marso (see http://spssx-discussion.1045642.n5.nabble.com/Date-Difference-Calculation-that-Excludes-Non-Business-Hours-td5717587.html). Right now, you can remember David's contribution. But X years from now when the syntax has been passed on to new employees (or students, or whatever), that information will be lost if you don't document it. ;-)
--
Bruce Weaver bweaver@lakeheadu.ca http://sites.google.com/a/lakeheadu.ca/bweaver/ "When all else fails, RTFM." PLEASE NOTE THE FOLLOWING: 1. My Hotmail account is not monitored regularly. To send me an e-mail, please use the address shown above. 2. The SPSSX Discussion forum on Nabble is no longer linked to the SPSSX-L listserv administered by UGA (https://listserv.uga.edu/). |
Re: Date Difference Calculation that Excludes Non-Business Hours
|
|
Done!
-----Original Message----- From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Bruce Weaver Sent: Monday, January 28, 2013 3:12 PM To: [hidden email] Subject: Re: Date Difference Calculation that Excludes Non-Business Hours I suggest you add another line in the commented section at the top of your final syntax. Something like: * This syntax is based on an example posted to SPSSX-L by David Marso (see http://spssx-discussion.1045642.n5.nabble.com/Date-Difference-Calculation-that-Excludes-Non-Business-Hours-td5717587.html). Right now, you can remember David's contribution. But X years from now when the syntax has been passed on to new employees (or students, or whatever), that information will be lost if you don't document it. ;-) Loynachan, Tracy wrote > I started over from scratch and, of course, it worked. Thanks so much > for your help with this! The syntax will save us several hours of work > every month. > > We do still need to determine how we'll account for certain scenarios > -specifically, how we'll account for time when documents are submitted > during non-business hours. As the syntax is written, it yields a > negative number if the submit time occurs on a weekend. In addition, > it provides a real-time calculation if the submit time occurs before > 8:00 am (e.g. SubmitTime of 7:00 am and AcceptTime of 8:30 am on the > same day yields a result of 90 minutes when it actually represents 30 > minutes of "business time"). I think, however, that these scenarios > are uncommon and that the provided syntax will properly calculate > elapsed time in the vast majority of cases. We'll just make any > necessary adjustments as we move forward. > > Thanks again! > > > Tracy > > -- > > Final Syntax > > *****This syntax calculates elapsed time based upon non-business hours. > It requires three separate files - a file with holiday dates, a file > that includes submit and accept dates/times, and a weekday file > created in the syntax below*****. > > *****Step 1. Create Holiday File*****. > > *First, must create a holiday file that includes all relevant > holidays. Do this by hand entering holidays, with separate variables > for month, date, year and then a combined date (adate10). This step > can be bypassed once the file is created and all relevant holidays are included*. > > COMPUTE TestDate=Holiday. > EXECUTE. > SAVE OUTFILE "C:\Users\LoynachanT\Desktop\Holidays.sav". > > *****Step 2. Prepare Main File (with Submit and Accept Times) for > Analysis*****. > > *Extract date and time portions from submit and accept adate20 > variables.* > > * Date and Time Wizard: SubmitTime. > COMPUTE SubmitTime=XDATE.TIME(Submit). > VARIABLE LABELS SubmitTime "Time Submitted". > VARIABLE LEVEL SubmitTime(SCALE). > FORMATS SubmitTime(TIME5). > VARIABLE WIDTH SubmitTime(5). > EXECUTE. > > * Date and Time Wizard: AcceptTime. > COMPUTE AcceptTime=XDATE.TIME(Accept). > VARIABLE LABELS AcceptTime "Time Accepted". > VARIABLE LEVEL AcceptTime(SCALE). > FORMATS AcceptTime(TIME5). > VARIABLE WIDTH AcceptTime(5). > EXECUTE. > > * Date and Time Wizard: SubmitDate. > COMPUTE SubmitDate=XDATE.DATE(Submit). > VARIABLE LABELS SubmitDate "Date Submitted". > VARIABLE LEVEL SubmitDate(SCALE). > FORMATS SubmitDate(DATE11). > VARIABLE WIDTH SubmitDate(11). > EXECUTE. > > * Date and Time Wizard: AccpetDate. > COMPUTE AcceptDate=XDATE.DATE(Accept). > VARIABLE LABELS AcceptDate "Date Accepted". > VARIABLE LEVEL AcceptDate(SCALE). > FORMATS AcceptDate(DATE11). > VARIABLE WIDTH AcceptDate(11). > EXECUTE. > > *Create an ID variable (for test data only - actual data has a unique > identifier already). > > COMPUTE ID=$CASENUM. > EXECUTE. > SAVE OUTFILE='C:\Users\LoynachanT\Desktop\Test Data for DateDiff > Calc.sav'. > > *****Step 3. Identify week days*****. > > LOOP TestDate=SubmitDate TO AcceptDate BY 86400. > DO IF RANGE(XDATE.WKDAY(TestDate),2,6). > XSAVE OUTFILE "C:\Users\LoynachanT\Desktop\WkDays.sav" / KEEP ID > SubmitDate SubmitTime AcceptDate AcceptTime TestDate. > END IF. > END LOOP. > EXECUTE. > > *****Step 4. Merge the newly created weekday file and the holiday file > and run time calculations*****. > > GET FILE "C:\Users\LoynachanT\Desktop\WkDays.sav" . > SORT CASES BY TestDate. > MATCH FILES / FILE * / TABLE > "C:\Users\LoynachanT\Desktop\Holidays.sav" / BY TestDate. > FORMATS TESTDATE (DATE). > SELECT IF MISSING(HolidayMonth). > SORT CASES BY ID TESTDATE. > MATCH FILES / FILE * / BY ID / FIRST=TOP/LAST=BOT. > > DO IF (TOP AND BOT). > COMPUTE HolidayMonth=(AcceptTime-SubmitTime)/60. > ELSE IF TOP . > COMPUTE HolidayMonth=MAX(0,TIME.HMS(16,30,0)-SubmitTime) /60. > ELSE IF BOT . > COMPUTE HolidayMonth=MAX(0,AcceptTime-TIME.HMS(8,0,0))/60. > ELSE. > COMPUTE HolidayMonth=(TIME.HMS(16,30,0)-TIME.HMS(8,0,0))/60. > END IF. > EXECUTE. > > *****Step 5. Aggregate results and merge with the main file*****. > > AGGREGATE OUTFILE * / BREAK ID / Minutes=SUM(HolidayMonth). > MATCH FILES / FILE 'C:\Users\LoynachanT\Desktop\Test Data for DateDiff > Calc.sav' / FILE * / BY ID. > EXECUTE. > > LIST. > > > -----Original Message----- > From: SPSSX(r) Discussion [mailto: > SPSSX-L@.UGA > ] On Behalf Of David Marso > Sent: Monday, January 28, 2013 12:55 AM > To: > SPSSX-L@.UGA > Subject: Re: Date Difference Calculation that Excludes Non-Business > Hours > > Carefully compare your code to my code. > Do you see something missing? > --- > > Loynachan, Tracy wrote >> When I run the syntax, I get the correct answer - 120 minutes - for >> ID >> #4 but get incorrect responses for the other three. Have I made a >> mistake in my syntax? Below is the file I am using, with some notes >> for my own use. >> I greatly appreciate any assistance. >> >> >> ***Create a file with holidays, then convert to a purely numeric >> variable***. >> DATA LIST LIST / HolidayMonth HolidayDay HolidayYear . >> BEGIN DATA >> 1 1 2013 >> 1 21 2013 >> [*All other dates for 2013 and 2012 added*] END DATA. >> >> Compute Holiday=Date.MDY(HolidayMonth,HolidayDay,HolidayYear). >> Formats Holiday(adate10). >> Compute TestDate=Holiday. >> Execute. >> >> Sort Cases by TestDate(a). >> >> ***Next, work within the main, test data file - the one with submit >> and accept times. To makes things easier, extract the date and time >> portions from the original variables (formatted as adate20). This >> will create a submit date, submit time, accept date and accept >> time***. >> >> DATA LIST LIST / Submit(adate20) Accept(adate20) CALC (F8.3) True (F8.3). >> BEGIN DATA >> 18-Jan-2013 16:31:15 22-Jan-2013 10:01:00 1049.00 >> 121.00 >> 31-Dec-2012 16:22:00 2-Jan-2013 09:10:00 1008.00 >> 78.00 >> 17-Jan-2013 15:00:00 18-Jan-2013 09:30:00 1110.00 >> 180.00 >> 18-Jan-2013 08:00:00 18-Jan-2013 10:00:00 120.00 >> 120.00 >> END DATA. >> >> * Date and Time Wizard: SubmitTime. >> COMPUTE SubmitTime=XDATE.TIME(Submit). >> VARIABLE LABELS SubmitTime "Time Submitted". >> VARIABLE LEVEL SubmitTime(SCALE). >> FORMATS SubmitTime(TIME5). >> VARIABLE WIDTH SubmitTime(5). >> EXECUTE. >> >> * Date and Time Wizard: AcceptTime. >> COMPUTE AcceptTime=XDATE.TIME(Accept). >> VARIABLE LABELS AcceptTime "Time Accepted". >> VARIABLE LEVEL AcceptTime(SCALE). >> FORMATS AcceptTime(TIME5). >> VARIABLE WIDTH AcceptTime(5). >> EXECUTE. >> >> * Date and Time Wizard: SubmitDate. >> COMPUTE SubmitDate=XDATE.DATE(Submit). >> VARIABLE LABELS SubmitDate "Date Submitted". >> VARIABLE LEVEL SubmitDate(SCALE). >> FORMATS SubmitDate(DATE11). >> VARIABLE WIDTH SubmitDate(11). >> EXECUTE. >> >> * Date and Time Wizard: AccpetDate. >> COMPUTE AcceptDate=XDATE.DATE(Accept). >> VARIABLE LABELS AcceptDate "Date Accepted". >> VARIABLE LEVEL AcceptDate(SCALE). >> FORMATS AcceptDate(DATE11). >> VARIABLE WIDTH AcceptDate(11). >> EXECUTE. >> >> *Compute an id number - this is only necessary for test data - the >> actual data has a unique identifier*. >> >> Compute ID=$Casenum. >> Execute. >> >> *Save test data file*. >> >> ***Next, work within the file to create a weekday file. >> >> LOOP TestDate=SubmitDate TO AcceptDate BY 86400. >> DO IF RANGE(XDATE.WKDAY(TestDate),2,6). >> END IF. >> END LOOP. >> EXECUTE. >> >> SORT CASES BY TestDate. >> >> *Save weekday file*. >> >> ***Match the weekday file to the holiday file. Modify path so it >> saves in the correct directory***. >> >> MATCH FILES /FILE=* >> /TABLE='C:\Temp\Holidays.sav' >> /BY TestDate. >> EXECUTE. >> >> Formats TestDate (adate10). >> SELECT IF Missing(HolidayMonth). >> EXECUTE. >> SORT CASES BY ID TESTDATE. >> MATCH FILES / FILE * / BY ID / FIRST=TOP/LAST=BOT. >> Execute. >> >> DO IF (TOP AND BOT). >> + COMPUTE HolidayMonth=(AcceptTime-SubmitTime)/60. >> ELSE IF TOP . >> + COMPUTE HolidayMonth=MAX(0,TIME.HMS(16,30,0)-SubmitTime) /60. >> ELSE IF BOT . >> + COMPUTE HolidayMonth=MAX(0,AcceptTime-TIME.HMS(8,0,0))/60. >> ELSE. >> + COMPUTE HolidayMonth=(TIME.HMS(16,30,0)-TIME.HMS(8,0,0))/60. >> END IF. >> Execute. >> >> AGGREGATE OUTFILE * / BREAK ID / Minutes=SUM(HolidayMonth). >> MATCH FILES / FILE "C:\Temp\TestData.sav" / FILE * / BY ID. >> >> List. >> >> >> -----Original Message----- >> From: SPSSX(r) Discussion [mailto: > >> SPSSX-L@.UGA > >> ] On Behalf Of David Marso >> Sent: Tuesday, January 22, 2013 6:08 PM >> To: > >> SPSSX-L@.UGA > >> Subject: Re: Date Difference Calculation that Excludes Non-Business >> Hours >> >> You will need to add all your holidays to the first data list business. >> -- >> This gives the correct answers to the example you posted. >> -- >> DATA LIST LIST / HDM HDD . >> BEGIN DATA >> 1 1 >> 1 21 >> END DATA. >> COMPUTE Holiday=DATE.MDY(HDM,HDD,2013). >> COMPUTE TestDate=Holiday. >> SAVE OUTFILE "C:\TEMP\Holidays.sav". >> >> >> DATA LIST LIST / D1 (DATE) T1 (TIME) D2 (DATE) T2 (TIME) CALC (F8.3) >> True (F8.3). >> BEGIN DATA >> 18-Jan-2013 16:31:15 22-Jan-2013 10:01:00 1049.00 >> 121.00 >> 31-Dec-2012 16:22:00 2-Jan-2013 09:10:00 1008.00 >> 78.00 >> 17-Jan-2013 15:00:00 18-Jan-2013 09:30:00 1110.00 >> 180.00 >> 18-Jan-2013 08:00:00 18-Jan-2013 10:00:00 120.00 >> 120.00 >> END DATA. >> COMPUTE ID=$CASENUM. >> SAVE OUTFILE "C:\TEMP\Rawdata.sav". >> >> LOOP TestDate=D1 TO D2 BY 86400. >> DO IF RANGE(XDATE.WKDAY(TestDate),2,6). >> XSAVE OUTFILE "C:\TEMP\WkDays.sav" / KEEP ID D1 T1 D2 T2 TestDate. >> END IF. >> END LOOP. >> EXE. >> >> GET FILE "C:\TEMP\WkDays.sav" . >> SORT CASES BY TestDate. >> MATCH FILES / FILE * / TABLE "C:\TEMP\Holidays.sav" / BY TestDate. >> FORMAT TESTDATE (DATE). >> SELECT IF MISSING(HDM). >> SORT CASES BY ID TESTDATE. >> MATCH FILES / FILE * / BY ID / FIRST=TOP/LAST=BOT. >> >> IF TOP HDM=MAX(0,TIME.HMS(16,30,0)-T1)/60. >> IF BOT HDM=MAX(0,T2-TIME.HMS(8,0,0))/60. >> IF NOT(TOP) AND NOT(BOT) HDM=(TIME.HMS(16,30,0)-TIME.HMS(8,0,0))/60. >> AGGREGATE OUTFILE * / BREAK ID / Minutes=SUM(HDM). >> MATCH FILES / FILE "C:\TEMP\Rawdata.sav" / FILE * / BY ID. >> >> LIST. >> >> David Marso wrote >>> What you are asking is not terribly straightforward!!! >>> First you need to build all the logic to deal with holidays (I will >>> not do that for you). >>> See XDATE.WKDAY function (you can ID Sat and Sun from that). >>> CTIME.DAYS function will yield number of days. >>> I would build a LOOP and accumulate partial days added to whatever >>> full days fall out of the above exclusionary logic. >>> Get started on the first bits and post tested working code for that >>> and then the group can fill in the leftovers. >>> -- >>> Loynachan, Tracy wrote >>>> Hi, >>>> >>>> I'm a new subscriber but have consulted the archives for past >>>> syntax-related questions. Any help with the question below is >>>> greatly appreciated! >>>> >>>> In SPSS syntax, we would like to determine how much time, in >>>> minutes, it takes to review a court document after it's been >>>> submitted. The calculation should be based upon business hours and, >>>> consequently, will exclude holidays, weekends, and all time outside >>>> of 8:00am - >>>> 4:30 pm Monday through Friday. So far, we have the syntax to >>>> exclude holidays and weekends but are getting stuck when we try to >>>> exclude non-business hours during the week. >>>> >>>> Essentially, we'd like to set a parameter that excludes all time >>>> outside of 8:00 am to 4:30 pm. We've figured out how to do this in >>>> Excel, but it's taking way too long to calculate as we amass more >>>> data. For that reason, we'd love to find a solution in SPSS. For >>>> reference, the Excel formula is: >>>> >>>> =IF(AND(INT(S19)=INT(Y19), OR(MOD(Y19, 1) < 0.333333333, MOD(Y19, >>>> 1) >>>> > 0.6875), (OR(MOD(S19, 1) < 0.333333333, MOD(S19, 1) > 0.6875))), >>>> MOD(S19,1) - MOD(Y19,1), (NETWORKDAYS(Y19,S19, >>>> Holidays!A:A)-1)*("16:30" >>>> - "8:00")+IF(NETWORKDAYS(S19,S19, >>>> Holidays!A:A),MEDIAN(MOD(S19,1),"16:30","8:00"),"16:30")-MEDIAN(NET >>>> W >>>> O >>>> RKDAYS(Y19,Y19, >>>> Holidays!A:A)*MOD(Y19,1),"16:30","8:00")) where column Y is the >>>> submitted date/time and column S is the review date/time. >>>> I've listed some test data below, in case it's helpful. >>>> >>>> Test Data >>>> SubmitDate AcceptDate >>>> FinalAnswer(using syntax) Expected FinalAnswer (hand >>>> calculation) >>>> 18-Jan-2013 16:31:15 22-Jan-2013 10:01:00 1049.00 >>>> 121.00 >>>> 31-Dec-2012 16:22:00 2-Jan-2013 09:10:00 1008.00 >>>> 78.00 >>>> 17-Jan-2013 15:00:00 18-Jan-2013 09:30:00 1110.00 >>>> 180.00 >>>> 18-Jan-2013 08:00:00 18-Jan-2013 10:00:00 120.00 >>>> 120.00 >>>> >>>> >>>> >>>> Tracy >> >> >> >> >> >> ----- >> Please reply to the list and not to my personal email. >> Those desiring my consulting or training services please feel free to >> email me. >> -- >> View this message in context: >> http://spssx-discussion.1045642.n5.nabble.com/Date-Difference-Calcula >> t ion-that-Excludes-Non-Business-Hours-tp5717587p5717600.html >> Sent from the SPSSX Discussion mailing list archive at Nabble.com. >> >> ===================== >> To manage your subscription to SPSSX-L, send a message to > >> LISTSERV@.UGA > >> (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 > >> LISTSERV@.UGA > >> (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 > > > > > > ----- > Please reply to the list and not to my personal email. > Those desiring my consulting or training services please feel free to > email me. > -- > View this message in context: > http://spssx-discussion.1045642.n5.nabble.com/Date-Difference-Calculat > ion-that-Excludes-Non-Business-Hours-tp5717587p5717746.html > Sent from the SPSSX Discussion mailing list archive at Nabble.com. > > ===================== > To manage your subscription to SPSSX-L, send a message to > LISTSERV@.UGA > (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 > LISTSERV@.UGA > (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 ----- -- Bruce Weaver [hidden email] http://sites.google.com/a/lakeheadu.ca/bweaver/ "When all else fails, RTFM." NOTE: My Hotmail account is not monitored regularly. To send me an e-mail, please use the address shown above. -- View this message in context: http://spssx-discussion.1045642.n5.nabble.com/Date-Difference-Calculation-that-Excludes-Non-Business-Hours-tp5717587p5717771.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 |
«
Return to SPSSX Discussion
|
1 view|%1 views
| Free forum by Nabble | Edit this page |