Jump to content

Add time to timestamp, ignoring out of office hours


This topic is 2039 days old. Please don't post here. Open a new topic instead.

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


 

 

 

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

Link to post
Share on other sites

This topic is 2039 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...

Important Information

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