Hi all

 

This is sort a continuation of this idea (for which I received a great deal of help): http://fmforums.com/forum/topic/91455-time-elapsed-calculation-causing-slowness/

 

The above finds the time between two timestamps, ignoring any time outside mon-fri 9-5.

 

I'm not sure if the above example will help but there it is just in case.

 

 

What I now need is the ability to acquire a new timestamp result by adding hours to the original timestamp, however, as with above, ignoring out of office hours.

 

e.g. If I wanted to add 7 hours to 15:00 02/01/2015 [Friday] then the result would be 14:00 05/01/2015 [Monday]

 

 

I really can't get my head around how I would tackle this. Any ideas appreciated.

 

 

Thanks

 

 

 

Can one assume the given timestamp is within office hours?

The initial timestamp could also fall outside of office hours

See if this could work for you. Caveat: it's not been tested very thoroughly! The two input fields are StartTimestamp (Timestamp)  and TimeToAdd (Time). Result is Timestamp.

Let ( [
startDate = GetAsDate ( StartTimestamp ) ;
startTime = GetAsTime ( StartTimestamp ) ; 

elapsedTime = Min ( Max ( 0 ;  startTime - Time ( 9 ; 0 ; 0 ) ) ;  Time ( 8 ; 0 ; 0 ) ) ;
workTime = elapsedTime + TimeToAdd ;
daysToAdd = Div ( workTime ; Time ( 8 ; 0 ; 0 ) ) ;
endTime = Time ( 9 ; 0 ; 0 ) + Mod ( workTime ; Time ( 8 ; 0 ; 0 ) ) ; 

d = startDate + Choose ( Mod ( startDate - 1 ; 7 ) - 5 ; 2 ; 1 ) ;
w = Div ( daysToAdd ; 5 ) ;
r = Mod ( daysToAdd ; 5 ) ;
s = Mod ( d ; 7 ) ;
a = s + r > 5 ;
e = d + 7 * w + r + 2 * a ;
endDate = e + Choose ( Mod ( e - 1 ; 7 ) - 5 ; 2 ; 1 )
] ;
Timestamp ( endDate ; endTime )
)

Hi

So far so good! I will do further testing but it seems to deal perfectly with most if not all criteria.

Thank you very much comment!