Exceeding Max Visit Duration

[Grantham]

Good day All 

Please can you assist. 

Below is visit data from one of our Sales Reps. The Rep forgot to log out of the call. The Max allowed duration is 2hours per call.

Acc no.	Acc Name	Visit No.	Start Time	End Time	Duration
112233	ABC Store	V0001	2022/06/28 11:00	2022/06/28 14:00	180
445566	XYZ Store	V0002	2022/06/28 14:50	2022/06/28 15:30	40
100025	Happy bar	V0003	2022/06/28 16:00	2022/06/29 02:00	600

Please help me get to below result. 

Acc no.	Acc Name	Visit No.	Start Time	End Time	Duration	End Time (Adjust for Max Duration)	Duration Adj
112233	ABC Store	V0001	2022/06/28 11:00	2022/06/28 14:00	180	2022/06/28 13:00	120
445566	XYZ Store	V0002	2022/06/28 14:50	2022/06/28 15:30	40	2022/06/28 15:30	40
100025	Happy bar	V0003	2022/06/28 16:00	2022/06/29 02:00	600	2022/06/28 18:00	120

[MarqueeCrew]

@Grantham ,

 it may seem backwards, but

Min([duration],120)

 cheers,

 mark

[Qiu]

@Grantham 
For the "End Time (Adjust for Max Duration) " part, we can use the DateTime Tool to the conversion from String to DateTime or the reverse.

[Emmanuel_G]

Hi @Grantham ,

Find a really simple test to use with exact desired output.

Let us know if it works as you want.

Cheers !

[grazitti_sapna]

@Grantham, another way of solving this problem.

Thanks!

[binuacs]

@Grantham one way of doing this