Re: Yet More Time by ekkehard
ekkehard
Mon Jun 18 15:45:18 CDT 2007
OldDog schrieb:
> On Jun 16, 10:03 am, OldDog <michael.r.felk...@wellsfargo.com> wrote:
>> On Jun 16, 3:45 am, "ekkehard.horner" <ekkehard.hor...@arcor.de>
>> wrote:
>>
>>
>>
>>
>>
>>> OldDog schrieb:
>>>> Hi,
>>>> I have found lots of interesting stuff about time here, but one thing
>>>> is eluding me.
>>>> I need to be able to find the elapsed time between when a call was
>>>> taken and the time that the case was resolved.
>>>> Easy enough you say and it is.
>>>> Then I need to compare the elapsed time with a constant value, ie. 2
>>>> Hours, 4 hours, 48 hours and 120 hours.
>>>> I have tried doing this in an excel spreadsheet but no luck.
>>>> so here it is in psudo code;
>>>> WorkTime = Start Time - Finish Time
>>>> If Worktime > 2 then
>>> Try this demo code:
>>> Dim dtRoot : dtRoot = Date
dtRoot will be Today 00:00:00
>>> dtRoot = DateAdd( "h", 7, dtRoot ) ' start work early
dtRoot will be Today 07:00:00 (seven o'clock in the morning)
>>> Dim nDayMin : nDayMin = 24 * 60
>>> Dim aLimits : aLimits = Array( _
>>> Array( 2 * 60, "fast: not more than 2 hours" ) _
>>> , Array( 4 * 60, "normal: not more than 4 hours" ) _
>>> , Array( 48 * 60, "slow: not more than 48 hours" ) _
>>> , Array( 120 * 60, "very slow: not more than 120 hours" ) _
>>> , Array( "forever", "forever: more than 120 hours" ) _
>>> )
>>> Dim aTests : aTests = Array( _
>>> Array( dtRoot, DateAdd( "h", 1, dtRoot ), Empty, Empty ) _
dtRoot is the start time
dtRoot + 1 hour is the end time
>>> , Array( dtRoot, DateAdd( "h", 2, dtRoot ), Empty, Empty ) _
>>> , Array( dtRoot, DateAdd( "n", 2 * 60 + 1, dtRoot ), Empty, Empty ) _
>>> , Array( dtRoot, DateAdd( "h", 3, dtRoot ), Empty, Empty ) _
>>> , Array( dtRoot, DateAdd( "n", 3 * 60 + 59, dtRoot ), Empty, Empty ) _
>>> , Array( dtRoot, DateAdd( "h", 4, dtRoot ), Empty, Empty ) _
>>> , Array( dtRoot, DateAdd( "n", 4 * 60 + 1, dtRoot ), Empty, Empty ) _
>>> , Array( dtRoot, DateAdd( "n", 47 * 60 + 59, dtRoot ), Empty, Empty ) _
>>> , Array( dtRoot, DateAdd( "h", 48, dtRoot ), Empty, Empty ) _
>>> , Array( dtRoot, DateAdd( "n", 48 * 60 + 1, dtRoot ), Empty, Empty ) _
>>> , Array( dtRoot, DateAdd( "n", 119 * 60 + 59, dtRoot ), Empty, Empty ) _
>>> , Array( dtRoot, DateAdd( "h", 120, dtRoot ), Empty, Empty ) _
>>> , Array( dtRoot, DateAdd( "n", 120 * 60 + 1, dtRoot ), Empty, Empty ) _
>>> , Array( dtRoot, DateAdd( "d", 9, dtRoot ), Empty, Empty ) _
>>> )
>>> Dim aTest, nMins, nDays, aLimit, vDiff
>>> For Each aTest In aTests
>>> nMins = DateDiff( "n", aTest( 0 ), aTest( 1 ) )
>>> aTest( 3 ) = aLimits( UBound( aLimits ) ) ' assume worst case
>>> For Each aLimit In aLimits
>>> If nMins <= aLimit( 0 ) Then
>>> aTest( 3 ) = aLimit( 1 )
>>> Exit For
>>> End If
>>> Next
>>> aTest( 2 ) = Right( Space( 6 ) & nMins, 6 )
>>> WScript.Echo Join( aTest, " " )
>>> Next
>>> -- output ---
>>> === howLong: how long did it take to solve a case =============================
>>> 16.06.2007 07:00:00 16.06.2007 08:00:00 60 fast: not more than 2 hours
start end duration compared against limits
>>> 16.06.2007 07:00:00 16.06.2007 09:00:00 120 fast: not more than 2 hours
>>> 16.06.2007 07:00:00 16.06.2007 09:01:00 121 normal: not more than 4 hours
>>> 16.06.2007 07:00:00 16.06.2007 10:00:00 180 normal: not more than 4 hours
>>> 16.06.2007 07:00:00 16.06.2007 10:59:00 239 normal: not more than 4 hours
>>> 16.06.2007 07:00:00 16.06.2007 11:00:00 240 normal: not more than 4 hours
>>> 16.06.2007 07:00:00 16.06.2007 11:01:00 241 slow: not more than 48 hours
>>> 16.06.2007 07:00:00 18.06.2007 06:59:00 2879 slow: not more than 48 hours
>>> 16.06.2007 07:00:00 18.06.2007 07:00:00 2880 slow: not more than 48 hours
>>> 16.06.2007 07:00:00 18.06.2007 07:01:00 2881 very slow: not more than 120 hours
>>> 16.06.2007 07:00:00 21.06.2007 06:59:00 7199 very slow: not more than 120 hours
>>> 16.06.2007 07:00:00 21.06.2007 07:00:00 7200 very slow: not more than 120 hours
>>> 16.06.2007 07:00:00 21.06.2007 07:01:00 7201 forever: more than 120 hours
>>> 16.06.2007 07:00:00 25.06.2007 07:00:00 12960 forever: more than 120 hours
>>> === howLong: 0 done (00:00:00) ================================================
>>> to see whether DateDiff (using "n" for minutes) and a loop over
>>> a list of limits will solve your problem.- Hide quoted text -
>>> - Show quoted text -
>> Wow, now thats a chunk of code. I'll spend the next week or so
>> digesting that and get back to you.
>> Thank you for your quick responce. I thought I had deleted that
>> question, so pardons for the double post.
>>
>> Old Dog- Hide quoted text -
>>
>> - Show quoted text -
>
> Hi, I have been looking at this most of the day and I have a few
> questions;
sorry about that; I should have added explanations.
>
> 1. Why are we adding 7 hours here? dtRoot = DateAdd( "h", 7,
> dtRoot ) ' start work early
Date returns the current date, adding 7 hours let us start the work
at seven o'clock (I could have used 5 hours to make you a really early
bird!)
>
> 2. How can I substitue a variable for dtRoot and where would I put a
> finish time
>
You would replace aTest( 0 ) resp. aTest( 1 ) with 'your' start and
end date/time; nMins will hold the duration of the work in minutes
>>> nMins = DateDiff( "n", aTest( 0 ), aTest( 1 ) )
Initializing a variable (I use aTest( 3 ) to make the output easy)
to indicate the duration was larger that the largest limit
>>> aTest( 3 ) = aLimits( UBound( aLimits ) ) ' assume worst case
check all limits
>>> For Each aLimit In aLimits
>>> If nMins <= aLimit( 0 ) Then
If the duration is smaller or equal to the current limit, correct
the (pessimistic) verdict and exit the loop
>>> aTest( 3 ) = aLimit( 1 )
>>> Exit For
>>> End If
>>> Next
we may reach this point after checking all limits without entering
the If clause; then our first assumption is correct: the work time
was larger than the largest limit
>>> aTest( 2 ) = Right( Space( 6 ) & nMins, 6 )
>>> WScript.Echo Join( aTest, " " )