Calculate end timestamp from start timestamp and working hours

[nboccassini]

Hi,

I have a problem for calculate final timestamp in my load script.

My working hours are 9:00-13:00 and 14:00-18:00

I have a initial timestamp and  a working duration hours for an activity. For example:

Start (DD/MM/YYYY hh:mm:ss)	Interval (hh:mm:ss)
01/02/2015 12:50:00	00:20:00

I want to calculate the final timestamp.

For the example in the table the final timestamp is 01/02/2015 14:10 because the time from 13:00 to 14:00 not must be considered. The same logic must be used for the time in the holidays day (all the day) and from 18:00 to 9:00.

Ideas?

thanks,

Gabriele

[agomes1971]

Hi,

Ok for a start formula to add the minutes...

=TimeStamp(Num(Date#(Start, 'MM/DD/YYYY hh:mm:ss'))+(20/(24 * 60)),'DD/MM/YYYY HH:mm:ss')

HTH

André Gomes

[agomes1971]

Hi,

and another small help with this if

=if(hour(TimeStamp(Num(Date#(Start, 'MM/DD/YYYY hh:mm:ss'))+(20/(24 * 60)),'DD/MM/YYYY HH:mm:ss'))=13,

    TimeStamp(Num(Date#(Start, 'MM/DD/YYYY hh:mm:ss'))+((60+20)/(24 * 60)),'DD/MM/YYYY HH:mm:ss'),

    TimeStamp(Num(Date#(Start, 'MM/DD/YYYY hh:mm:ss'))+(20/(24 * 60)),'DD/MM/YYYY HH:mm:ss'))

and you just have to add a field if is vacation day or not... to do the rest of the calculations...

Regards

André Gomes

[nboccassini]

Your solution doesn't works because the Interval can be any value >00:00:00 (for example 13:23:45 )