Jump to content
Sign in to follow this  
sal88

Add time to timestamp, ignoring out of office hours

Recommended Posts

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


 

 

 

Share this post


Link to post
Share on other sites

The initial timestamp could also fall outside of office hours

Share this post


Link to post
Share on other sites

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 )
)
  • Like 1

Share this post


Link to post
Share on other sites

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!  :king:

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.