Need to change the Start and end time In Increments of 30

[sgaryali]

Hi

I want to update the Start and End time column based on condition :-

Start -

1) If we have minutes less then 30 minutes then we will reduce it to round of 00 minutes like

like - 16:15 will become 16:00 or 16:29 will become 16:00

2) If we have minutes more then 30 minutes then we will decrease it to 30 minutes.

like - 16:45 will become 16:30 or 16:53 will become 16:30


End -

1) If we have minutes less then 30 minutes then we will increase  it to round of 30 minutes like

 like - 16:15 to 16:30 or 16:29 to 16:30

2) If we have minutes more then 30 minutes then we will increase  it to 00 minutes.

Like - 16:45 to 17:00 or 16:59 to 17:00

I hope I have given some clarity let me know if further clarification required please help little bit tricky as the time metedata us also string.

Thank you so much :)

[gautiergodard]

@sgaryali check out this thread i think it answers you question!

Time Rounding - Alteryx Community

[sgaryali]

@gautiergodard  no that is a different scenario.

[DataNath · Accepted solution]

@sgaryali how do you get on with something like this? Basically just extracting the minutes, converting them to a number and applying the necessary checks, before then rounding them to the relevant value and - in the case of [End] - also increasing the hour by one if it needs rounding up:

[danilang · Accepted solution]

Good job @DataNath 

You just missed the edge case where end is >=23:31.   Change your formula to this

IIF(!EndsWith([End], '00'),
IIF(ToNumber(Right([End], 2))<30,Left([End], 3)+'30',
IIF(ToNumber(Left([End],2))<23,ToString(ToNumber(Left([End], 2))+1)+':00','00:00')),[End])

To give a valid time of 00:00

Dan 

[DataNath]

Ah of course @danilang - totally slipped my mind. Great spot thanks!