Add time to timestamp, ignoring out of office hours

[sal88]

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

 

 

 

[comment]

Can one assume the given timestamp is within office hours?

[sal88]

The initial timestamp could also fall outside of office hours

[comment]

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 )
)

[sal88]

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!